Viết SQL tìm những user đăng nhập ít nhất 3 ngày liên tiếp (bài gaps and islands).

Mẹo là lấy ngày trừ đi số thứ tự của chính nó: trong một chuỗi ngày liên tiếp, ngày tăng 1 và ROW_NUMBER() cũng tăng 1 nên hiệu số không đổi.

• Chuỗi bị ngắt thì hiệu số nhảy sang giá trị khác.
• Mỗi giá trị hiệu số là một "hòn đảo" (island).

WITH days AS (
  SELECT DISTINCT user_id, CAST(login_at AS DATE) AS login_date
  FROM logins
),
grouped AS (
  SELECT user_id, login_date,
         login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
  FROM days
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Ví dụ user đăng nhập ngày 1, 2, 3 và 5: số thứ tự là 1, 2, 3, 4, hiệu số là 0, 0, 0, 1. Ngày 1–3 thành một nhóm 3 ngày, ngày 5 thành nhóm riêng.

Bước DISTINCT theo ngày là bắt buộc: một ngày đăng nhập hai lần làm số thứ tự tăng 2 trong khi ngày chỉ tăng 1, chuỗi bị cắt sai. Cú pháp trừ ngày tuỳ DB: PostgreSQL date - integer, BigQuery DATE_SUB(login_date, INTERVAL rn DAY), Spark SQL date_sub(login_date, rn).

Lưu ý: cách thứ hai là dùng LAG so với ngày trước, đánh dấu chỗ ngắt rồi cộng dồn thành mã nhóm. Cách này tổng quát hơn khi "liên tiếp" không phải đúng 1 ngày, ví dụ các sự kiện cách nhau tối đa 30 phút, tức bài sessionization.