题目: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