一、计算每日新增用户数,次日、三日、七日留存人数及次日、三日、七日留存率
用户登录表user_log,字段:user_id,log_date
select
a.first_date,
count(a.user_id) as new_num,
round(count(distinct case when datediff(substr(log_time,1,10),a.first_date)=1 then user_id else null end)/count(a.user_id),2) as next1_rate,
round(count(distinct case when datediff(substr(log_time,1,10),a.first_date)=3 then user_id else null end)/count(a.user_id),2) as next3_rate,
round(count(distinct case when datediff(substr(log_time,1,10),a.first_date)=7 then user_id else null end)/count(a.user_id),2) as next7_rate
from
(select distinct
user_id,
min(substr(log_time,1,10)) as first_date
from user_log
) a
left join
user_log as b
on a.user_id=b.user_id
group by
a.first_date
二、 连续登陆天数问题
1.计算最大连续登录天数
select
c.user_id,
max(count(c.diff)) as max_log_days
from
(select
b.user_id,
date_sub(b.log_date-b.rank) as diff
from
(select
a.user_id,
a.log_date,
row_number()over(partition by a.user_id order by a.log_date) as rank
from
(select distinct
user_id,
substr(log_time,1,10) as log_date
from user_log
) a ) b ) c
group by c.user_id
2.查询连续登录3天/7天/10天以上的用户
select
c.user_id,
case when max(count(c.diff))>=3 then '3天以上'
when max(count(c.diff))>=7 then '7天以上'
when max(count(c.diff))>=30 then '30天以上'
else '3天以内' end as '连续登录天数'
from
(select
b.user_id,
date_sub(b.log_date-b.rank) as diff
from
(select
a.user_id,
a.log_date,
row_number()over(partition by a.user_id order by a.log_date as rank
from
(select distinct
user_id,
substr(log_time,1,10) as log_date
from user_log
) a ) b ) c
group by c.user_id
-----------------------------------------------------------------------------------------
行转列
select
count(case when max(count(c.diff))>=3 then user_id else null end) as '3days_num',
count(case when max(count(c.diff))>=7 then user_id else null end) as '7days_num',
count(case when max(count(c.diff))>=30 then user_id else null end) as '30days_num'
from
(select
b.user_id,
date_sub(b.log_date-b.rank) as diff
from
(select
a.user_id,
a.log_date,
row_number()over(partition by a.user_id order by a.log_date as rank
from
(select distinct
user_id,
substr(log_time,1,10) as log_date
from user_log
) a ) b ) c
group by c.user_id
3.求连续点击三次的用户数,中间不能有别人的点击 ,最大连续天数的变形问题(腾讯微保面试)
select distinct
user_id,
count(*) as click_num
from
(select
user_id,
(rank_1-rank_2) as diff
from
(select
*,
row_number()over(order by click_time) as rank_1,
row_number()over(partition by user_id order by click_time) as rank_2
from user_click
) a ) b
group by user_id
having count(*)=3
4.连续三次(及以上)为球队得分的球员名
select
a.team,
a.name
from
(select *,
lag(name,1)over(partition by team order by scoretime) as lag1,
lag(name,2)over(partition by team order by scoretime) as lag2
from score
) a
where a.name=a.lag1
and a.lag1=a.lag2
5.请计算截至当前每个用户已经连续签到的天数(输出表仅包含当天签到的所有用户,计算其连续签到天数)
输出表【t_user_consecutive_days】:包含三个字段:日期【fdate】,用户id【fuser_id】,用户当天是否签到【fis_sign_in:0否1是】
create table t_user_consecutive_days as
Select
t1.id,
date_diff(‘day’,cast(‘20210524’as date),cast(t1. latest_date as date)) as consecutive_days
from
(Select
id,
max(fdate) as latest_date
from t_user_attendence as a
where fis_sign_in=0
group by id
) t1
6.请计算每个用户历史以来最大的连续签到天数(输出表为用户签到表中所有出现过的用户,计算其历史最大连续签到天数)
create table t_user_max_days as
select
b.id,
b.max(length(b. cut_fsign_record)) as max_record
(Select
a.id,
split(a. fsign_record,'0') as cut_fsign_record
from
(select
id,
concat(fis_sign_in) fsign_record
from t_user_attendence as a
group by id
) a) b
Group by b.id
三、topN问题
1.找出2017年入学,专业为计算机的年龄最小的10位同学
select a.name, a.id, a.class
from
(select
name,
id,
class,
rank() over(order by age) as rank
from tb_student
where in_time = 2017 and major = '计算机'
) as a
where rank <= 10
2.求最近七天内每一个一级类目下成交额排名前三的二级类目;
数据表:订单表orders,字段:
order_id,user_id,order_pay,order_time,commodity_level_1,commodity_level_2;
SELECT
commodity_level_1,
commidity_level_2,
total_pay
FROM
(SELECT *,
rank ( ) over ( PARTITION BY commodity_level_1 ORDER BY total_pay DESC ) AS rank_desc
FROM
(SELECT
commodity_level_1,
commidity_level_2,
sum( order_pay ) AS total_pay
FROM orders
WHERE datediff( now( ), CONVERT ( order_time, date ) ) <= 7
GROUP BY
commodity_level_1,
commidity_level_2
) a ) b
WHERE rank_desc <=3
3.提取2020.12.27-2020.12.31每一天消费金额排名在1-3的user_id
SELECT
oredr_date,
user_id,
total_pay
FROM
(SELECT *,
rank ( ) over ( PARTITION BY order_date ORDER BY total_pay DESC ) AS rank_desc
FROM
(SELECT
CONVERT( order_time, date ) AS order_date,
user_id,
sum( order_pay ) AS total_pay
FROM orders
WHERE
CONVERT ( order_time, date ) BETWEEN '20201227' AND '20201231'
GROUP BY
CONVERT ( order_time, date ),
user_id
ORDER BY
CONVERT ( order_time, date )
) a ) b
WHERE rank_desc <=3