面试速查:SQL 手写要点

面试速查:SQL 手写要点

Hello,我是小兔大白糖。 这篇整理面试口述与高频追问要点,方便对照站内同主题通识文。


正文

定位:面试前 2 小时速查 + 白板现场填空式复用。 原则:每道题都是 大型加密货币交易所/Risk 真实业务场景,背 schema + 套模板即可。 方言:MySQL 8.0+ / StarRocks / ClickHouse 兼容写法,避免 PG 独有语法。


目录

  1. 窗口函数 — 交易对成交量分层排名
  2. CTE — 递归查询邀请关系链与返佣层级
  3. CTE — 非递归多步 KYC 状态聚合
  4. JOIN 优化 — 成交明细关联盘口快照,避免笛卡尔积
  5. Top-N per Group — 每个用户每日成交最活跃的前 3 个交易对
  6. 连续 streak — 连续登录天数检测(风险评分模型输入)
  7. 留存分析 — 新注册用户 Day-1 / Day-7 / Day-30 交易留存
  8. 漏斗分析 — KYC 认证五步转化与流失
  9. 移动平均 / 累积和 — 用户近 7 日提现金额滑动窗口与异常检测
  10. 重复检测与去重 — 提现请求重复提交识别与幂等去重
  11. 时序聚合 — 每小时成交额 rollup 与补零
  12. 面试现场建议
  13. 常见追问应对

Q1: 窗口函数 — 交易对成交量分层排名

Problem Statement

"给定 trade_statistics_daily 表,请按交易对 (symbol) 的日成交额 (quote_volume) 进行: (a) 不分组全局排名; (b) 按 market_type (SPOT / FUTURES) 分组排名; (c) 将每个市场类型的交易对按成交量划分为 4 个分位桶。 要求同时展示 ROW_NUMBER、RANK、DENSE_RANK 的区别。"

Table Schema

CREATE TABLE trade_statistics_daily (
    stat_date      DATE NOT NULL COMMENT '统计日期',
    symbol         VARCHAR(32) NOT NULL COMMENT '交易对,如 BTCUSDT',
    market_type    VARCHAR(16) NOT NULL COMMENT 'SPOT / FUTURES / MARGIN',
    quote_volume   DECIMAL(38,8) NOT NULL COMMENT '日成交额(计价币)',
    trade_count    BIGINT NOT NULL COMMENT '日成交笔数',
    -- 生产环境通常还有分区与 TTL
    PRIMARY KEY (stat_date, symbol, market_type)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(stat_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01')
)
COMMENT='日级交易对统计,保留 90 天';

Sample Data

INSERT INTO trade_statistics_daily VALUES
('2024-06-01', 'BTCUSDT',  'SPOT',    450000000.00, 1200000),
('2024-06-01', 'ETHUSDT',  'SPOT',    280000000.00,  900000),
('2024-06-01', 'BNBUSDT',  'SPOT',    120000000.00,  600000),
('2024-06-01', 'BTCUSDT',  'FUTURES', 890000000.00, 2100000),
('2024-06-01', 'ETHUSDT',  'FUTURES', 560000000.00, 1800000),
('2024-06-01', 'SOLUSDT',  'FUTURES', 560000000.00, 1500000),  -- ETH 与 SOL 成交量相同
('2024-06-01', 'DOGEUSDT', 'SPOT',     80000000.00,  400000);

Complete SQL Answer

-- ============================================================
-- (a)(b) 全局排名 + 分组排名,同时对比三种排名函数
-- ============================================================
SELECT
    stat_date,
    symbol,
    market_type,
    quote_volume,
 
    -- 1) 全局 ROW_NUMBER:严格连续,无并列,1,2,3,4...
    ROW_NUMBER() OVER (ORDER BY quote_volume DESC) AS global_rn,
 
    -- 2) 全局 RANK:并列跳号,1,2,2,4...
    RANK() OVER (ORDER BY quote_volume DESC) AS global_rk,
 
    -- 3) 全局 DENSE_RANK:并列不跳号,1,2,2,3...
    DENSE_RANK() OVER (ORDER BY quote_volume DESC) AS global_dr,
 
    -- 4) 市场类型内 ROW_NUMBER
    ROW_NUMBER() OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS type_rn,
 
    -- 5) 市场类型内 RANK
    RANK() OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS type_rk
 
FROM trade_statistics_daily
WHERE stat_date = '2024-06-01'
ORDER BY market_type, type_rn;
 
-- ============================================================
-- (c) NTILE(4) 分位桶 —— 每个 market_type 分成 4 档
-- ============================================================
SELECT
    stat_date,
    symbol,
    market_type,
    quote_volume,
    NTILE(4) OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS quartile_bucket
FROM trade_statistics_daily
WHERE stat_date = '2024-06-01'
ORDER BY market_type, quartile_bucket, quote_volume DESC;

Common Mistakes / Anti-Patterns

错误原因
ORDER BY quote_volume 忘写 DESC默认 ASC,导致排名结果完全相反
RANK() 与 DENSE_RANK() 选错需要"不跳号连续等级"时用 DENSE_RANK;需要"真实百分比位置"时用 RANK
NTILE(4) 在数据量 < 4 时使用每桶至少 1 行,桶数 > 行数时部分桶为空,生产需加 COUNT(*) OVER() 判断
忘记 PARTITION BY market_type所有交易对混在一起排名,SPOT 和 FUTURES 不可比

Key Concept Tip

ROW_NUMBER 给唯一序号,RANK 允许并列但跳号,DENSE_RANK 允许并列且不跳号,NTILE 做分桶。 面试一句话:ROW_NUMBER 去重取 Top-N,RANK 算百分比,DENSE_RANK 做等级分层。


Q2: CTE — 递归查询邀请关系链与返佣层级

Problem Statement

"用户邀请系统里,user_referral 表保存了直接邀请关系 (referrer_id → referee_id)。 给定一个用户 ID,请递归查询出他的所有下级(直推 + 间推),并计算每个下级的层级 depth 以及该链条上的 root 用户。 假设 大型加密货币交易所 返佣最多 3 级,请只返回 depth <= 3 的记录。"

Table Schema

CREATE TABLE user_referral (
    referrer_id    BIGINT NOT NULL COMMENT '邀请人 UID',
    referee_id     BIGINT NOT NULL COMMENT '被邀请人 UID',
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (referrer_id, referee_id),
    KEY idx_referee (referee_id)        -- 反向查上级时用
) ENGINE=InnoDB
COMMENT='用户邀请关系,记录不删除,软删用状态字段';

Sample Data

