CODEWITHSUNDEEP
sqlpostgresql
bgame2498
bgame2498

Reputation: 4857

Postgres: Count over a series of days

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

Upvotes: 0

Views: 168

Answers (2)

Adam
Adam

Reputation: 5599

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'.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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.

Upvotes: 1

Related Questions