CODEWITHSUNDEEP
sqlpostgresql
XDProgrammer
XDProgrammer

Reputation: 861

Pad row with default if values not found PostgresSQL

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

Upvotes: 2

Views: 57

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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

Upvotes: 5

Related Questions