中级练习[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天前活跃

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

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天重新累积签到天数。从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序。