中级练习[14]：Hive SQL

目录

1. 统计活跃间隔对用户分级结果

1.1 题目需求

1.2 代码实现

2. 连续签到领金币数

2.1 题目需求

2.2 代码实现

3. 国庆期间的7日动销率和滞销率

3.1 题目需求

3.2 代码实现

4. 同时在线最多的人数

4.1 题目需求

4.2 代码实现

---

1. 统计活跃间隔对用户分级结果

1.1 题目需求

用户等级定义：

• 忠实用户：近7天活跃且非新用户
• 新晋用户：近7天新增
• 沉睡用户：近7天未活跃但是在7天前活跃
• 流失用户：近30天未活跃但是在30天前活跃

假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数。

Level（用户等级）	Cn（用户数量）
忠实用户	6
新增用户	3
沉睡用户	1

1.2 代码实现

SELECT
  t2.level,
  COUNT(*)
FROM
  (
    SELECT
      uld.user_id,
      CASE
        WHEN (DATE_FORMAT(MAX(uld.login_ts), 'yyyy-MM-dd') <= DATE_SUB(TODAY(), 30)) THEN '流失用户'
        WHEN (DATE_FORMAT(MIN(uld.login_ts), 'yyyy-MM-dd') <= DATE_SUB(TODAY(), 7) AND DATE_FORMAT(MAX(uld.login_ts), 'yyyy-MM-dd') >= DATE_SUB(TODAY(), 7)) THEN '忠实用户'
        WHEN (DATE_FORMAT(MIN(uld.login_ts), 'yyyy-MM-dd') >= DATE_SUB(TODAY(), 7)) THEN '新增用户'
        WHEN (DATE_FORMAT(MIN(uld.login_ts), 'yyyy-MM-dd') <= DATE_SUB(TODAY(), 7) AND DATE_FORMAT(MAX(uld.login_ts), 'yyyy-MM-dd') <= DATE_SUB(TODAY(), 7)) THEN '沉睡用户'
      END AS level
    FROM
      user_login_detail uld
    JOIN
      (
        SELECT 
          DATE_FORMAT(MAX(login_ts), 'yyyy-MM-dd') AS today 
        FROM
          user_login_detail
      ) t1
    ON
      1 = 1
    GROUP BY 
      uld.user_id, t1.today
  ) t2
GROUP BY
  t2.level;

2. 连续签到领金币数

2.1 题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。每连续签到7天重新累积签到天数。从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序。

User_id（用户id）	Sum_coin_cn（金币总数）
101	7
109	3
107	3
102	3
106	2