Aggregate count by several weeks after field data in PostgreSQL -


i have query returns that:
registered_at - date of user registration;
action_at - date of kind of action.

 |       registered_at | user_id |           action_at | ------------------------------------------------------- | 2015-05-01 12:00:00 |       1 | 2015-05-04 12:00:00 | | 2015-05-01 12:00:00 |       1 | 2015-05-10 12:00:00 | | 2015-05-01 12:00:00 |       1 | 2015-05-16 12:00:00 | | 2015-04-01 12:00:00 |       2 | 2015-04-04 12:00:00 | | 2015-04-01 12:00:00 |       2 | 2015-04-05 12:00:00 | | 2015-04-01 12:00:00 |       2 | 2015-04-10 12:00:00 | | 2015-04-01 12:00:00 |       2 | 2015-04-30 12:00:00 | 

i'm trying implement query returns me that:
weeks_after_registration - in example limited 3, in real task limited 6.

 | user_id |  weeks_after_registration | action_counts | ------------------------------------------------------- |       1 |                         1 |             1 | |       1 |                         2 |             1 | |       1 |                         3 |             1 | |       2 |                         1 |             2 | |       2 |                         2 |             1 | |       2 |                         3 |             0 | 

you can use extract(days (action_at - registered_at) / 7)+1 number of weeks. count number of actions grouped number of weeks.

  select user_id, wk, count(*) actions   (select user_id, extract(days (action_at - registered_at) / 7)+1 wk table1)   wk <= 3   group user_id, wk 

if must display rows action_counts = 0 in result, need join possible week numbers (1, 2, 3) , possible user_ids (1, 2) like:

select b.user_id, a.wk, coalesce(c.actions, 0) actions (select * generate_series(1, 3) wk) join (select distinct user_id table1) b on true left join (   select user_id, wk, count(*) actions   (select user_id, extract(days (action_at - registered_at) / 7)+1 wk table1)   wk <= 3   group user_id, wk ) c on a.wk = c.wk , b.user_id = c.user_id order b.user_id, a.wk; 

fiddle


Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

Kivy: Swiping (Carousel & ScreenManager) -