INSERT INTO user_referral VALUES
(1001, 1002, '2024-01-01 10:00:00'),  -- 1001 -> 1002
(1002, 1003, '2024-01-02 11:00:00'),  -- 1002 -> 1003
(1003, 1004, '2024-01-03 12:00:00'),  -- 1003 -> 1004 (depth 3 from 1001)
(1001, 1005, '2024-01-04 13:00:00'),  -- 1001 -> 1005
(1005, 1006, '2024-01-05 14:00:00'),  -- 1005 -> 1006
(1006, 1007, '2024-01-06 15:00:00'),  -- 1006 -> 1007 (depth 3 from 1001)
(1002, 1008, '2024-01-07 16:00:00');  -- 1002 -> 1008

Complete SQL Answer

WITH RECURSIVE referral_chain AS (
    -- 锚定成员:直接下级(depth = 1)
    SELECT
        referrer_id AS root_user,
        referee_id  AS current_user,
        1           AS depth,
        CAST(referee_id AS CHAR(255)) AS path  -- MySQL 递归 CTE 需要显式类型一致
    FROM user_referral
    WHERE referrer_id = 1001
 
    UNION ALL
 
    -- 递归成员:下级的下级
    SELECT
        rc.root_user,
        ur.referee_id AS current_user,
        rc.depth + 1  AS depth,
        CONCAT(rc.path, '->', ur.referee_id) AS path
    FROM referral_chain rc
    INNER JOIN user_referral ur ON ur.referrer_id = rc.current_user
    WHERE rc.depth < 3   -- 返佣层级上限,防止无限递归
)
SELECT
    root_user,
    current_user,
    depth,
    path
FROM referral_chain
ORDER BY depth, current_user;

Common Mistakes / Anti-Patterns

错误原因
忘写 depth < 3 终止条件MySQL 默认 cte_max_recursion_depth = 1000,数据量大时栈溢出或超时
UNION 代替 UNION ALL递归 CTE 必须用 UNION ALL,UNION 会去重导致递归中断
path 字段类型不一致锚定行是 BIGINT,递归行是 CONCAT(...),MySQL 8 要求 CTE 列类型严格一致,需显式 CAST
没有反向索引 idx_refereeON ur.referrer_id = rc.current_user 需要 referee 做驱动,无索引全表扫描
试图递归 JOIN 大表全量必须先 WHERE referrer_id = ? 锚定,再做递归,否则 O(n^2) 爆炸

Key Concept Tip

递归 CTE = 锚定行 + UNION ALL + 终止条件。 面试写之前先说:"我会用递归 CTE,锚点查直接邀请,然后递归 JOIN,限制 depth 防循环。"


Q3: CTE — 非递归多步 KYC 状态聚合

Problem Statement

"KYC 审核流有 5 个步骤,每个步骤的状态变更记录在 kyc_step_log 中。 请用非递归 CTE 查询每个用户最新的 KYC 整体状态:要求把 5 个步骤的最新状态 pivot 成一行,并标记是否全部通过。"

Table Schema

CREATE TABLE kyc_step_log (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT NOT NULL COMMENT '用户 UID',
    step_name       VARCHAR(32) NOT NULL COMMENT '步骤:REGISTER / EMAIL_VERIFY / ID_UPLOAD / FACIAL / MANUAL_REVIEW',
    status          VARCHAR(16) NOT NULL COMMENT 'PENDING / PASSED / FAILED / RETRY',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user_step_time (user_id, step_name, created_at DESC)
) ENGINE=InnoDB
COMMENT='KYC 步骤状态变更流水,只插入不更新';

Sample Data

INSERT INTO kyc_step_log (user_id, step_name, status, created_at) VALUES
(2001, 'REGISTER',       'PASSED', '2024-06-01 08:00:00'),
(2001, 'EMAIL_VERIFY',   'PASSED', '2024-06-01 08:05:00'),
(2001, 'ID_UPLOAD',      'PASSED', '2024-06-01 08:10:00'),
(2001, 'FACIAL',         'FAILED', '2024-06-01 08:15:00'),
(2001, 'FACIAL',         'PASSED', '2024-06-01 08:20:00'),  -- 重试通过
(2001, 'MANUAL_REVIEW',  'PENDING','2024-06-01 08:25:00'),
(2002, 'REGISTER',       'PASSED', '2024-06-01 09:00:00'),
(2002, 'EMAIL_VERIFY',   'PASSED', '2024-06-01 09:05:00'),
(2002, 'ID_UPLOAD',      'FAILED', '2024-06-01 09:10:00');

Complete SQL Answer

-- ============================================================
-- Step 1: CTE 取每个用户每个步骤的最新一条记录
-- ============================================================
WITH latest_step AS (
    SELECT
        user_id,
        step_name,
        status,
        created_at,
        -- 每个 (user, step) 取最新
        ROW_NUMBER() OVER (PARTITION BY user_id, step_name ORDER BY created_at DESC) AS rn
    FROM kyc_step_log
)
-- ============================================================
-- Step 2: 把 5 个步骤 pivot 成一行,并计算是否全通过
-- ============================================================
SELECT
    user_id,
    MAX(CASE WHEN step_name = 'REGISTER'      THEN status END) AS register_status,
    MAX(CASE WHEN step_name = 'EMAIL_VERIFY'  THEN status END) AS email_status,
    MAX(CASE WHEN step_name = 'ID_UPLOAD'     THEN status END) AS id_status,
    MAX(CASE WHEN step_name = 'FACIAL'        THEN status END) AS facial_status,
    MAX(CASE WHEN step_name = 'MANUAL_REVIEW' THEN status END) AS manual_status,
 
    -- 判断是否全部 PASSED(NULL 表示该步骤无记录,视为未完成)
    CASE
        WHEN COUNT(DISTINCT CASE WHEN status = 'PASSED' THEN step_name END) = 5
        THEN 'FULL_PASSED'
        ELSE 'INCOMPLETE'
    END AS overall_status
 
FROM latest_step
WHERE rn = 1
GROUP BY user_id
ORDER BY user_id;

Common Mistakes / Anti-Patterns

错误原因
用 GROUP BY 直接取 MAX(status)状态是字符串,MAX 按字典序,'PASSED' < 'PENDING' 可能错
不用 ROW_NUMBER() 而直接用 MAX(created_at)需要子查询再次 JOIN 取 status,性能差且易错
Pivot 时忘写 MAX() 聚合没有聚合函数的 CASE 放在 GROUP BY 中会报错
没有索引 (user_id, step_name, created_at DESC)每个用户每个步骤都要排序,全表扫描代价高

Key Concept Tip

非递归 CTE 的核心价值是"分步可读":先过滤/打标,再聚合。 Pivot 口诀:CASE WHEN ... THEN ... END 放在聚合函数里,GROUP BY 用户。


Q4: JOIN 优化 — 成交明细关联盘口快照,避免笛卡尔积

Problem Statement

