Analytics Patterns · generate_series · LEFT JOIN · filling date gaps
49. Daily active users, zero days included
Concept: generate_series · LEFT JOIN · filling date gapsshow
GROUP BY only returns dates that exist in the data, so quiet days silently vanish from charts. generate_series(start, end, INTERVAL '1 day') builds the full calendar; LEFT JOIN the data onto it and count a column from the data side, which gives 0 when nothing matched.
Example (different data):
SELECT d::date AS day, COUNT(t.id) AS tickets
FROM generate_series('2024-03-01'::date, '2024-03-07'::date, INTERVAL '1 day') AS d
LEFT JOIN tickets t ON t.opened_on = d::date
GROUP BY d
ORDER BY d;Your task
The growth dashboard's daily active users chart skips days with no logins, which makes August look smoother than it was.
Return day (a DATE) and active_users (distinct users who logged in that day) for every day from 2024-08-01 to 2024-08-14, including days with 0. Oldest first.
Starting Postgres in your browser…
Checking your access…