POSTGRESQL: How to run calculations for different dates

Question

I am trying to find out active users (grouped by age) for each month. I tried with subquery but i get an error. Is there decent way to do this? Thanks!

with first_date_of_month as
(
SELECT current_date - (interval '1 month' * s.a) AS dates FROM 
generate_series(0,24,1) AS s(a)
)


select q1.dates from first_date_of_month
where exists (select 
case when round ((CURRENT_DATE - date_of_birth)/365) =<18 then '0-18'
     ...
     when round ((CURRENT_DATE - date_of_birth)/365) >= 65 then '65+'
     Else 'N/A' end as "Age",
     count(1)
from users
and signup_date between q1.dates-INTERVAL '2 months' and q1.dates
group by 1 order by 1) ;

Your query makes no sense at all. Try showing sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jul 14, 2017 at 3:01

Nick · Accepted Answer · 2017-07-14 04:17:43Z

First, generate_series() can work with timestamps:

test=# select * from generate_series('2017-01-01', now(), interval '1 month');
    generate_series
------------------------
 2017-01-01 00:00:00+00
 2017-02-01 00:00:00+00
 2017-03-01 00:00:00+00
 2017-04-01 00:00:00+00
 2017-05-01 00:00:00+00
 2017-06-01 00:00:00+00
 2017-07-01 00:00:00+00
(7 rows)

Second, there is a special function to get ages, it's surprisingly called age() and returns intervals:

test=# select age(now(), '1981-11-18');
                   age
-----------------------------------------
 35 years 7 mons 26 days 03:07:41.561932

Next, you can extract years from intervals, with extract():

test=# select extract(year from age(now(), '1981-11-18'));
 date_part
-----------
        35
(1 row)

Finally, as far as I understand, you want to get counts of users grouped by age withing each month -- so this looks like you need 2 levels of grouping.

As a result, we get this (I use multiple CTE stages here, implicit CROSS JOIN at the 2nd CTE stage and finally, I reduce the number of "age" groups as you wanted in the main CTE query, when groups with "raw" ages are already obtained):

with dates(month) as (
  select generate_series(
    date_trunc('day', now() - interval '2 year'),
    now(), interval '1 month'
  )
), usrs_full_age as (
  select
    month,
    extract(year from age(now(), date_of_birth)) as age,
    count(*) as count
  from users u, dates
  where signup_date between month - interval '2 month' and month
  group by 1, 2
)
select
  month::date,
  case
    when age <= 18 then '0-18'
    -- ...
    else 'N/A' -- NULLs will go here (records with empty date_of_birth)
  end as age,
  sum(count) as count
from usrs_full_age
group by 1, 2
order by 1, 2
;

Collectives™ on Stack Overflow

POSTGRESQL: How to run calculations for different dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related