SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…