PostgreSQL Aggregation – How to Aggregate Transactional Records by Subscription Period

aggregatepostgresql

I have a table containing transactional events for customers:

table: events
| id   | cust_id  | date                       | amount | 
|------|----------|----------------------------|--------| 
| 78   | 211      | 2010-06-18 20:10:00.000000 | 20     | 
| 84   | 211      | 2010-07-18 05:53:00.000000 | 35     | 
| 91   | 211      | 2010-07-27 05:45:00.000000 | 25     | 
| 2136 | 211      | 2011-02-14 20:07:00.000000 | 5000   | 
| 2947 | 211      | 2011-02-21 20:29:25.000000 | 22     | 
| 2945 | 211      | 2011-03-14 20:48:26.000000 | 22     |

I want to produce a year and month aggregation of the sum of the amounts, but where the month periods correspond to the customer's subscription period. This period has a specific start date, for example the 20th of the month.

The query can take the inputs:

  • customer id – the customer to generate the report for
  • start day of period – the day of the month on which the "month" should start

Here is a query that naively groups by month without the custom period:

SELECT
  extract(YEAR FROM date)  AS year,
  extract(MONTH FROM date) AS month,
  sum(amount)              AS amount
FROM events
WHERE cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

Example output of this naive query:

| year | month | amount | 
|------|-------|--------| 
| 2010 | 6     | 20     | 
| 2010 | 7     | 60     | 
| 2011 | 2     | 5022   | 
| 2011 | 3     | 22     |

But, given that start day for customer 211 is the 20th of the month, I want the actual output to be:

| year | month | amount | 
|------|-------|--------| 
| 2010 | 5     | 20     |  from May 20 - June 19
| 2010 | 6     | 35     |  from June 20 - July 19
| 2010 | 7     | 25     |  from July 20 - Aug 19
| 2011 | 1     | 5000   |  from Jan 20 - Feb 19
| 2011 | 2     | 44     |  from Feb 20 - March 19

I'm at a loss for how to go from my naive query to the query I need to generate this output.

Best Answer

First it calculates month according to next formula:

Month = If day < 20 then, if month = 1 then month = 12 else previous month

Obviously you can change day 20 by no matter which other day.

NOTE: As far as I understand, this date 2010-06-18 20:10:00.000000 should returns Month=5 due Day=18

with calcMonth as
(
    SELECT extract(YEAR FROM date)  AS year,
           case when extract(day from date) < 20 
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date) 
                end AS month,
            amount,
            cust_id
    FROM  events
)
SELECT year,
       month,
       sum(amount) AS amount
FROM   calcMonth
WHERE  cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

year | month | amount
:--- | :---- | -----:
2010 | 5     |     20
2010 | 6     |     35
2010 | 7     |     25
2011 | 1     |   5000
2011 | 2     |     44

dbfiddle here

These are values returned by CTE:

    SELECT date as Date,
           extract(YEAR FROM date)  AS Year,
           extract(day from date) AS Day,
           extract(MONTH FROM date) AS Month,
           case when extract(day from date) < 20 
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date) 
                end AS CalMonth,
            Amount
    FROM  events

date                | year | day | month | calmonth | amount
:------------------ | :--- | :-- | :---- | :------- | -----:
2010-06-18 20:10:00 | 2010 | 18  | 6     | 5        |     20
2010-07-18 05:53:00 | 2010 | 18  | 7     | 6        |     35
2010-07-27 05:45:00 | 2010 | 27  | 7     | 7        |     25
2011-02-14 20:07:00 | 2011 | 14  | 2     | 1        |   5000
2011-02-21 20:29:25 | 2011 | 21  | 2     | 2        |     22
2011-03-14 20:48:26 | 2011 | 14  | 3     | 2        |     22

dbfiddle here

Related Question