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