37、用户等级：
忠实用户：近7天活跃且非新用户
新晋用户：近7天新增
沉睡用户：近7天未活跃但是在7天前活跃
流失用户：近30天未活跃但是在30天前活跃
假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数

结果如下：

需要用到的表：

用户登录明细表：user_login_detail

代码：这题题目没有给出今天是哪天，所以代码是没经过测试的

with t as (select 
user_id
,min(substr(login_ts,1,10)) as frist_login_ts
,max(substr(login_ts,1,10)) as recent_login_ts
from user_login_detail
group by user_id)select 
level
,count(user_id) as cn
from (
select 
user_id
,case when (datediff('2023-01-20',recent_login_ts)<=6 and datediff('2023-01-20',frist_login_ts) >6) then '忠实用户'when datediff('2023-01-20',frist_login_ts) <=6 then '新晋用户'when datediff('2023-01-20',recent_login_ts) between 6 and 30 then '沉睡用户' else '流失用户' end as level from t
)a
group by level

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

结果如下：

需要用到的表：

用户登录明细表：user_login_detail

代码

with t as (
select 
*,count(*) over(partition by user_id, olddate) as lianxudays,count(distinct login_ts) over(partition by user_id )as days
from (
select * 
,date_add(login_ts,-rn) as olddate
from (
select  user_id,substr(login_ts,1,10) as login_ts 
,row_number() over(partition by user_id order by substr(login_ts,1,10) ) as rn
from user_login_detail
) b
)a)
, t2 as (
select 
-- 连续签到分二种情况：1、连续超过七天的， 2、未连续超过七天的  
,days
,cast (lianxudays /7 as int )+1 as week_cnt
,case when (lianxudays % 7) < 3 then 0 when (lianxudays % 7) between 3 and 6 then 2 else  8 end  external_cnt
from t 
group by 
user_id
,days
,cast (lianxudays /7 as int )+1  -- 当前第几次7天循环循环
,case when (lianxudays % 7) < 3 then 0  when (lianxudays % 7) between 3 and 6 then 2 else  8 end
)select 
user_id
,days + external_cnt*week_cnt as sum_coin_cn
from 
(
select 
user_id
,days
,week_cnt
,sum(external_cnt)as external_cntfrom t2group by user_id
,days
,week_cnt
)a