增长团队的经典题库：12 道

差值分组法（gaps and islands）：日期序号减行号，连续段差值恒定、一断档就跳变，按「用户 + 差值」分组即得连续区间。两个细节：日期必须先 distinct（同一天多条会把行号顶歪）；PG 里 date 没有减 bigint 的运算符，row_number() 要 ::int。

with d as (
  select distinct user_id, login_at::date as d from user_logins   -- 先去重！
),
g as (
  select user_id, d,
         d - (row_number() over (partition by user_id order by d))::int as grp
  from d
)
select user_id, min(d) as 起始, max(d) as 结束, count(*) as 天数
from g
group by user_id, grp
having count(*) >= 7
order by 天数 desc, user_id;

和第 1 题同一套 CTE，去掉 having 就是全部连续区间；一个用户可以有多段。这就是「区间合并」题的标准答案。

with d as (
  select distinct user_id, login_at::date as d from user_logins
),
g as (
  select user_id, d,
         d - (row_number() over (partition by user_id order by d))::int as grp
  from d
)
select user_id, min(d) as 起始日期, max(d) as 结束日期, count(*) as 连续天数
from g
group by user_id, grp
order by user_id, 起始日期;

口径：注册次日当天有过登录算留存。分子分母都 distinct，防止一人次日登录多次被重复计数。本库注册散布在近一年、登录窗口集中在近 90 天，整体次日留存约 5% 属预期偏低；真实业务按注册日 cohort 逐日算，骨架不变（group by 注册日）。

select round(100.0 * count(distinct l.user_id) / count(distinct u.id), 1) as 次日留存率
from users u
left join user_logins l
       on l.user_id = u.id
      and l.login_at >= u.created_at::date + 1
      and l.login_at <  u.created_at::date + 2;

把窗口平移到第 7 天即可。「第 7 天当天」和「注册后 7 日内任一天」是两个口径，数字差很多，报表里必须写清是哪个（后者的区间上界放宽到 created_at + 8 天）。

select round(100.0 * count(distinct l.user_id) / count(distinct u.id), 1) as 七日留存率
from users u
left join user_logins l
       on l.user_id = u.id
      and l.login_at >= u.created_at::date + 7
      and l.login_at <  u.created_at::date + 8;

每组 TOP N 标准模板，和 D23 第 2 题同骨架，只是度量从销售额换成销量 sum(qty)。商品挂二级、按一级聚合要 categories 自连接两层。

select *
from (
  select p.name as 商品, c1.name as 一级类目,
         sum(i.qty) as 销量,
         row_number() over (partition by c1.name order by sum(i.qty) desc) as rn
  from order_items i
  join products p    on p.id = i.product_id
  join categories c2 on c2.id = p.category_id
  join categories c1 on c1.id = c2.parent_id
  group by p.id, p.name, c1.name
) t
where rn <= 3
order by 一级类目, rn;

rank 并列同名次、后面跳号；要「并列算并列、总数不跳」用 dense_rank，要「假装没并列」用 row_number。并列怎么算不是技术问题，是口径问题--先问清楚再选函数。

select user_id, sum(total_amount) as 消费额,
       rank() over (order by sum(total_amount) desc) as 名次
from orders
group by user_id
order by 名次, user_id
limit 20;

累计占比 = 一个窗口到当前行（sum over (order by 消费额 desc)）除以一个整表窗口（sum over ()）。where 累计占比 <= 80 取头部用户；边界上正好压线的用户算不算进「贡献 80% 的人」，交付前说清口径。

with u as (
  select user_id, sum(total_amount) as 消费额
  from orders
  group by user_id
),
r as (
  select user_id, 消费额,
         rank() over (order by 消费额 desc) as 名次,
         round(100.0 * sum(消费额) over (order by 消费额 desc)
               / nullif(sum(消费额) over (), 0), 2) as 累计占比
  from u
)
select *
from r
where 累计占比 <= 80
order by 名次;

口径用 status = 2 当「已支付」（本库约 78.9%）。也可以用 paid_at is not null，但本库约 9% 的单状态是已支付却没有支付时间，两个口径差约 10 个百分点--呼应 W1：报数之前先说分母。filter (where ...) 是 PG 在一条 SQL 里算多口径计数的利器。

select count(*)                                            as 下单数,
       count(*) filter (where status = 2)                  as 已支付数,
       round(100.0 * count(*) filter (where status = 2) / count(*), 1) as 支付转化率
from orders;

第 1 题的差值分组法用在布尔列上：全表行号减「按涨/不涨分区」的行号，连续为 true 的段差值恒定。第一天 lag 是 NULL 自然不参与。另一条路是 lag(gmv,1)/lag(gmv,2)/lag(gmv,3) 连比三次，但只能查定长、改天数要重写。

with d as (
  select created_at::date as 日期, sum(total_amount) as gmv
  from orders
  group by 1
),
f as (
  select 日期, gmv,
         (gmv > lag(gmv) over (order by 日期)) as 涨
  from d
),
i as (
  select 日期, gmv, 涨,
         row_number() over (order by 日期)
       - row_number() over (partition by 涨 order by 日期) as grp
  from f
)
select min(日期) as 起始, max(日期) as 结束, count(*) as 连涨天数
from i
where 涨
group by grp
having count(*) >= 3
order by 起始;

row_number(partition by 用户 order by 时间) = 2，就是「第 N 笔」类题的全部。要注意同一时刻并列时谁排第二由排序键决定，要稳定就补第二排序键 id。

select id, user_id, created_at, total_amount
from (
  select id, user_id, created_at, total_amount,
         row_number() over (partition by user_id order by created_at) as rn
  from orders
) t
where rn = 2
order by created_at;

D26 中位数的应用题：先在 CTE 里聚合出商品级销售额，再对它取 percentile_cont(0.5)。中位数对离群商品远比 avg 稳，类目体量对比常用它。

with s as (
  select p.id, p.name as 商品, c1.name as 一级类目,
         sum(i.qty * i.unit_price) as 销售额
  from order_items i
  join products p    on p.id = i.product_id
  join categories c2 on c2.id = p.category_id
  join categories c1 on c1.id = c2.parent_id
  group by p.id, p.name, c1.name
)
select 一级类目,
       percentile_cont(0.5) within group (order by 销售额) as 销售额中位数
from s
group by 一级类目
order by 一级类目;

环比 lag(gmv, 1)、同比 lag(gmv, 12)：同一个函数换偏移量而已。W4 阶段只有约 3 个月数据，同比列全 NULL 是预期现象；第 6 周 §F 时间快进后同一条 SQL 自动有值。

select 月份, gmv,
       lag(gmv)     over (order by 月份) as 上月,
       round(100.0 * (gmv - lag(gmv) over (order by 月份))
             / nullif(lag(gmv) over (order by 月份), 0), 1) as 环比pct,
       lag(gmv, 12) over (order by 月份) as 去年同月,
       round(100.0 * (gmv - lag(gmv, 12) over (order by 月份))
             / nullif(lag(gmv, 12) over (order by 月份), 0), 1) as 同比pct
from (
  select date_trunc('month', created_at) as 月份,
         sum(total_amount) as gmv
  from orders
  group by 1
) t
order by 月份;