SQL - Calculate consecutive login points

 

CREATE TABLE user_login (
    user_id int,
    login_date date
);


INSERT INTO user_login VALUES 
(1, '2025-12-01'::date), 
(1, '2025-12-01'::date), 
(1, '2025-12-02'::date), 
(1, '2025-12-03'::date), 
(1, '2025-12-04'::date), 
(1, '2025-12-05'::date), 
(1, '2025-12-06'::date), 
(1, '2025-12-07'::date), 
(2, '2025-12-01'::date), 
(2, '2025-12-02'::date), 
(2, '2025-12-03'::date), 
(2, '2025-12-05'::date), 
(2, '2025-12-06'::date), 
(2, '2025-12-07'::date), 
(2, '2025-12-08'::date), 
(3, '2025-12-01'::date), 
(3, '2025-12-02'::date), 
(3, '2025-12-03'::date), 
(3, '2025-12-05'::date), 
(3, '2025-12-06'::date), 
(3, '2025-12-08'::date), 
(4, '2025-12-05'::date);

 

WITH t1 AS (
    SELECT DISTINCT user_id, login_date
      FROM user_login
),
t2 AS (
    -- Calculate a 'group' for consecutive dates
    SELECT user_id,
           login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
           login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day' AS grp
      FROM t1
),
t3 AS (
    -- Count consecutive logins within each group
    SELECT user_id,
           login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id, grp ORDER BY login_date) AS consecutive_login_days
      FROM t2
),
t4 AS (
    SELECT user_id,
           login_date,
           CASE
               WHEN consecutive_login_days = 7 THEN 10
               WHEN consecutive_login_days = 6 THEN 6
               WHEN consecutive_login_days = 3 THEN 3
               ELSE 0
           END AS points
      FROM t3 
),
t5 AS (
    SELECT user_id, MAX(points) AS max_points
      FROM t4
    GROUP BY user_id
),
t6 AS (
    SELECT a.user_id, SUM(a.points) AS points
      FROM t4 a, t5 b
     WHERE a.user_id = b.user_id AND a.points = b.max_points
     GROUP BY a.user_id
)
SELECT * FROM t6
ORDER BY user_id;