1. 题目需求
用户每天签到可以领1金币,并可以累计签到天数,连续签到的第3、7天分别可以额外领2和6金币。
每连续签到7天重新累积签到天数。
从用户登录明细表中求出每个用户金币总数,并按照金币总数倒序排序
结果如下:
2. 需要用到的表
用户登录明细表:user_login_detail
3. 查询sql
SELECT
user_id,
sum(coin) as sum_coin_cn
FROM
(
SELECT
user_id,
(
case
when rn_new % 7 = 3 then 3
when rn_new % 7 = 7 then 7
else 1
end
) coin
FROM
(
SELECT
*,
date_sub (login_date, rn) date_temp,
rank() over (
partition by
user_id,
date_sub (login_date, rn)
order by
login_date
) rn_new
FROM
(
SELECT
user_id,
to_date (login_ts) as login_date,
rank() over (
partition by
user_id
order by
to_date (login_ts)
) rn
FROM
user_login_detail
GROUP BY
user_id,
to_date (login_ts)
) t
) t2
) t3
GROUP BY
user_id