SQL 面试高频题型

WITH distinct_logins AS (
    -- 第一步：去重
    SELECT DISTINCT user_id, login_date
    FROM user_logins
),
numbered AS (
    -- 第二步：编号
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        ) AS rn
    FROM distinct_logins
),
grouped AS (
    -- 第三步：计算分组标记
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS grp
    FROM numbered
),
streaks AS (
    -- 第四步：统计每段连续登录的天数
    SELECT
        user_id,
        grp,
        COUNT(*)       AS consecutive_days,
        MIN(login_date) AS start_date,
        MAX(login_date) AS end_date
    FROM grouped
    GROUP BY user_id, grp
    HAVING COUNT(*) >= 3
)
-- 最终结果：每个用户的最长连续登录天数
SELECT
    user_id,
    MAX(consecutive_days) AS 最长连续登录天数
FROM streaks
GROUP BY user_id
ORDER BY 最长连续登录天数 DESC;

WITH ranked AS (
    SELECT
        id,
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rk
    FROM employees
)
SELECT department, name, salary, rk
FROM ranked
WHERE rk <= 3
ORDER BY department, rk, name;

WITH first_day AS (
    -- 每个用户的首次活跃日
    SELECT
        user_id,
        MIN(action_date) AS first_date
    FROM user_actions
    GROUP BY user_id
),
retention AS (
    SELECT
        f.user_id,
        f.first_date,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM user_actions a
                WHERE a.user_id = f.user_id
                  AND a.action_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
            ) THEN 1
            ELSE 0
        END AS is_retained
    FROM first_day f
)
SELECT
    first_date              AS 日期,
    COUNT(*)                AS 新用户数,
    SUM(is_retained)        AS 次日留存用户数,
    ROUND(
        SUM(is_retained) / COUNT(*) * 100, 2
    )                       AS 次日留存率百分比
FROM retention
GROUP BY first_date
ORDER BY first_date;

WITH first_day AS (
    SELECT user_id, MIN(action_date) AS first_date
    FROM user_actions
    GROUP BY user_id
)
SELECT
    f.first_date,
    COUNT(DISTINCT f.user_id) AS 新用户数,
    COUNT(DISTINCT CASE
        WHEN DATEDIFF(a.action_date, f.first_date) = 1 THEN f.user_id
    END) AS 次日留存,
    COUNT(DISTINCT CASE
        WHEN DATEDIFF(a.action_date, f.first_date) = 3 THEN f.user_id
    END) AS 三日留存,
    COUNT(DISTINCT CASE
        WHEN DATEDIFF(a.action_date, f.first_date) = 7 THEN f.user_id
    END) AS 七日留存,
    COUNT(DISTINCT CASE
        WHEN DATEDIFF(a.action_date, f.first_date) = 30 THEN f.user_id
    END) AS 三十日留存
FROM first_day f
LEFT JOIN user_actions a
    ON f.user_id = a.user_id
    AND a.action_date >= f.first_date
GROUP BY f.first_date
ORDER BY f.first_date;

WITH monthly AS (
    SELECT
        DATE_FORMAT(sale_date, '%Y-%m') AS month,
        YEAR(sale_date)                 AS yr,
        MONTH(sale_date)                AS mn,
        SUM(amount)                     AS revenue
    FROM sales
    GROUP BY DATE_FORMAT(sale_date, '%Y-%m'),
             YEAR(sale_date), MONTH(sale_date)
)
SELECT
    month                                                       AS 月份,
    revenue                                                     AS 当月销售额,
    LAG(revenue, 1)  OVER (ORDER BY yr, mn)                     AS 上月销售额,
    LAG(revenue, 12) OVER (ORDER BY yr, mn)                     AS 去年同月销售额,
    CASE
        WHEN LAG(revenue, 1) OVER (ORDER BY yr, mn) IS NOT NULL
        THEN ROUND(
            (revenue - LAG(revenue, 1) OVER (ORDER BY yr, mn))
            / LAG(revenue, 1) OVER (ORDER BY yr, mn) * 100, 2)
    END AS 环比增长率,
    CASE
        WHEN LAG(revenue, 12) OVER (ORDER BY yr, mn) IS NOT NULL
        THEN ROUND(
            (revenue - LAG(revenue, 12) OVER (ORDER BY yr, mn))
            / LAG(revenue, 12) OVER (ORDER BY yr, mn) * 100, 2)
    END AS 同比增长率
FROM monthly
ORDER BY month;

SELECT
    student_name                                        AS 学生,
    MAX(CASE WHEN subject = '语文' THEN score END)      AS 语文,
    MAX(CASE WHEN subject = '数学' THEN score END)      AS 数学,
    MAX(CASE WHEN subject = '英语' THEN score END)      AS 英语,
    SUM(score)                                          AS 总分,
    ROUND(AVG(score), 1)                                AS 平均分
FROM scores
GROUP BY student_name
ORDER BY 总分 DESC;

-- 假设有宽表 wide_scores(student_name, chinese, math, english)
SELECT student_name, '语文' AS subject, chinese AS score FROM wide_scores
UNION ALL
SELECT student_name, '数学', math FROM wide_scores
UNION ALL
SELECT student_name, '英语', english FROM wide_scores
ORDER BY student_name, subject;

WITH running AS (
    SELECT
        sale_date,
        amount,
        SUM(amount) OVER (ORDER BY sale_date) AS cumulative_amount
    FROM daily_sales
)
SELECT sale_date AS 首次达标日期, cumulative_amount AS 累计销售额
FROM running
WHERE cumulative_amount >= 1000000
ORDER BY sale_date
LIMIT 1;

-- 先找出要保留的行
WITH keep AS (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY created_at DESC
            ) AS rn
        FROM contacts
    ) t
    WHERE rn = 1
)
-- 删除不在保留名单中的行
DELETE FROM contacts
WHERE id NOT IN (SELECT id FROM keep);

-- 方法一：利用 ROW_NUMBER 和总行数
WITH ordered AS (
    SELECT
        total_amount,
        ROW_NUMBER() OVER (ORDER BY total_amount) AS rn,
        COUNT(*) OVER () AS total_count
    FROM orders
    WHERE status = 'completed'
)
SELECT
    ROUND(AVG(total_amount), 2) AS 中位数
FROM ordered
WHERE rn IN (
    FLOOR((total_count + 1) / 2),
    CEIL((total_count + 1) / 2)
);

-- 方法二：利用 NTILE（更简洁但不够精确）
-- 适用于快速估算
WITH bucketed AS (
    SELECT
        total_amount,
        NTILE(2) OVER (ORDER BY total_amount) AS half
    FROM orders
    WHERE status = 'completed'
)
SELECT
    ROUND(
        (MAX(CASE WHEN half = 1 THEN total_amount END) +
         MIN(CASE WHEN half = 2 THEN total_amount END)) / 2
    , 2) AS 中位数近似值
FROM bucketed;