CREATE TABLE continues_time (
    sign_date DATE,
    user_id INT,
    continues_time TIME
);
INSERT INTO continues_time (sign_date, user_id, continues_time)
VALUES
('2023-08-01', 1001, '09:00:00.0'),
('2023-08-01', 1002, '10:30:00.0'),
('2023-08-01', 1003, '11:45:00.0'),
('2023-08-01', 1004, '14:20:00.0'),
('2023-08-02', 1001, '08:39:00.0'),
('2023-08-02', 1002, '11:15:00.0'),
('2023-08-02', 1003, '13:49:00.0'),
('2023-08-02', 1004, '15:50:00.0'),
('2023-08-03', 1001, '10:00:00.0'),
('2023-08-03', 1002, '12:30:00.0'),
('2023-08-03', 1003, '14:10:00.0'),
('2023-08-03', 1004, '16:45:00.0'),
('2023-08-04', 1001, '09:45:00.0'),
('2023-08-04', 1002, '12:15:00.0'),
('2023-08-04', 1003, '14:30:00.0'),
('2023-08-04', 1004, '16:20:00.0'),
('2023-08-05', 1001, '18:00:00.0'),
('2023-08-05', 1002, '12:40:00.0');

-- 目标：统计在同一用户内，相邻两次打点（日期+时间）差值 < 1500 分钟的记录数量（步聚 3 所需指标）
-- 步骤 1：日期排序 —— 为每个用户的时间记录按时间顺序分配行号 ROW_NUMBER()
WITH a AS (
    SELECT
        user_id,
        UNIX_TIMESTAMP(CONCAT(sign_date, ' ', continues_time)) AS unix_timestamp,  -- 将日期与时间合并为时间戳，便于差值计算
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY CONCAT(sign_date, ' ', continues_time)
        ) AS num  -- 步骤 1：日期排序生成的序号
    FROM continues_time
),
b AS (
    -- 与 CTE a 相同：用于实现 “序号错位” 自连接（步骤 2a）
    SELECT
        user_id,
        UNIX_TIMESTAMP(CONCAT(sign_date, ' ', continues_time)) AS unix_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY CONCAT(sign_date, ' ', continues_time)
        ) AS num
    FROM continues_time
)
SELECT
    a.user_id,
    COUNT(a.user_id) AS cnt  -- 步骤 3：根据题目要求统计满足条件的次数
FROM a
LEFT JOIN b ON a.user_id = b.user_id
    AND a.num = b.num - 1      -- 步骤 2a：序号错位相减：将当前行与“下一行”配对
WHERE
    (b.unix_timestamp - a.unix_timestamp)/60 < 1500  -- 步骤 2：计算相邻时间差（单位：分钟）并筛选
GROUP BY a.user_id;

-- 目标相同：统计同一用户内，相邻两次时间差 < 1500 分钟的记录数量
WITH base AS (
    SELECT
        user_id,
        UNIX_TIMESTAMP(CONCAT(sign_date, ' ', continues_time)) AS unix_timestamp,
        LAG(UNIX_TIMESTAMP(CONCAT(sign_date, ' ', continues_time)), 1) OVER (
            PARTITION BY user_id
            ORDER BY CONCAT(sign_date, ' ', continues_time)
        ) AS prev_unix_timestamp  -- 步骤 2b：窗口函数 LAG() 获取“上一条”时间戳
    FROM continues_time
)
SELECT
    user_id,
    COUNT(*) AS cnt  -- 步骤 3：统计满足相邻差值条件的次数
FROM base
WHERE
    prev_datetime IS NOT NULL
    AND (unix_timestamp - prev_unix_timestamp)/60 < 1500  -- 步骤 2b：计算与上一条的时间差（分钟）
GROUP BY user_id;

-- 关键步骤：
--   步骤 1：提取“去重后的登录日期”（按天）；
--   步骤 2：计算与上一登录日期的差值 diffdate（单位：天）；
--   步骤 3：根据 diffdate > 2 标记新的分段 part_id（累积求和实现分段编号）；
--   步骤 4：按用户与分段聚合得到开始日期、结束日期并计算连续跨度 day_count（含缺失天的跨距 = MAX - MIN + 1）。