"trade_fill 记录逐笔成交,order_book_snapshot 记录每秒盘口快照(买一卖一)。 请关联出每笔成交发生时最近的盘口快照,要求: (a) 写出正确的 JOIN SQL; (b) 指出一种会产生笛卡尔积的错误写法; (c) 说明如何建索引。"

Table Schema

-- 逐笔成交表(量大,按日分区)
CREATE TABLE trade_fill (
    fill_id         BIGINT NOT NULL COMMENT '成交 ID',
    symbol          VARCHAR(32) NOT NULL COMMENT '交易对',
    fill_time       DATETIME(3) NOT NULL COMMENT '成交时间,毫秒精度',
    price           DECIMAL(38,16) NOT NULL,
    qty             DECIMAL(38,16) NOT NULL,
    PRIMARY KEY (fill_id, fill_time),
    KEY idx_symbol_time (symbol, fill_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(fill_time)) (
    PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-02'))
)
COMMENT='逐笔成交,TTL 30 天';
 
-- 盘口快照表(每秒一条,按 symbol 分表或分区)
CREATE TABLE order_book_snapshot (
    snapshot_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol          VARCHAR(32) NOT NULL,
    snapshot_time   DATETIME(3) NOT NULL,
    best_bid_price  DECIMAL(38,16) NOT NULL,
    best_ask_price  DECIMAL(38,16) NOT NULL,
    KEY idx_symbol_time (symbol, snapshot_time)
) ENGINE=InnoDB
COMMENT='盘口快照,保留 7 天';

Sample Data

INSERT INTO trade_fill VALUES
(1, 'BTCUSDT', '2024-06-01 10:00:00.500', 69950.00, 0.5),
(2, 'BTCUSDT', '2024-06-01 10:00:01.200', 69951.00, 1.2),
(3, 'BTCUSDT', '2024-06-01 10:00:02.800', 69952.00, 0.3);
 
INSERT INTO order_book_snapshot (symbol, snapshot_time, best_bid_price, best_ask_price) VALUES
('BTCUSDT', '2024-06-01 10:00:00.000', 69949.50, 69950.50),
('BTCUSDT', '2024-06-01 10:00:01.000', 69950.50, 69951.50),
('BTCUSDT', '2024-06-01 10:00:02.000', 69951.50, 69952.50);

Complete SQL Answer

-- ============================================================
-- 正确写法:AsOf JOIN(MySQL 无原生 AsOf,用 window + JOIN 模拟)
-- ============================================================
WITH snapshot_ranked AS (
    SELECT
        symbol,
        snapshot_time,
        best_bid_price,
        best_ask_price,
        -- 为每个 symbol 生成有序序列
        ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY snapshot_time) AS snap_rn
    FROM order_book_snapshot
    WHERE symbol = 'BTCUSDT'
),
fills AS (
    SELECT
        fill_id,
        symbol,
        fill_time,
        price,
        qty
    FROM trade_fill
    WHERE symbol = 'BTCUSDT'
)
SELECT
    f.fill_id,
    f.fill_time,
    f.price    AS fill_price,
    s.best_bid_price,
    s.best_ask_price,
    s.snapshot_time AS matched_snapshot_time
FROM fills f
-- 关联:snapshot_time <= fill_time 的最新一条
LEFT JOIN snapshot_ranked s
    ON f.symbol = s.symbol
    AND s.snapshot_time = (
        SELECT MAX(snapshot_time)
        FROM order_book_snapshot s2
        WHERE s2.symbol = f.symbol
          AND s2.snapshot_time <= f.fill_time
    )
ORDER BY f.fill_time;
 
-- ============================================================
-- 更优生产写法:利用 LATERAL/窗口函数预先打标(StarRocks/CH 可用)
-- MySQL 8.0 推荐用上述子查询,或程序端合并(避免大数据量 JOIN)
-- ============================================================

Common Mistakes / Anti-Patterns

错误写法(笛卡尔积):

-- 错误!没有 snapshot_time 关联条件,或条件方向错误
SELECT *
FROM trade_fill f
JOIN order_book_snapshot s ON f.symbol = s.symbol   -- 每秒 N 条成交 × 86400 条快照 = 爆炸
WHERE s.snapshot_time <= f.fill_time;                -- 过滤在 WHERE,先 JOIN 后过滤
错误原因
只按 symbol JOIN同 symbol 一天有 8.6 万条快照,笛卡尔积巨大
用 BETWEEN 或 DATE() 函数关联无法使用索引,全表扫描
WHERE s.snapshot_time <= f.fill_time 写在 WHERE某些引擎先执行 JOIN 再过滤,中间结果膨胀
没有 symbol 过滤条件两表都是海量数据,必须带 symbol 或日期裁剪

Key Concept Tip

"最近一条关联" = 不等值 JOIN,必须限制单侧范围,或子查询先取 MAX。 生产环境大数据量时,不要用 SQL 做 AsOf JOIN,用 Flink/Spark 或程序端二分查找。


Q5: Top-N per Group — 每个用户每日成交最活跃的前 3 个交易对

Problem Statement

"给定 user_trade_daily 表,请查询每个用户每个自然日成交额最高的前 3 个交易对。 如果第 3 名出现并列,全部保留。"

Table Schema

