Making charts with Postgres

Time series reports

SELECT COUNT(id) AS "orders", to_char(date, 'YYYY-MM-DD') AS "day"
FROM orders
GROUP BY "day"
ORDER BY "day"

Moving date ranges

SELECT COUNT(id) AS "orders", to_char(date, 'YYYY-MM-DD') AS "day" 
FROM orders
WHERE date > current_date - interval '30 day'
GROUP BY "day"
ORDER BY "day"

Case statements

SELECT COUNT(id), 
CASE WHEN status=0 THEN 'Pending'
WHEN status=2 THEN 'Complete'
WHEN status=3 THEN 'Error'
WHEN status=4 THEN 'Returned'
ELSE 'Unknown status type'
END as "status"
FROM orders
GROUP BY status

Time between two timestamps

SELECT EXTRACT(epoch FROM (ended_at — started_at))
FROM orders
SELECT AVG(EXTRACT(epoch FROM(ended_at — started_at))), to_char(started_at, 'YYYY-MM-DD') as "day"
FROM orders
GROUP BY "day"
ORDER BY "day"

IN clauses

SELECT * FROM orders
WHERE store_id = 100
OR store_id = 127
OR store_id = 156
OR store_id = 189
OR store_id = 212
SELECT * FROM orders
WHERE store_id IN (100, 127, 156, 189, 212)

JOIN against a key value table for WHERE clauses

id user_id key value
1 1 name Kim
2 1 email kim@gmail.com
3 1 tier premium
4 2 name Thor
5 2 email thor@gmail.com
6 2 tier basic
7 3 name Ben
8 3 email ben@bengarvey.com
9 3 tier premium
SELECT COUNT(o.id) FROM orders o
WHERE o.user_id IN
(SELECT u.user_id FROM users u WHERE u.tier = 'premium')
AND o.user_id IN
(SELECT u.user_id FROM users u WHERE u.email LIKE '%gmail.com')

--

--

chart junkie @ Betterment http://bengarvey.com

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store