Count seen users per week

My input table looks like:

id	user_id	at
1	1	2020-12-07 13:07:35
2	794	2020-12-07 13:12:17
3	794	2020-12-07 13:12:26
4	794	2020-12-07 13:12:27
5	263	2020-12-07 13:12:50
6	263	2020-12-07 13:12:50
7	263	2020-12-07 13:13:10
8	1029	2020-12-07 13:18:07
9	1029	2020-12-07 13:18:07
10	378	2020-12-07 13:19:10
11	378	2020-12-07 13:19:10

This table grows over time and will persist every time a user is seen. So one user_id can occurs many times per day. Now I’m looking for a SQL query to give me this result:

week	one-time	two-times	more-than-two-times
40	22	4	12
41	34	12	31
42	…	…	…

A user should count one-time per day and column separated how often per week. Any suggestions?

EDIT:

this seems a valid result:

select
    t.yyyyww,
    SUM(t.num_days = 1) as cnt_1,
    SUM(t.num_days = 2) as cnt_2,
    SUM(t.num_days > 2) as cnt_3plus
from
    (
    select
        WEEKOFYEAR(`at`) as yyyyww, user_id, COUNT(distinct DATE(`at`)) as num_days
    from
        user_seen
    group by
        yyyyww, user_id ) t
group by
    t.yyyyww

Answer

If I understand correctly, you want to count the number of days per week (“a user should count one-time per day”). For this, you can use conditional aggregation:

select yyyyww, 
       sum(num_days = 1) as cnt_1,
       sum(num_days = 2) as cnt_2,
       sum(num_days > 2) as cnt_3plus
from (select yearweek(at) as yyyyww, user_id,
             count(distinct date(at)) as num_days
      from t
      group by yyyyww, user_id
     ) t
group by yyyyww