3

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
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.