SQL面试题一道（偏实际业务）

题目：Table Design

小 Q 做了一个宠物周边购物 APP, 商品有小猫、小狗等不同宠物的玩具周边、口粮。用户在 App中可以浏览商城首页，点击浏览商品详情，加商品到购物车里，下订单，给商品打分。

• Q1 为了更好的了解用户的需求，小 Q 希望能够方便的分析用户数据，请你帮助他设计以下几张数据表：
  - t_dwd_app_user_info_ad
  - t_dwd_app_goods_info_ad
  - t_ods_app_user_event_ah

-- Q1
-- t_dwd_app_user_info_ad
--     user_id bigint 用户id
--     user_name string 用户名
--     user_age int 用户年龄
-- t_dwd_app_goods_info_ad
--     goods_id bigint 商品id
--     goods_name string 商品名
--     goods_price decimal(5,2) 商品单价
-- t_ods_app_user_event_ah
--     stat_year string 购买年份
--     stat_date string 购买日期
--     stat_time string 购买时间
--     user_id bigint 用户id
--     goods_id bigint 商品id
--     cnt_int in 购买数量

• Q2 拿到数据表后，小 Q 希望能知道每天有多少用户购买猫粮，请基于你设计的表格 编写 SQL 实现：

SELECT
	concat( stat_year, stat_date ) AS `date`,
	count( DISTINCT user_id ) AS `cnt` 
FROM
	t_ods_app_user_event_ah 
WHERE
	goods_id = ( SELECT goods_id FROM t_dwd_app_goods_info_ad WHERE goods_name = '猫粮' ) 
GROUP BY
	stat_year,
	stat_date

• Q3 小 Q 想知道，自己APP的次日留存有多少，请编写 SQL 实现

SELECT
	concat( a.stat_year, a.stat_date ) AS `date`,
	count( distict b.user_id ) AS `1_day_retention` 
FROM
	t_ods_app_user_event_ah AS a
	LEFT JOIN t_ods_app_user_event_ah AS b ON a.user_id = b.user_id 
	AND a.stat_year = b.stat_year 
	AND a.stat_date = date_sub( b.stat_date, 1 ) 
GROUP BY
	a.stat_year,
	a.stat_date

• Q4 小 Q 想知道，昨天 DAU 里面，不同年龄段的用户群（A:18 岁以下 B:19-25岁 C:26-35岁 D 36岁以上）人数分布情况，请编写 SQL 实现

SELECT
	count( DISTINCT CASE WHEN user_age <= 18 THEN a.user_id ELSE NULL END ) AS `A:18岁以下`,
	count( DISTINCT CASE WHEN user_age BETWEEN 19 AND 25 THEN a.user_id ELSE NULL END ) AS `B:19-25岁`,
	count( DISTINCT CASE WHEN user_age BETWEEN 26 AND 35 THEN a.user_id ELSE NULL END ) AS `C:26-35岁`,
	count( DISTINCT CASE WHEN user_age >= 36 THEN a.user_id ELSE NULL END ) AS `C:36岁以上` 
FROM
	t_ods_app_user_event_ah AS a
	LEFT JOIN t_dwd_app_user_info_ad AS b ON a.user_id = b.user_id