WITH N1 AS( SELECT *, DENSE_RANK() OVER(PARTITION BY user_id ORDER BY fdate ASC) AS rnk FROM tb_dau ), N2 AS( SELECT user_id, COUNT(*) AS cnt FROM N1 GROUP BY user_id, DATE_SUB(fdate, INTERVAL rnk DAY) ) SELECT user_id, MAX(cnt) AS max_consec_days FROM N2 GROUP BY user_id
