SQL Queries Practice

--Step -1: 
--The ROW_NUMBER() function assigns a sequential number to each user's login based on login_date.
--The grp column groups consecutive dates by subtracting the ROW_NUMBER() value from the login_date.  
--Consecutive dates will have the same grp value.

SELECT user_id, 
login_date,  
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn,
login_date - INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) DAY AS grp
FROM 'user_logins_fixed.csv'
ORDER BY user_id, login_date;

--Step - 2
--Groups records by user_id and grp, then calculates the start_date, end_date,  
--and total consecutive_days for each group.
--Filters only groups with at least 5 consecutive days (COUNT(*) >= 5).  

with t1 as (
SELECT user_id, 
login_date,  
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn,
login_date - INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) DAY AS grp
FROM 'user_logins_fixed.csv'
ORDER BY user_id, login_date)

Select user_id, 
--grp,
min(login_date) as start_date,
max(login_date) as max_date,
count(*) as num_consecutive_logins
from t1
group by user_id, grp
HAVING num_consec_logins >= 5
order by 1,4 desc

SELECT
  user_id_token,
  event_timestamp_ct,
  LAG(event_timestamp_ct) OVER (PARTITION BY user_id_token ORDER BY event_timestamp_ct )
  AS previous_event_timestamp_ct,
  TIMESTAMPDIFF( MINUTE,LAG(event_timestamp_ct) OVER (PARTITION BY user_id_token ORDER BY event_timestamp_ct ),
  event_timestamp_ct ) AS duration_minutes
FROM
  your_table_name
ORDER BY
  user_id_token,
  event_timestamp_ct