2

I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01.

Is there any way possible or workaround I can do to say that the first month of first quarter is for instance September? So instead of the traditional: Q1: 1-3, Q2: 4-6, Q3: 7-9, Q4: 10-12

I want to be able to specify which month is the start of Q1, so if I say September it should become: Q1: 9-11, Q2: 12-2, Q3: 3-5, Q4: 6-8

Here is how I make a standard quarterly score average with default quarter.

SELECT id,
       name,
       date_trunc('quarter', date) AS date,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

I'm open for all suggestions to make this work.

0

2 Answers 2

3

If you don't really need the start date of the quarter, you can easily do this with a CASE expression:

SELECT id,
       name,
       case 
         when extract(month from date) in (9,10,11) then 'Q1'
         when extract(month from date) in (12,1,2) then 'Q2'
         when extract(month from date) in (3,4,5) then 'Q3'
         when extract(month from date) in (6,7,8) then 'Q4'
       end as quarter,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

Of course you can put that into a stored function to make your queries simpler.

0
2

You don't actually use the number of the quarter, just the timestamps. So all you need is an offset, which you subtract and then add back after truncating, which is interval '2 month' for your given example:

SELECT id, date
     , date_trunc('quarter', date - interval '2 month')
                                  + interval '2 month' AS quarter
FROM   player_daily_score;

db<>fiddle here

However, your query makes no sense. Including the id column (assuming it's the PK) in GROUP BY is a noise operation, as nothing will be aggregated. This might make sense:

SELECT date_trunc('quarter', date - interval '2 month')
                                  + interval '2 month' AS quarter
     , country
     , device
     , AVG(rank) AS avg_rank
     , AVG(score) AS avg_score
FROM   player_daily_score
GROUP  BY 1,2,3
ORDER  BY 1 desc;

If id is not the PK, your database design can profit from normalization.

1
  • Yeah my table structure is not the best. I've kept all data in the same table and have not normalized it. Will maybe do it in the future. Regarding id, it's player_id, I have not declared any serial ID as a PK. I have multiple columns as the PK. As for the solution, I appreciate it and learned that offsets can be done like this, but I will stick with the other answer as its easier to avoid mistakes that way.
    – apple212
    Feb 18, 2021 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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