I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL.
What I have so far is a I create a sequence of days, then LEFT JOIN it, if I do this on a single user, I get exactly what I need, but if I do the same over multiple users I don't because the table with the records has all users, so it will always be able to join a day at any one point
This is the query I'm running now
WITH days AS (
select generate_series((current_date - interval '7 days')::timestamp, current_date::timestamp, '1 day'::interval)::date AS day
), eves AS (
SELECT COUNT(id) as evs, user_id,
DATE_TRUNC('day', created_at)::timestamp AS full_day
FROM logged_events
WHERE DATE_TRUNC('day', logged_events.created_at) > current_date - interval '7 days'
GROUP BY user_id, full_day
)
SELECT
eves.full_day, days.day, eves.user_id FROM days
LEFT JOIN eves ON eves.full_day = days.day
GROUP BY eves.user_id, eves.full_day, days.day
But like I said because on logged_events I have records from all users, when I join days it will always find something to join, if I were to limit to a single user_id in logged_events I would get the output that I am looking for
This is what I am getting
full_day | day | user_id | evs
---------------------+------------+---------+-----
2016-05-20 00:00:00 | 2016-05-20 | 4 | 21
2016-05-21 00:00:00 | 2016-05-21 | 4 | 3
2016-05-22 00:00:00 | 2016-05-22 | 4 | 5
2016-05-23 00:00:00 | 2016-05-23 | 4 | 47
2016-05-24 00:00:00 | 2016-05-24 | 4 | 26
2016-05-25 00:00:00 | 2016-05-25 | 4 | 2
2016-05-20 00:00:00 | 2016-05-20 | 19 | 11
2016-05-24 00:00:00 | 2016-05-24 | 19 | 8
And looking to have
full_day | day | user_id | evs
---------------------+------------+---------+-----
2016-05-20 00:00:00 | 2016-05-20 | 4 | 21
2016-05-21 00:00:00 | 2016-05-21 | 4 | 3
2016-05-22 00:00:00 | 2016-05-22 | 4 | 5
2016-05-23 00:00:00 | 2016-05-23 | 4 | 47
2016-05-24 00:00:00 | 2016-05-24 | 4 | 26
2016-05-25 00:00:00 | 2016-05-25 | 4 | 2
2016-05-20 00:00:00 | 2016-05-20 | 19 | 11
2016-05-21 00:00:00 | 2016-05-21 | 19 | NULL
2016-05-22 00:00:00 | 2016-05-22 | 19 | NULL
2016-05-23 00:00:00 | 2016-05-23 | 19 | NULL
2016-05-24 00:00:00 | 2016-05-24 | 19 | 8
2016-05-25 00:00:00 | 2016-05-25 | 19 | NULL