Postgres LEFT JOIN with recurring dates and users

Question

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

Answer 1

You have to do with users the same thing you're doing with days:

WITH
  days AS (
    SELECT generate_series(current_date-7, current_date, '1d')::date AS day
    ),
  eves AS (
    SELECT user_id, created_at::date AS full_day, COUNT(*) as evs
    FROM logged_events
    WHERE logged_events.created_at >= current_date-6
    GROUP BY user_id, full_day
    ),
  users AS (
    SELECT DISTINCT user_id FROM eves
    )
SELECT eves.full_day, days.day, users.user_id, eves.evs
FROM days
CROSS JOIN users
LEFT JOIN eves ON (eves.full_day = days.day AND eves.user_id = users.user_id)
GROUP BY users.user_id, days.day;