Aggregate count by several weeks after field data in PostgreSQL

Question

I have a query returns something like that:
registered_at - date of user registration;
action_at - date of some 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 to implement query that will returns me something like that:
weeks_after_registration - in this example limited by 3, in real task it will be limited by 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 |

Answer 1

You can use extract(days from (action_at - registered_at) / 7)+1 to get the number of weeks. Then count the number of actions grouped by the number of weeks.

  select user_id, wk, count(*) actions
  from (select user_id, extract(days from (action_at - registered_at) / 7)+1 wk from Table1) a
  where wk <= 3
  group by user_id, wk

If you must display rows where action_counts = 0 in the result, then you need to join with the all possible week numbers (1, 2, 3) and all possible user_ids (1, 2) like:

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

fiddle