2

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.

  • Why does June 18 falls into the range of June 20 - July 19? – mustaccio May 5 '17 at 13:06
  • It shouldn't, instead it should fall into May 20 - June 19. I've updated the answer accordingly. – Casey May 5 '17 at 13:39
  • Does the start day vary from customer to customer? Or is it always the 20th? – RDFozz May 5 '17 at 15:27
  • It varies from customer to customer. – Casey May 5 '17 at 16:23
0

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

  • Great! I will try this out, can you explain what the if day = 1 then 12 else previous bit means? Why 12? – Casey May 5 '17 at 12:53
  • Sorry, if Month=1 then Month=12, it's a typo. – McNets May 5 '17 at 12:54
0

May be you need something like this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.