求职SQL测试错误排查：2024每日开放抵押报价统计问题

一、语法错误 #

• date_table子查询存在字段名不匹配：unnest(date) as date后却select days，数据库会提示“字段days不存在”，需改为select date as days或者unnest(date) as days。
• quote_table的where子句中，year(step__quote_ts) = 2024与step__completed_ts is null之间缺少AND关键字，属于语法不合法。
• 最终查询的select语句里，t1.date和t2.quotes之间缺少逗号，会导致语法解析失败。

二、核心逻辑错误（最关键问题） #

你的代码仅统计了2024年当天生成报价且截至查询时仍未完成/流失的申请数量，但需求是统计2024年每一天当天处于开放状态的申请数量。正确的逻辑应该是：对于某一天D，所有满足以下条件的申请都要被计入：

• 报价生成时间step__quote_ts <= D（已进入报价阶段）
• 完成时间step__completed_ts 为NULL（从未完成）或step__completed_ts > D（在D之后才完成）
• 流失时间step__closed_lost_ts 为NULL（从未流失）或step__closed_lost_ts > D（在D之后才流失）

原代码完全偏离了这个需求，只计算了当天新增的开放申请，而非当天所有在库的开放申请。

三、修正后的SQL代码 #

WITH date_table AS (
  SELECT date AS stat_date
  FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2024-12-31', INTERVAL 1 DAY)) AS date
),
mortgage_status AS (
  SELECT
    mortgage_journey_id,
    DATE(step__quote_ts) AS quote_date,
    DATE(step__completed_ts) AS completed_date,
    DATE(step__closed_lost_ts) AS lost_date
  FROM `mortgage_journey`
  WHERE step__quote_ts IS NOT NULL -- 过滤未进入报价阶段的无效申请
)
SELECT
  dt.stat_date,
  COUNT(ms.mortgage_journey_id) AS open_quotes_count
FROM date_table dt
LEFT JOIN mortgage_status ms
  ON ms.quote_date <= dt.stat_date
  AND (ms.completed_date IS NULL OR ms.completed_date > dt.stat_date)
  AND (ms.lost_date IS NULL OR ms.lost_date > dt.stat_date)
GROUP BY dt.stat_date
ORDER BY dt.stat_date;

修正说明 #

1. 简化日期生成逻辑，直接通过UNNEST(GENERATE_DATE_ARRAY)生成2024年所有日期，避免冗余子查询。
2. 预处理抵押申请的关键时间字段，转换为日期类型便于后续日期比较。
3. 通过LEFT JOIN的关联条件精准筛选每日开放申请：报价在当日或之前生成，且完成/流失在当日之后或未发生。
4. 按日期分组统计符合条件的申请ID数量，得到每日开放报价的准确数值。

内容的提问来源于stack exchange，提问作者mortgage man