CREATE TABLE user_trade_daily (
    stat_date    DATE NOT NULL,
    user_id      BIGINT NOT NULL COMMENT '用户 UID',
    symbol       VARCHAR(32) NOT NULL COMMENT '交易对',
    quote_volume DECIMAL(38,8) NOT NULL COMMENT '当日成交额',
    trade_count  BIGINT NOT NULL,
    PRIMARY KEY (stat_date, user_id, symbol),
    KEY idx_user_date_vol (user_id, stat_date, quote_volume DESC)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(stat_date) (
    PARTITION p202406 VALUES LESS THAN ('2024-07-01')
)
COMMENT='用户日级交易统计,保留 90 天';

Sample Data

INSERT INTO user_trade_daily VALUES
('2024-06-01', 3001, 'BTCUSDT', 50000.00, 10),
('2024-06-01', 3001, 'ETHUSDT', 30000.00, 8),
('2024-06-01', 3001, 'BNBUSDT', 20000.00, 5),
('2024-06-01', 3001, 'SOLUSDT', 20000.00, 4),  -- 与 BNBUSDT 并列第 3
('2024-06-01', 3002, 'BTCUSDT', 100000.00, 20),
('2024-06-01', 3002, 'ETHUSDT', 80000.00, 15),
('2024-06-02', 3001, 'BTCUSDT', 60000.00, 12);

Complete SQL Answer

-- ============================================================
-- 法一:窗口函数 + 外层过滤(最通用)
-- ============================================================
WITH ranked AS (
    SELECT
        stat_date,
        user_id,
        symbol,
        quote_volume,
        -- 需求:前 3 名,并列全保留 → 用 DENSE_RANK
        DENSE_RANK() OVER (
            PARTITION BY user_id, stat_date
            ORDER BY quote_volume DESC
        ) AS dr
    FROM user_trade_daily
    WHERE stat_date = '2024-06-01'   -- 分区裁剪
)
SELECT
    stat_date,
    user_id,
    symbol,
    quote_volume,
    dr AS rank_in_day
FROM ranked
WHERE dr <= 3
ORDER BY user_id, stat_date, dr, symbol;
 
-- ============================================================
-- 法二:MySQL 8 变量法(兼容旧思维,不推荐)
-- ============================================================
-- 面试时优先写法一,展示你掌握现代 SQL。

Common Mistakes / Anti-Patterns

错误原因
用 ROW_NUMBER() <= 3并列时只保留一条,不符合"并列全保留"需求
用 RANK() <= 3若第 2 名有 3 个并列,RANK 会跳号到 5,导致只保留 2 个组,可能漏掉真正的第 3 名
没有 PARTITION BY user_id, stat_date所有用户所有日期混在一起排名
没有 WHERE 日期裁剪90 天分区的表,全表扫描代价极高

Key Concept Tip

Top-N 并列处理口诀:去并列用 ROW_NUMBER,允许并列不跳号用 DENSE_RANK,允许并列要跳号用 RANK。 本题 "并列全保留" = DENSE_RANK() <= 3。


Q6: 连续 streak — 连续登录天数检测(风险评分模型输入)

Problem Statement

"风控模型需要识别用户连续登录行为。给定 user_login_log 表,请查询每个用户截至今天的最长连续登录天数,以及当前是否处于连续登录状态。 连续定义:自然日连续,不要求时刻连续。"

Table Schema

CREATE TABLE user_login_log (
    user_id     BIGINT NOT NULL,
    login_date  DATE NOT NULL COMMENT '登录自然日,去重',
    login_count INT NOT NULL DEFAULT 1 COMMENT '当日登录次数',
    PRIMARY KEY (user_id, login_date),
    KEY idx_date (login_date)   -- 用于批量统计
) ENGINE=InnoDB
COMMENT='用户登录日汇总,从流水表聚合写入,保留 1 年';

Sample Data

INSERT INTO user_login_log VALUES
(4001, '2024-06-01', 3),
(4001, '2024-06-02', 1),
(4001, '2024-06-03', 2),
(4001, '2024-06-05', 1),  -- 断了一天
(4001, '2024-06-06', 1),
(4002, '2024-06-01', 1),
(4002, '2024-06-03', 1),  -- 不连续
(4002, '2024-06-04', 1);

Complete SQL Answer

-- ============================================================
-- 核心技巧:login_date 减去行号 = 连续组标识(等差数列原理)
-- ============================================================
WITH numbered AS (
    SELECT
        user_id,
        login_date,
        -- 按日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_login_log
),
grouped AS (
    SELECT
        user_id,
        login_date,
        -- 关键:DATE_SUB 后相同的属于同一连续组
        DATE_SUB(login_date, INTERVAL rn DAY) AS grp
    FROM numbered
)
SELECT
    user_id,
    COUNT(*)                    AS streak_length,
    MIN(login_date)             AS streak_start,
    MAX(login_date)             AS streak_end,
    -- 判断当前 streak 是否包含今天(假设今天是 2024-06-06)
    CASE WHEN MAX(login_date) = CURRENT_DATE THEN 'ACTIVE' ELSE 'BROKEN' END AS current_status
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_length DESC;
 
-- ============================================================
-- 若只取每个用户的最长 streak:
-- ============================================================
WITH ... /* 同上 */ ...
SELECT
    user_id,
    MAX(streak_length) AS max_streak
FROM (
    SELECT user_id, grp, COUNT(*) AS streak_length
    FROM grouped
    GROUP BY user_id, grp
) t
GROUP BY user_id;

Common Mistakes / Anti-Patterns

错误原因
用 LEAD/LAG 逐行比较 date_diff = 1可以判断相邻是否连续,但统计连续长度需要再嵌套一层,代码更复杂
DATE_SUB 后比较忘了 INTERVAL rn DAY行号是整数,不能直接与 DATE 运算,必须显式转换
把 login_count > 0 作为去重条件表本身已按日去重,无需再判断
没有处理跨月/跨年DATE_SUB 自动处理历法,无需担心

Key Concept Tip

连续日期问题万能公式:date - ROW_NUMBER() = constant。同一 constant 即为同一连续段。 记住原理:等差数列减等差数列 = 常数。


Q7: 留存分析 — 新注册用户 Day-1 / Day-7 / Day-30 交易留存

Problem Statement

"给定 user_registration 和 user_trade_daily,请按注册日期 cohort 统计: 每日新增用户数、Day-1 交易留存率、Day-7 交易留存率、Day-30 交易留存率。 留存定义:注册后第 N 天有成交(不要求金额)。"

Table Schema

CREATE TABLE user_registration (
    user_id       BIGINT PRIMARY KEY,
    register_date DATE NOT NULL,
    channel       VARCHAR(32) COMMENT '注册渠道',
    KEY idx_register_date (register_date)
) ENGINE=InnoDB;
 
CREATE TABLE user_trade_daily (
    stat_date    DATE NOT NULL,
    user_id      BIGINT NOT NULL,
    symbol       VARCHAR(32) NOT NULL,
    quote_volume DECIMAL(38,8) NOT NULL,
    PRIMARY KEY (stat_date, user_id, symbol)
) ENGINE=InnoDB;

Sample Data

INSERT INTO user_registration VALUES
(5001, '2024-06-01', 'APP'),
(5002, '2024-06-01', 'WEB'),
(5003, '2024-06-01', 'APP'),
(5004, '2024-06-02', 'APP');
 
INSERT INTO user_trade_daily VALUES
('2024-06-02', 5001, 'BTCUSDT', 100),  -- Day-1 留存
('2024-06-08', 5001, 'ETHUSDT', 200),  -- Day-7 留存
('2024-06-09', 5002, 'BTCUSDT', 50),   -- Day-8 不算 Day-7
('2024-06-02', 5003, 'BTCUSDT', 300),  -- Day-1 留存
('2024-06-03', 5004, 'BTCUSDT', 100);  -- Day-1 留存

Complete SQL Answer

-- ============================================================
-- Step 1: 构建注册 cohort 基表
-- Step 2: 左连接交易表,判断注册后第 N 天是否有交易
-- ============================================================
WITH cohort AS (
    SELECT
        register_date,
        COUNT(*) AS new_users
    FROM user_registration
    WHERE register_date BETWEEN '2024-06-01' AND '2024-06-02'
    GROUP BY register_date
),
retention_flags AS (
    SELECT
        r.register_date,
        r.user_id,
        -- 是否有 Day-1 交易
        MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 1 DAY) THEN 1 END) AS d1_flag,
        -- 是否有 Day-7 交易
        MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 7 DAY) THEN 1 END) AS d7_flag,
        -- 是否有 Day-30 交易
        MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 30 DAY) THEN 1 END) AS d30_flag
    FROM user_registration r
    LEFT JOIN user_trade_daily t
        ON r.user_id = t.user_id
        AND t.stat_date IN (
            DATE_ADD(r.register_date, INTERVAL 1 DAY),
            DATE_ADD(r.register_date, INTERVAL 7 DAY),
            DATE_ADD(r.register_date, INTERVAL 30 DAY)
        )
    WHERE r.register_date BETWEEN '2024-06-01' AND '2024-06-02'
    GROUP BY r.register_date, r.user_id
)
SELECT
    c.register_date,
    c.new_users,
    COUNT(CASE WHEN rf.d1_flag  = 1 THEN 1 END) / c.new_users AS d1_retention_rate,
    COUNT(CASE WHEN rf.d7_flag  = 1 THEN 1 END) / c.new_users AS d7_retention_rate,
    COUNT(CASE WHEN rf.d30_flag = 1 THEN 1 END) / c.new_users AS d30_retention_rate