WITH distinct_login_dates AS (  -- 步骤 1：规范化为日期粒度并去重
    SELECT
        user_id,
        DATE_FORMAT(login_datetime, '%Y-%m-%d') AS login_date
    FROM login_events
    GROUP BY
        user_id,
        DATE_FORMAT(login_datetime, '%Y-%m-%d')
),
diff_calc AS (  -- 步骤 2：计算与上一条日期的间隔（天）
    SELECT
        user_id,
        login_date,
        DATEDIFF(
            login_date,
            LAG(login_date, 1) OVER (
                PARTITION BY user_id
                ORDER BY login_date
            )
        ) AS diffdate
    FROM distinct_login_dates
),
partitions AS (  -- 步骤 3：累积分段编号（间隔 > 2 天则开启新分段）
    SELECT
        user_id,
        login_date AS login_datetime,  -- 保留原字段命名风格（原代码后续称为 login_datetime）
        SUM(
            IF(diffdate > 2, 1, 0)
        ) OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) AS part_id,
        diffdate
    FROM diff_calc
),
aggregated AS (  -- 步骤 4：对每个分段做聚合
    SELECT
        user_id,
        part_id,
        MIN(login_datetime) AS start_login_date,
        MAX(login_datetime) AS end_login_date,
        DATEDIFF(MAX(login_datetime), MIN(login_datetime)) + 1 AS day_count
    FROM partitions
    GROUP BY
        user_id,
        part_id
)
SELECT
    user_id,
    start_login_date,
    day_count
FROM aggregated;

WITH recent AS (  -- 去重 + 近 7 天
    SELECT DISTINCT
        user_id,
        event_date
    FROM continues_time
    WHERE DATEDIFF(CURRENT_DATE(), event_date) <= 7
),
seq AS (  -- 计算锚点 + 段内长度
    SELECT
        user_id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_date
        ) AS rn,
        DATE_SUB(event_date, rn) AS dense_date
    FROM recent
),
seg AS (
    SELECT
        user_id,
        dense_date,
        COUNT(*) AS seg_len
    FROM seq
    GROUP BY
        user_id,
        dense_date
)
SELECT
    user_id,
    MAX(seg_len) AS result
FROM seg
GROUP BY user_id;

WITH ranked AS (
  SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (
        PARTITION BY user_id 
        ORDER BY score 
        DESC
    ) AS rn
  FROM score_table
)
SELECT *
FROM ranked
WHERE rn <= N;

-- 建表
CREATE TABLE event_log (
    event        BIGINT,
    event_type   VARCHAR(50),
    event_time   TIMESTAMP
);

-- 插入示例数据
INSERT INTO event_log (event, event_type, event_time) VALUES
(1037176,'首次','2024-04-27 22:10:00'),
(1037176,'催催','2024-04-28 10:20:00'),
(1037176,'砸开','2024-04-28 18:42:00'),
(1037176,'催催','2024-04-28 18:49:00'),
(1037176,'首次','2024-04-29 10:20:00'),
(1037177,'首次','2024-04-29 10:20:00'),
(1037176,'催催','2024-05-01 10:20:00'),
(1037177,'砸开','2024-04-30 10:20:00'),
(1037176,'催催','2024-05-01 17:20:00'),
(1037176,'首次','2024-05-02 10:20:00'),
(1037177,'催催','2024-05-01 10:20:00');

SELECT
    count_money.time,
    SUM(count_money.money) OVER (
        ORDER BY count_money.time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cum_money
FROM count_money
ORDER BY time;

WITH part AS (
    SELECT
        event,
        event_type,
        event_time,
        -- 分段编号：每遇到 '首次' 累加
        SUM(CASE WHEN event_type = '首次' THEN 1 ELSE 0 END)
            OVER (PARTITION BY event ORDER BY event_time) AS part_id
    FROM event_log
)
SELECT
    event,
    event_time,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY event, part_id ORDER BY event_time) AS rn
FROM part
ORDER BY event, event_time;

