Problem Overview

This problem involves calculating bonus coins earned from consecutive daily check-ins. The challenge lies in correctly identifying continuous check-in periods and applying bonus rules at specific intervals.

Table Structure

Table: tb_user_log

Core Concepts

Bonus Rules

• Base: 1 coin per day
• Every 3rd consecutive day: +2 bonus coins (total 5)
• Every 7th consecutive day: +6 bonus coins (total 13)
• Breaks reset the counter

Key Insight

The critical challenge is distinguishing between continuous and broken check-in sequences. The solution involves using date arithmetic combined with ranking to create session labels for each continuous check-in period.

When we subtract the row number from the actual check-in date, identical results indicate belonging to the same continuous session. When a break occurs, the date difference changes because the date shifts by at least one day while the row number resets.

Step-by-Step Solution

Step 1: Filter and Rank

First, filter the releavnt records and assign sequential row numbers within each user's check-in sequence:

SELECT 
    uid, 
    DATE(in_time) AS dt1,
    ROW_NUMBER() OVER (
        PARTITION BY uid 
        ORDER BY DATE(in_time)
    ) AS rn
FROM tb_user_log
WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
    AND article_id = 0
    AND sign_in = 1;

Step 2: Identify Sessions

Calculate the difference between the check-in date and row number to establish session boundaries:

SELECT 
    uid,
    dt1,
    DATE_SUB(dt1, INTERVAL rn DAY) AS session_key
FROM step1_result;

Step 3: Calculate Consecutive Day Numbers

Rank records within each session to determine which consecutive day each check-in represents:

SELECT 
    uid,
    dt1,
    ROW_NUMBER() OVER (
        PARTITION BY uid, session_key 
        ORDER BY dt1
    ) AS day_num
FROM step2_result;

Step 4: Apply Bonus Rules

Use modulo arithmetic to calculate coins based on the consecutive day number:

SELECT 
    uid,
    dt1,
    day_num,
    1 AS base_coins,
    CASE 
        WHEN day_num % 3 = 0 THEN 2 
        ELSE 0 
    END AS bonus_3,
    CASE 
        WHEN day_num % 7 = 0 THEN 6 
        ELSE 0 
    END AS bonus_7,
    (1 + 
        CASE WHEN day_num % 3 = 0 THEN 2 ELSE 0 END + 
        CASE WHEN day_num % 7 = 0 THEN 6 ELSE 0 END
    ) AS total_coins
FROM step3_result;

Step 5: Final Aggregation

Combine all steps using CTEs for better readability:

WITH filtered AS (
    SELECT 
        uid,
        DATE(in_time) AS dt1,
        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY DATE(in_time)) AS rn
    FROM tb_user_log
    WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
        AND article_id = 0
        AND sign_in = 1
),
grouped AS (
    SELECT 
        uid,
        dt1,
        DATE_SUB(dt1, INTERVAL rn DAY) AS session_key
    FROM filtered
),
ranked AS (
    SELECT 
        uid,
        dt1,
        ROW_NUMBER() OVER (PARTITION BY uid, session_key ORDER BY dt1) AS day_num
    FROM grouped
),
calculated AS (
    SELECT 
        uid,
        dt1,
        day_num,
        1 + 
            CASE WHEN day_num % 3 = 0 THEN 2 ELSE 0 END +
            CASE WHEN day_num % 7 = 0 THEN 6 ELSE 0 END AS coins
    FROM ranked
)
SELECT 
    uid,
    dt1,
    day_num,
    coins
FROM calculated
ORDER BY uid, dt1;

Summary

The solution leverages window functions to create session identifiers through date arithmetic. By partitioning on both user ID and the computed session key, we can accurately track consecutive days within each uninterrupted check-in period. The modulo operator handles the periodic bonus multipliers elegantly.