FROM cohort c
LEFT JOIN retention_flags rf ON c.register_date = rf.register_date
GROUP BY c.register_date, c.new_users
ORDER BY c.register_date;

Common Mistakes / Anti-Patterns

错误原因
INNER JOIN 代替 LEFT JOIN未交易用户被过滤,留存率分母变小,结果虚高
用 DATEDIFF(t.stat_date, r.register_date) = 1必须确保 t.stat_date >= r.register_date,否则负数也会匹配
把条件 t.stat_date IN (...) 写在 WHEREWHERE 中对右表过滤会把 LEFT JOIN 变成 INNER JOIN
用 COUNT(r.user_id) 做分子应该 COUNT(CASE WHEN ... THEN 1 END) 只统计有交易的用户
没限制 register_date 范围全量注册表 JOIN 全量交易表,数据爆炸

Key Concept Tip

留存率 = 有行为的用户数 / cohort 总用户数。 关键:LEFT JOIN 保全体,CASE WHEN 做标记,COUNT(标记) 求和,再除 cohort 人数。 生产环境用日期位图或 RoaringBitmap 预计算,SQL 仅用于离线报表。


Q8: 漏斗分析 — KYC 认证五步转化与流失

Problem Statement

"KYC 流程共 5 步:REGISTER → EMAIL_VERIFY → ID_UPLOAD → FACIAL → MANUAL_REVIEW。 给定 kyc_step_log,请统计 2024-06-01 当日 cohort 每一步的转化人数、转化率、相对上一步的流失率。 注意:用户可能重试某一步,只算最新状态;FAILED 视为未通过该步。"

Table Schema

-- 复用 Q3 的 kyc_step_log 表结构,增加步骤顺序映射表
CREATE TABLE kyc_step_definition (
    step_name   VARCHAR(32) PRIMARY KEY,
    step_order  INT NOT NULL COMMENT '1~5'
) ENGINE=InnoDB;
 
INSERT INTO kyc_step_definition VALUES
('REGISTER', 1), ('EMAIL_VERIFY', 2), ('ID_UPLOAD', 3),
('FACIAL', 4), ('MANUAL_REVIEW', 5);

Sample Data

复用 Q3 数据,增加一条:

INSERT INTO kyc_step_log (user_id, step_name, status, created_at) VALUES
(2003, 'REGISTER',      'PASSED', '2024-06-01 07:00:00'),
(2003, 'EMAIL_VERIFY',  'PASSED', '2024-06-01 07:05:00'),
(2003, 'ID_UPLOAD',     'FAILED', '2024-06-01 07:10:00');  -- 卡在第三步

Complete SQL Answer

-- ============================================================
-- 漏斗分析:每一步的转化人数、转化率、流失率
-- ============================================================
WITH latest_status AS (
    -- 每个用户每个步骤最新状态
    SELECT
        user_id,
        step_name,
        status,
        ROW_NUMBER() OVER (PARTITION BY user_id, step_name ORDER BY created_at DESC) AS rn
    FROM kyc_step_log
    WHERE DATE(created_at) = '2024-06-01'
),
passed_users AS (
    -- 只保留 PASSED 的
    SELECT DISTINCT user_id, step_name
    FROM latest_status
    WHERE rn = 1 AND status = 'PASSED'
),
step_counts AS (
    SELECT
        d.step_order,
        d.step_name,
        COUNT(DISTINCT p.user_id) AS passed_count
    FROM kyc_step_definition d
    LEFT JOIN passed_users p ON d.step_name = p.step_name
    GROUP BY d.step_order, d.step_name
)
SELECT
    step_order,
    step_name,
    passed_count,
    -- 相对于第一步的转化率
    passed_count / MAX(passed_count) OVER () AS overall_conversion_rate,
    -- 相对于上一步的转化率
    passed_count / LAG(passed_count) OVER (ORDER BY step_order) AS step_conversion_rate,
    -- 相对于上一步的流失率
    1 - (passed_count / LAG(passed_count) OVER (ORDER BY step_order)) AS churn_rate
FROM step_counts
ORDER BY step_order;

Common Mistakes / Anti-Patterns

错误原因
不去重 DISTINCT user_id一个用户可能在多步出现,直接 COUNT(*) 会重复计数
没有过滤 rn = 1重试记录导致旧状态干扰,必须用最新一条
FAILED 也算通过漏斗只看成功转化,FAILED 应视为未进入该步
第一步用 LAG 计算流失率LAG(passed_count) 第一步为 NULL,需用 COALESCE 或 UNION 加初始值

Key Concept Tip

漏斗 = cohort → 每步去重计数 → LAG 求环比。 面试画金字塔口述:"先取每个用户每步最新状态,过滤出 PASSED,按步骤去重计数,再用窗口函数 LAG 算每步转化率和流失率。"


Q9: 移动平均 / 累积和 — 用户近 7 日提现金额滑动窗口与异常检测

Problem Statement

"风控需要监控用户提现行为异常。给定 withdrawal_request 表,请统计每个用户最近 7 天(含当日)的: (a) 每日提现总额; (b) 7 日滑动平均提现额; (c) 当日提现额 / 7 日均值的比率(> 3 倍标记为 HIGH_RISK)。 缺失日期补零。"

Table Schema

