数据分析-面试（SQL真题）

注：时间内有限，可能有不少瑕疵，目前只看思路

1. 淘宝

题目一

• 表格描述：现有一张用户APP访问日志表(view_log),字段结构如下：

  • user_id，用户注册时生成的ID

  • page_name，页面名称

  • viewtime，访问时间

  • page_view_duration, 访问时长

• 问题描述：统计用户最后访问的页面信息，提取每日最后停留次数最多TOP10页面

• 解答

  SELECT DISTINCT n.page_name
  FROM 
  		(
  		SELECT m.*, COUNT(page_name) OVER(PARTITION BY page_time ORDER BY viewtime DESC) cnt
  		FROM 
  				（SELECT *，
  				         RANK() OVER(PARTITION BY user_id ORDER BY page_time DESC) rk
  		FROM view_log） m 
  		WHERE m.rk = 1
  		) n  # 用户最后访问的页面信息
  WHERE n.cnt <= 10
  

题目二（重点：漏斗转化率）

• 表格描述：现有一张用户APP访问日志表（view_log），表结构请参考面试真题1。
• 题目描述：用户下单需要经历以下几个步骤，打开APP-》商品详情页-》订单填写页-》支付成功页。请统计每天该漏斗的转换情况。
• 题目解答：

SELECT l2/l1, l3/l2, l4/l3
FROM 
(
SELECT SUM(IF(page_name = 'l1'), 1, 0) l1,
			 SUM