中级练习[14]：Hive SQL

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