Postgres: Count over a series of days

Question

I have created the following query which returns 3 values for 1 day ('20170731'). What I am struggling to figure out is how do I run this query for everyday in series from 30 days ago to 60 days from now and return a row for each day.

SELECT DATE_TRUNC('day', '20170731'::TIMESTAMP),
       COUNT(CASE WHEN state NOT IN ('unsub','skipped', 'error') THEN 1 ELSE NULL END) AS a,
       COUNT(CASE WHEN (state IN ('unsub')) AND (DATE_TRUNC('month', unsub_at) BETWEEN '20170731' AND DATE_TRUNC('day', NOW()))  THEN 1 ELSE NULL END) AS b,
       COUNT(CASE WHEN (state IN ('skipped')) AND (DATE_TRUNC('month', skipped_at) BETWEEN '20170731' AND DATE_TRUNC('day', NOW()))  THEN 1 ELSE NULL END) AS c          
FROM subscriptions
WHERE DATE_TRUNC('day', run) >= '20170731'
      AND DATE_TRUNC('day', created_at) <= '20170731'
ORDER BY 1

Gordon Linoff · Accepted Answer · 2017-08-08 17:55:17Z

1

You can use generate_series() to generate the dates. The idea is:

SELECT gs.dte,
       SUM( (state NOT IN ('unsub','skipped', 'error'))::int) AS a,
       SUM( (state IN ('unsub') AND DATE_TRUNC('month', unsub_at) BETWEEN gs.dte AND DATE_TRUNC('day', NOW()))::int) AS b,
       SUM( (state IN ('skipped') AND DATE_TRUNC('month', skipped_at) BETWEEN gs.dte AND DATE_TRUNC('day', NOW()))::int) AS c          
FROM subscriptions s CROSS JOIN
     generate_series(current_date - interval '30 day',
                     current_date + interval '60 day',
                     interval '1 day'
                    ) gs(dte)
WHERE DATE_TRUNC('day', run) >= gs.dte AND
      DATE_TRUNC('day', created_at) <= gs.dte
GROUP BY gs.dte
ORDER BY 1;

I switched the query to cast the booleans as integers -- I just find that easier to follow.

answered Aug 8, 2017 at 17:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bgame2498 Over a year ago

Thank you! I knew I needed to generate a series of days but didn't know how to implement.

Adam · Accepted Answer · 2017-08-08 17:58:25Z

See Set Returning Functions. The generate_series function is what you want.

First check this, so you know what it does:

SELECT
    *
FROM
    generate_series(
        '2017-07-31'::TIMESTAMP - INTERVAL '30 days', 
        '2017-07-31'::TIMESTAMP + INTERVAL '60 days', 
        INTERVAL '1 day');

Then your query could look something like that:

SELECT DATE_TRUNC('day', stamp),
       COUNT(CASE WHEN state NOT IN ('unsub','skipped', 'error') THEN 1 ELSE NULL END) AS a,
       COUNT(CASE WHEN (state IN ('unsub')) AND (DATE_TRUNC('month', unsub_at) BETWEEN '20170731' AND DATE_TRUNC('day', NOW()))  THEN 1 ELSE NULL END) AS b,
       COUNT(CASE WHEN (state IN ('skipped')) AND (DATE_TRUNC('month', skipped_at) BETWEEN stamp AND DATE_TRUNC('day', NOW()))  THEN 1 ELSE NULL END) AS c          
FROM subscriptions,
     generate_series('2017-07-31'::TIMESTAMP - INTERVAL '30 days', '2017-07-31'::TIMESTAMP + INTERVAL '60 days', INTERVAL '1 day') AS stamp
WHERE DATE_TRUNC('day', run) >= stamp
      AND DATE_TRUNC('day', created_at) <= stamp
ORDER BY 1

Just add generate_series function as you would do with plain input table (alias it AS stamp), JOIN with subscriptions (cartesian product) and use stamp value instead of hard-coded '20170731'.

Collectives™ on Stack Overflow

Postgres: Count over a series of days

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related