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
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页