CREATE TABLE withdrawal_request (
    request_id    BIGINT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    amount        DECIMAL(38,8) NOT NULL COMMENT '提现金额 USDT',
    status        VARCHAR(16) NOT NULL COMMENT 'SUCCESS / PENDING / REJECTED',
    created_at    DATETIME NOT NULL,
    completed_at  DATETIME COMMENT '成功时间',
    KEY idx_user_completed (user_id, completed_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(completed_at)) (
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01'))
)
COMMENT='提现请求,只统计 status=SUCCESS 且 completed_at 非空';

Sample Data

INSERT INTO withdrawal_request VALUES
(1, 6001, 1000.00, 'SUCCESS', '2024-06-01 10:00:00', '2024-06-01 10:05:00'),
(2, 6001, 2000.00, 'SUCCESS', '2024-06-02 11:00:00', '2024-06-02 11:05:00'),
(3, 6001, 1500.00, 'SUCCESS', '2024-06-03 12:00:00', '2024-06-03 12:05:00'),
(4, 6001, 1000.00, 'SUCCESS', '2024-06-05 13:00:00', '2024-06-05 13:05:00'),  -- 6-04 缺失
(5, 6001, 10000.00,'SUCCESS', '2024-06-06 14:00:00', '2024-06-06 14:05:00'), -- 异常大额
(6, 6002, 500.00,  'SUCCESS', '2024-06-01 09:00:00', '2024-06-01 09:05:00');

Complete SQL Answer

-- ============================================================
-- Step 1: 生成日期序列(MySQL 8 可用递归 CTE 生成;生产中常从日历表取)
-- Step 2: 与用户每日提现额 LEFT JOIN(补零)
-- Step 3: 窗口函数计算滑动平均与累积和
-- ============================================================
WITH RECURSIVE date_series AS (
    SELECT DATE('2024-06-01') AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < DATE('2024-06-07')
),
user_list AS (
    SELECT DISTINCT user_id FROM withdrawal_request
    WHERE completed_at BETWEEN '2024-06-01' AND '2024-06-07 23:59:59'
),
date_user_grid AS (
    -- 笛卡尔积:每个用户 × 每天(小范围日期允许,大数据量用日历表 JOIN)
    SELECT d.dt, u.user_id FROM date_series d CROSS JOIN user_list u
),
daily_amount AS (
    SELECT
        user_id,
        DATE(completed_at) AS dt,
        SUM(amount) AS daily_total
    FROM withdrawal_request
    WHERE status = 'SUCCESS'
      AND completed_at BETWEEN '2024-06-01' AND '2024-06-07 23:59:59'
    GROUP BY user_id, DATE(completed_at)
)
SELECT
    g.dt,
    g.user_id,
    COALESCE(d.daily_total, 0) AS daily_total,
 
    -- 7 日滑动平均(当前行往前 6 天 = 含当日共 7 天)
    AVG(COALESCE(d.daily_total, 0)) OVER (
        PARTITION BY g.user_id
        ORDER BY g.dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
 
    -- 7 日累积和
    SUM(COALESCE(d.daily_total, 0)) OVER (
        PARTITION BY g.user_id
        ORDER BY g.dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS cumulative_7d,
 
    -- 异常比率:当日 / 7 日均值(前 6 天不足时不标记)
    CASE
        WHEN COUNT(*) OVER (
            PARTITION BY g.user_id
            ORDER BY g.dt
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) >= 7
        AND COALESCE(d.daily_total, 0) > 0
        THEN COALESCE(d.daily_total, 0) / AVG(COALESCE(d.daily_total, 0)) OVER (
            PARTITION BY g.user_id
            ORDER BY g.dt
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )
        ELSE NULL
    END AS spike_ratio,
 
    CASE
        WHEN COALESCE(d.daily_total, 0) > 3 * AVG(COALESCE(d.daily_total, 0)) OVER (
            PARTITION BY g.user_id
            ORDER BY g.dt
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )
        THEN 'HIGH_RISK'
        ELSE 'NORMAL'
    END AS risk_flag
 
FROM date_user_grid g
LEFT JOIN daily_amount d ON g.user_id = d.user_id AND g.dt = d.dt
ORDER BY g.user_id, g.dt;

Common Mistakes / Anti-Patterns

错误原因
RANGE BETWEEN 代替 ROWS BETWEENRANGE 按值划窗,日期重复时会多算;日期序列去重后应使用 ROWS
忘写 COALESCE(d.daily_total, 0)缺失日期 JOIN 后为 NULL,AVG/SUM 会忽略 NULL,导致均值虚高
用 UNBOUNDED PRECEDING那是累积从开头到当前,不是滑动 7 日
没有日期裁剪withdrawal_request 量大,必须先 WHERE completed_at 范围
缺失日期未补零风控场景 6-04 无提现不能直接跳过,必须显式补 0

Key Concept Tip

滑动窗口语法:AVG(x) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)。 补零关键:先生成日期网格 CROSS JOIN 用户,再 LEFT JOIN 实际数据。


Q10: 重复检测与去重 — 提现请求重复提交识别与幂等去重

Problem Statement

"用户前端可能因网络抖动重复点击提交提现。给定 withdrawal_request 表,请: (a) 找出过去 1 小时内,同一用户、同一金额、同一地址的重复提交(保留第一条,其余标记 DUPLICATE); (b) 写出幂等去重后的唯一请求视图。"

Table Schema

CREATE TABLE withdrawal_request (
    request_id    BIGINT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    amount        DECIMAL(38,8) NOT NULL,
    to_address    VARCHAR(128) NOT NULL COMMENT '链上地址',
    chain         VARCHAR(32) NOT NULL COMMENT 'ETH / BSC / TRON',
    status        VARCHAR(16) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_ref_id VARCHAR(64) COMMENT '客户端幂等键',
    KEY idx_user_amount_address_time (user_id, amount, to_address, created_at),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB;

Sample Data

INSERT INTO withdrawal_request
(request_id, user_id, amount, to_address, chain, status, created_at, client_ref_id) VALUES
(101, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:00', 'ref-001'),
(102, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:02', 'ref-002'),  -- 重复
(103, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:05', 'ref-003'),  -- 重复
(104, 7001, 200.00, '0xDef...', 'ETH', 'PENDING', '2024-06-01 10:10:00', 'ref-004'),
(105, 7002,  50.00, '0xXyz...', 'BSC', 'PENDING', '2024-06-01 10:00:01', 'ref-005');

Complete SQL Answer

-- ============================================================
-- (a) 找出重复记录(保留第一条,其余标记 DUPLICATE)
-- ============================================================
WITH dedup_window AS (
    SELECT
        request_id,
        user_id,
        amount,
        to_address,
        chain,
        created_at,
        -- 按时间排序,第一条为 1
        ROW_NUMBER() OVER (
            PARTITION BY user_id, amount, to_address, chain
            ORDER BY created_at ASC, request_id ASC
        ) AS rn
    FROM withdrawal_request
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT
    request_id,
    user_id,
    amount,
    to_address,
    created_at,
    CASE WHEN rn = 1 THEN 'KEEP' ELSE 'DUPLICATE' END AS action
FROM dedup_window
WHERE rn > 1   -- 只看重复项;面试时可去掉 WHERE 展示全部
ORDER BY user_id, created_at;
 
-- ============================================================
-- (b) 幂等去重后的唯一请求视图(保留第一条)
-- ============================================================
WITH dedup AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, amount, to_address, chain
            ORDER BY created_at ASC, request_id ASC
        ) AS rn
    FROM withdrawal_request
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT *
FROM dedup
WHERE rn = 1;

Common Mistakes / Anti-Patterns

错误原因
用 GROUP BY + MIN(request_id)只能取到 request_id,其他字段需要再 JOIN 一次,效率低
PARTITION BY 少了 chain不同链地址格式可能相同,必须区分
ORDER BY created_at DESC应该保留最早的一条(最可能是用户真实意图),DESC 会留最晚的
没有 client_ref_id 判断生产环境优先用客户端幂等键去重,服务端 user+amount+address 是兜底
全表扫描created_at 必须带范围,否则大数据表性能灾难

Key Concept Tip

去重公式:ROW_NUMBER() OVER (PARTITION BY 业务唯一键 ORDER BY 时间 ASC) = 1 即保留。 生产先防:客户端传 client_ref_id,服务端 Redis SETNX 拦截;SQL 去重是事后补偿。


Q11: 时序聚合 — 每小时成交额 rollup 与补零

Problem Statement

"交易大屏需要展示某交易对过去 24 小时每小时成交额。给定 trade_fill 表(含毫秒时间),请: (a) 按小时汇总成交额; (b) 缺失小时补零; (c) 同时展示环比上小时增长率。"

Table Schema

复用 Q4 的 trade_fill 表:

CREATE TABLE trade_fill (
    fill_id    BIGINT NOT NULL,
    symbol     VARCHAR(32) NOT NULL,
    fill_time  DATETIME(3) NOT NULL,
    price      DECIMAL(38,16) NOT NULL,
    qty        DECIMAL(38,16) NOT NULL,
    amount     DECIMAL(38,16) GENERATED ALWAYS AS (price * qty) STORED,  -- 成交额
    PRIMARY KEY (fill_id, fill_time),
    KEY idx_symbol_time (symbol, fill_time)
) ENGINE=InnoDB;

Sample Data

INSERT INTO trade_fill (fill_id, symbol, fill_time, price, qty) VALUES
(1, 'BTCUSDT', '2024-06-01 10:15:00.000', 70000.00, 1.0),
(2, 'BTCUSDT', '2024-06-01 10:45:00.000', 70100.00, 0.5),
(3, 'BTCUSDT', '2024-06-01 12:10:00.000', 70200.00, 2.0);  -- 11 点缺失

Complete SQL Answer

-- ============================================================
-- Step 1: 递归生成过去 24 小时整点序列
-- Step 2: 左连接实际成交汇总(补零)
-- Step 3: LAG 计算环比
-- ============================================================
WITH RECURSIVE hours AS (
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 23 HOUR), '%Y-%m-%d %H:00:00') AS hour_slot
    UNION ALL
    SELECT DATE_ADD(hour_slot, INTERVAL 1 HOUR)
    FROM hours
    WHERE hour_slot < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
),
hourly_trades AS (
    SELECT
        DATE_FORMAT(fill_time, '%Y-%m-%d %H:00:00') AS hour_slot,
        SUM(price * qty) AS hourly_volume
    FROM trade_fill
    WHERE symbol = 'BTCUSDT'
      AND fill_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY DATE_FORMAT(fill_time, '%Y-%m-%d %H:00:00')
)
SELECT
    h.hour_slot,
    COALESCE(t.hourly_volume, 0) AS hourly_volume,
    -- 环比上小时
    COALESCE(t.hourly_volume, 0) / NULLIF(LAG(COALESCE(t.hourly_volume, 0)) OVER (ORDER BY h.hour_slot), 0) - 1 AS mom_rate,
    -- 标记是否有成交
    CASE WHEN t.hourly_volume IS NULL THEN 'NO_TRADE' ELSE 'TRADED' END AS trade_status
FROM hours h
LEFT JOIN hourly_trades t ON h.hour_slot = t.hour_slot
ORDER BY h.hour_slot;

Common Mistakes / Anti-Patterns

错误原因
DATE_FORMAT(fill_time, '%Y-%m-%d %H') 直接 GROUP返回字符串小时,JOIN 时类型需一致;建议统一格式化为整点时间戳
用 UNION ALL 手工写 24 行不优雅,且时间窗口变化时需改代码;递归 CTE 或程序生成更灵活
没有 COALESCE(..., 0)缺失小时显示 NULL,前端图表会断线
LAG 跨 NULL 计算环比上小时无成交时 LAG 取 0,除零需用 NULLIF 保护
没有 symbol 过滤trade_fill 通常按 symbol 分片,不带 symbol 不走分区裁剪

Key Concept Tip

时序补零 = 日期网格 LEFT JOIN 实际聚合。 面试一句话:"先生成连续小时序列,再左连接成交汇总,COALESCE 补零,LAG 算环比。"


面试现场建议

1. 当面试官没给 Schema 时 —— 主动追问

不要直接写 SQL。先问清楚以下问题:

要问的问题为什么重要
"表的数据量级大概是多少?百万、千万、还是百亿?"决定用单机 SQL 还是分布式引擎(StarRocks/ClickHouse)
"有没有分区或索引?主键是什么?"决定你的查询能否走索引,是否要做分区裁剪
"NULL 是怎么处理的?字段允许 NULL 吗?"决定要不要 COALESCE / IFNULL
"时间精度要求?到天、小时、还是毫秒?"决定 GROUP BY 的粒度
"去重逻辑是什么?取最早、最晚、还是随机一条?"决定 ORDER BY 方向
"输出需要全部列,还是只需要几个聚合指标?"决定 SELECT 范围和是否需要 JOIN 回主表

面试官不给你 schema,是故意看你会不会问问题。 风控/后端岗位尤其看重"需求澄清能力"。

2. 如何在脑中测试你的 SQL

即使没有 IDE,也可以做" mentally dry run":

  1. 画小表:在草稿纸上写 5~8 行 sample data,亲手推导窗口函数结果。
  2. 跟踪 PARTITION 边界:每换一行,问自己 "PARTITION BY 变了没有?ORDER BY 变了没有?"
  3. 检查 NULL:在脑中给某个 cell 填 NULL,看 JOIN 后会不会整行消失(LEFT JOIN 是否变 INNER JOIN)。
  4. 检查边界:第一行和最后一行的 LAG / LEAD 是否为 NULL?是否符合预期?
  5. 估算行数:每一步 CTE / 子查询后,输出大概多少行?是否比输入爆炸?

3. 如何逐步讲解你的 SQL

推荐"三段式"口述:

Step 1 — 过滤与裁剪:
"我先做日期裁剪和 symbol 过滤,利用分区/索引减少数据量。"

Step 2 — 打标与分组:
"然后用窗口函数 / CTE 给每行打标(如 ROW_NUMBER / DENSE_RANK / 连续组标识)。"

Step 3 — 聚合与输出:
"最后 GROUP BY 做聚合,SELECT 出最终指标,并做风险标记(如 HIGH_RISK)。"

边说边写,不要闷头写 3 分钟再交卷。面试官要的是你的思维过程。

4. 如果忘了函数名

忘了什么现场补救方案
DENSE_RANK 拼写说 "我用 RANK 的稠密版本,就是不跳号的那个"
LAG 语法说 "我用 offset 为 1 的前一行引用"
DATE_SUB vs DATE_ADD说 "我用日期减去一个 INTERVAL"
ROWS vs RANGE说 "我用基于物理行的滑动窗口,不是基于值的"
递归 CTE 语法先写非递归 CTE,然后说 "如果层级深,我可以改成递归 CTE"

承认"函数名可能拼错,但逻辑是这样",比硬猜一个错的更有专业性。


常见追问应对

Q1: "How would you optimize this query?"

模型答案:

"优化分三层:

  1. SQL 层面:确保 WHERE 条件命中分区键和索引键,避免函数索引列(如 DATE(column)),把计算移到常量侧;
  2. 索引层面:对 JOIN 条件和 ORDER BY 字段建联合索引,遵循最左前缀;对高基数字段在前,低基数在后;
  3. 架构层面:如果表过十亿行,单机 MySQL 扛不住,我会考虑按 user_id 分库分表,或者把分析型查询迁移到 StarRocks/ClickHouse,利用列存和向量化执行。"

Q2: "What index would you add?"

模型答案:

"看查询的过滤、JOIN、排序三要素:

  • 本查询 WHERE 有 user_id = ? AND stat_date BETWEEN ? AND ?,我会建联合索引 (user_id, stat_date, quote_volume DESC);
  • stat_date 放在第二列因为它有范围查询,再后面放 quote_volume DESC 可以避免 filesort;
  • 如果是覆盖索引场景,我把 SELECT 需要的字段也加进索引尾部,避免回表。"

Q3: "How does this execute under the hood?"

模型答案:

"我会用 EXPLAIN ANALYZE 看执行计划:

  • 先看 type 是不是 range 或 ref,避免 ALL 全表扫描;
  • 看 Extra 有没有 Using filesort 或 Using temporary,窗口函数通常需要内部临时表排序;
  • 对于 PARTITION BY user_id ORDER BY date 的窗口函数,如果数据按 (user_id, date) 有序,可以省去一次排序;
  • 递归 CTE 看是否走了 referee_id 索引,每次递归迭代都是一次索引查找。"

Q4: "What if the table has 1 billion rows?"

模型答案:

"十亿级表在 MySQL 里做复杂分析查询会触达单机天花板:

  • 存储层:按时间分区 + TTL,冷数据归档到 S3/OSS;
  • 查询层:把 OLTP 和 OLAP 分离,实时报表用 StarRocks/ClickHouse 物化视图预聚合;
  • 计算层:如果必须在线查明细,按 user_id 分片,把查询路由到对应分片并行执行;
  • 业务层:留存/漏斗类指标不需要秒级实时,T+1 离线 Spark 作业预计算到 Redis/HBase。"

Q5: "How to handle NULLs?"

模型答案:

"我遵循三个原则:

  1. 存储层面:业务上必须有值的字段(如 user_id, amount)设 NOT NULL 加默认值;
  2. 查询层面:聚合函数前用 COALESCE(col, 0),避免 AVG/SUM 忽略 NULL 导致结果偏差;
  3. JOIN 层面:LEFT JOIN 后的右表字段可能为 NULL,做比率计算时用 NULLIF(denominator, 0) 防除零;
  4. 窗口函数层面:LAG(col, 1, 0) 用第三个参数指定默认值,避免首行 NULL。"

Q6: "What's the difference between RANK and DENSE_RANK?"

模型答案:

"两者都允许并列,但处理跳号不同:

  • RANK():1, 2, 2, 4 —— 并列后跳号,适合计算真实百分比分位;
  • DENSE_RANK():1, 2, 2, 3 —— 并列不跳号,适合分层(如 A/B/C/D 等级);
  • ROW_NUMBER():1, 2, 3, 4 —— 无并列,适合去重取唯一一条。"

Q7: "Why use CTE instead of subquery?"

模型答案:

"三个原因:

  1. 可读性:复杂查询分步命名,面试官和队友都能看懂;
  2. 复用性:同一个 CTE 可以在主查询中多次引用,子查询要复制粘贴;
  3. 递归能力:只有 CTE 支持 WITH RECURSIVE,子查询无法实现树形/层级遍历。 性能上现代优化器对 CTE 和子查询通常做同等优化,不会更差。"

Q8: "How would you implement this in a streaming pipeline?"

模型答案:

"风控场景很多指标需要近实时:

  • Flink SQL:用 OVER 窗口做滑动平均,MATCH_RECOGNIZE 做连续事件检测;
  • 物化视图:StarRocks 的异步物化视图可以预计算小时级 rollup,查询直接读 MV;
  • 增量聚合:留存/漏斗用位图(RoaringBitmap)每日增量合并,而不是每次全量 SQL 重算;
  • Lambda 架构:实时 Flink 算近似值,离线 Spark 算精确值,T+1 修正。"

附录:速查口诀卡

场景口诀
Top-N 去并列ROW_NUMBER() = 1
Top-N 保并列不跳号DENSE_RANK() <= N
连续日期检测date - ROW_NUMBER() = grp
留存率计算LEFT JOIN + CASE WHEN + COUNT
漏斗流失LAG(count) OVER (ORDER BY step)
滑动平均AVG(x) OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)
去重保留最早ROW_NUMBER() OVER (PARTITION BY key ORDER BY time ASC) = 1
时序补零"日期网格 CROSS JOIN 用户,LEFT JOIN 实际数据,COALESCE 补 0"
递归树WITH RECURSIVE ... UNION ALL ... depth < N
Pivot 行转列MAX(CASE WHEN step = 'X' THEN status END)

最后提醒:大型加密货币交易所 面试白板写 SQL 时,先写 CTE 框架再填细节。即使某个函数名不确定,先把 WITH ... AS () 的骨架搭好,向面试官确认字段名后再填充,展示结构化思维比一次写对更重要。