WITH events AS (           -- Step 1: 将区间拆成事件流
    SELECT course_id, in_datetime  AS event_time,  1  AS delta
    FROM attend_tb
    UNION ALL
    SELECT course_id, out_datetime AS event_time, -1 AS delta
    FROM attend_tb
),
ordered AS (               -- Step 2: 按课程与时间排序（控制同一时间点的处理顺序）
    SELECT
        course_id,
        event_time,
        delta
    FROM events
    -- 如果同一 event_time 既有进入(+1)又有离开(-1)，
    -- 选择 delta 排序方向决定并发瞬时的处理语义：
    ORDER BY event_time, delta DESC  -- 先加后减（并发尽量不降）
--  ORDER BY event_time, delta ASC   -- 先减后加（并发先下降再上升）
),
running AS (               -- Step 3: 前缀和 = 当前时刻并发在线人数
    SELECT
        course_id,
        event_time,
        SUM(delta) OVER (
            PARTITION BY course_id
            ORDER BY event_time, delta ASC   -- 这里采用“先处理离开(-1)”语义，可按需要改成 DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS concurrent_cnt
    FROM ordered
),
max_concurrent AS (        -- Step 4: 每门课程的最大并发
    SELECT
        course_id,
        MAX(concurrent_cnt) AS max_num
    FROM running
    GROUP BY course_id
)
SELECT                      -- Step 5: 关联课程名称输出结果
    c.course_id,
    c.course_name,
    m.max_num
FROM max_concurrent m
JOIN course_tb c USING (course_id)
ORDER BY c.course_id;

-- 下面代码默认 time 是日期-时间的格式，如果仅仅是日期格式，可以不用 date(time)
WITH a AS (
    SELECT DISTINCT
        user_id,
        DATE(time) AS time,
        MIN(DATE_ADD(DATE(time), 1)) OVER (PARTITION BY user_id) AS next_day_time
    FROM user_login
),
b AS (
    SELECT
        user_id,
        -- 用户不是次日留存，指标就是 0；是次日留存，指标就是 1（因为已经去重了）
        SUM(IF(time = next_day_time, 1, 0)) AS if_next_keep
    FROM a
    GROUP BY user_id
)
-- 计算次日留存率
SELECT
    ROUND(SUM(if_next_keep) / COUNT(1), 2) AS next_keep_rate
FROM b;

-- 计算次日留存率：用户首登次日是否再次登录
WITH a AS ( 
    SELECT
        user_id,
        MIN(time) AS first_log_time
    FROM user_login
    GROUP BY user_id
), 
b AS ( 
    SELECT
        t1.user_id,
        -- 如果用户次日登录了，那就是1，否则就是0
        -- CASE WHEN语句如果没有指定ELSE，则默认值为NULL
        -- COUNT(不会统计NULL)
        COUNT(DISTINCT CASE WHEN DATEDIFF(time, first_log_time) = 1 THEN time END) AS day1_retention
    FROM user_login AS t1
    JOIN a AS t2 ON t1.user_id = t2.user_id  
    GROUP BY t1.user_id
) 
SELECT
    ROUND(SUM(day1_retention) / COUNT(1), 2) AS next_keep_rate
FROM b;

SELECT
    product_id,
    MAX(CASE WHEN store = 'store1' THEN price END) AS store1,
    MAX(CASE WHEN store = 'store2' THEN price END) AS store2,
    MAX(CASE WHEN store = 'store3' THEN price END) AS store3
FROM Products
GROUP BY product_id
ORDER BY product_id;

WITH a AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) AS id,
        name,
        continent
    FROM student
)
SELECT
    MAX(CASE continent WHEN 'America' THEN name ELSE NULL END) AS America,
    MAX(CASE continent WHEN 'Europe'  THEN name ELSE NULL END) AS Europe,
    MAX(CASE continent WHEN 'Asia'    THEN name ELSE NULL END) AS Asia
FROM a
GROUP BY id;

SELECT '[0-5>' AS bin, COUNT(*) AS total 
FROM Sessions 
WHERE duration BETWEEN 0 AND 5*60 
UNION ALL 
SELECT '[5-10>' AS bin, COUNT(*) AS total 
FROM Sessions 
WHERE duration BETWEEN 5*60 AND 10*60 
UNION ALL 
SELECT '[10-15>' AS bin, COUNT(*) AS total 
FROM Sessions 
WHERE duration BETWEEN 10*60 AND 15*60 
UNION ALL 
SELECT '15 or more' AS bin, COUNT(*) AS total 
FROM Sessions 
WHERE duration >= 15*60