Pad row with default if values not found PostgresSQL

Question

I wanted to return the last 7 days of user_activity, but for those empty days I want to add 0 as value

Say I have this table

actions |  id   | date
------------------------
67      |  123  | 2019-07-7
90      |  123  | 2019-07-9
100     |  123  | 2019-07-10
50      |  123  | 2019-07-13
30      |  123  | 2019-07-15

and this should be the expected output , for the last 7 days

  actions |  id   | date
    ------------------------
    90      |  123  | 2019-07-9
    100     |  123  | 2019-07-10
    0       |  123  | 2019-07-11  <--- padded
    0       |  123  | 2019-07-12  <--- padded
    50      |  123  | 2019-07-13
    0       |  123  | 2019-07-14  <--- padded
    30      |  123  | 2019-07-15

Here is my query so far, I can only get the last 7 days but not sure if it's positive to add to default values

SELECT *
FROM user_activity
WHERE action_day > CURRENT_DATE - INTERVAL '7 days' 
ORDER BY uid, action_day

@Jeremy its the activity of a single user, so not necessarily — XDProgrammer, Jul 16 '19 at 05:31

Kaushik Nayak · Accepted Answer · 2019-07-16T05:10:33.247

5

You may left join your table with generate_series. First you need to have a way to use the rows for distinct ids. That set can then be correctly joined with the main table.

WITH days
AS (SELECT id,dt
    FROM (
        SELECT DISTINCT id FROM user_activity
        ) AS ids
  CROSS JOIN generate_series(
        CURRENT_DATE - interval '7 days', 
    CURRENT_DATE,  interval '1 day') AS dt
    )
SELECT  
     coalesce(u.actions,0)
    ,d.id
    ,d.dt
FROM days d LEFT JOIN user_activity u ON u.id = d.id AND u.action_day = d.dt

DEMO

edited Jul 16 '19 at 05:10

answered Jul 16 '19 at 05:04

Kaushik Nayak

30,772
5
32
45

1

+1. You beat me to it by 1 minute. BTW You do not need to coalesce id or dt - you can take them direct from days. Also the where clause is redundant as there are only 7 days in the cte. – Jonathan Willcock Jul 16 '19 at 05:09
@JonathanWillcock : You're right, I've modified it now. – Kaushik Nayak Jul 16 '19 at 05:10
impressive! thank you very much! I appreciate it guys – XDProgrammer Jul 16 '19 at 05:38

Pad row with default if values not found PostgresSQL

1 Answers1

Linked