【SQL 09 2022年SHEIN面试题 - 品类会员数】

题目描述

请计算每个月不同品类不同类型用户的用户数量。
用户类型如下：

• 品类新会员数： 统计月当月首次购买X品类的用户数
• 品类老会员数： 截止到统计月之前，购买过X品类的用户数
• 品类老会员复购数： 截止到统计月之前购买过X品类，且在统计月内再次购买X品类的用户数
• 当期新会员转化数： 当期新会员（首次消费在统计月内的用户数）中，有多少用户购买了X品类
• 品类历史新会员数： 截止到统计月之前，历史曾经发生过购买行为但没有购买X品类的用户数
• 品类历史新会员转化数： 品类历史新会员中，有多少用户购买了X品类

全量销售明细表 dw_oder_detail_df：

字段名	字段类型	字段含义
pay_time	varchar	付款日期（yyyy-MM-dd HH:mm:ss格式，假设一个用户同一秒中只能支付一个订单）
sku	varchar	商品名称
user_id	varchar	用户ID
cate_nm	varchar	商品对应的品类

最终输出样式：

以2021-12月为例（实际SQL结果应包括所有月份），品类只有A,B,C三个，则数据的输出格式为：

月份	品类	品类新会员数	品类老会员数	品类老会员复购数	当期新会员转化数	品类历史新会员数	品类历史新会员转化数
2021-12	A	100	1000	300	20	20000	80
2021-12	B	100	1000	300	20	20000	80
2021-12	C	100	1000	300	20	20000	80
2021-12	A	解释：表示12月当月有100个人第一次购买X品类	解释：截止11月底，总共有1000个人买过X品类	解释：左侧1000人中，12月有300人又买了X品类	解释：首次购买在本月的用户中，有20个人在首单月购买了X品类	解释：截止到11月底，总共又2W个购买过用户没有买过A品类的用户	解释：左侧2W用户中，有80人在12月第一次买了A品类

题目分析

首先我们对于6个指标口径观察：
1.“品类新会员数” 和 “品类老会员数” 的关系就是通过——会员首次购买该品类动作发生的月份来区分。
2.“品类老会员复购数” 、 “当期新会员转化数” 、“品类历史新会员数”、“品类历史新会员转化数”的关系通过——对比会员首次购买该品类的月份和会员各月份某品类是否购买来区分的。
因此，我们核心就是查询：会员首次购买某品类月份 、 会员每个月份每个品类该品类的购买情况 以及 会员首次购买的月份。
品类新会员数 :该品类该会员的购买月份 = 该品类该会员的首次购买月份
品类老会员数 :该品类该会员的购买月份 > 该品类该会员的首次购买月份

SQL实现

一、会员首次购买某品类月份 first_month_for_cate_user & 会员首次购买月份 fisrt_month_for_user

SELECT