How to use SQL to count events in the first week

Question

I'm trying to write a SQL query, which says how many logins each user made in their first week.

Assume, for the purpose of this question, that I have a table with at least user_id and login_date. I'm trying to produce an output table with user_id and num_logins_first_week

Answer 1

Use aggregation to get the first date for each user. Then join in the logins and aggregate:

select t.user_id, count(*) as num_logins_first_week
from t join
     (select user_id, min(login_date) as first_login_date
      from t
      group by user_id
     ) tt
     on tt.user_id = t.user_id and
        t.login_date >= tt.first_login_date and
        t.login_date < tt.first_login_date + interval 7 day
group by t.user_id;