sql常见复杂问题

一、计算每日新增用户数，次日、三日、七日留存人数及次日、三日、七日留存率

用户登录表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