右下角图片

排名

用户解题统计

过去一年提交了

勋章 ①金银铜:在竞赛中获得第一二三名;②好习惯:自然月10天提交;③里程碑:解决1/2/5/10/20/50/100/200题;④每周打卡挑战:完成每周5题,每年1月1日清零。

收藏

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-30 10月1日后再也没活跃过的用户 
题目描述和代码的实现,10-01所属区间不一致呀
注意开闭区间
2024-11-29 小宇宙电台的同期群分析 
with data1 as (
    select distinct usr_id,
                    date(login_time) as login_date
    from user_login_log
),
     data2 as (
         select usr_id,
                login_date,
                first_value(login_date) over (partition by usr_id order by login_date) as `start_date`,
                lag(login_date) over (partition by usr_id order by login_date) as `pre_date`
         from data1
     ),
     data3 as (
         select  distinct data1.usr_id,
                         data1.login_date,
                         case
                             when pre_date is null and datediff(data1.login_date,start_date) = 0 then 'flag-1'
                             when pre_date is not null and datediff(data1.login_date,pre_date) <= 3 then 'flag-2'
                             else 'flag-3'
                         end as flag
         from data1
                left join data2
                            on (data1.usr_id,data1.login_date) = (data2.usr_id,data2.login_date)
     ),
     data4 as (
         select login_date,
                round((sum(if(flag = 'flag-1', 1, 0)) / count(1)) * 100, 2) as rate_flag_1,
                round((sum(if(flag = 'flag-2', 1, 0)) / count(1)) * 100, 2) as rate_flag_2,
                round((sum(if(flag = 'flag-3', 1, 0)) / count(1)) * 100, 2) as rate_flag_3
         from data3
         group by login_date
     )
select login_date,
       concat_ws(',', rate_flag_1, rate_flag_2, rate_flag_3) as pct
from data4
where year(login_date) = '2024'
group by login_date
order by login_date;
求指点这个思路的错误呀,一直是0过不去
啥也没说
2024-11-29 小结(2)越花越多是死罪,按月统计Substr 
除了把条件二和条件一分开列出来,还有什么坑点呀,汗流浃背了
看了下你代码,感觉逻辑乱了啊哈哈哈
2024-11-29 小结(1)大数据早就能扫黄,找足证据不慌张 
交了十几次,最后发现是字段名不对
哈哈 好多人反应这个问题,现在已经修正了。不需要严格对应字段名

提交记录

提交日期 题目名称 提交代码
2025-02-18 快手面试真题(3)同时在线人数峰值时点 
with data1 as(
select
	ks_live_t1.live_id,
	ks_live_t1.enter_time,
	ks_live_t1.leave_time,
	ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select 
	live_id,
	live_nm,
	enter_time as action_time,
	1 as flag
from data1
union all
select
	live_id,
	live_nm,
	leave_time as action_time,
-1 as flag
from data1
),
data3 as(
select 
live_id,
live_nm,
	action_time,
sum(flag)over(partition by live_id,live_nm order by action_time) as each_cnt
from data2
),
data4 as(
select
	live_id,
	live_nm,
	action_time,
	each_cnt,
	max(each_cnt)over(partition by live_id,live_nm) as max_cnt
from data3
)
select 
	live_id,
live_nm,
max_cnt as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from data4
where each_cnt = max_cnt
group by live_id,live_nm,max_cnt
order by max_cnt desc;
2025-02-18 快手面试真题(3)同时在线人数峰值时点 
with data1 as(
select
	ks_live_t1.live_id,
	ks_live_t1.enter_time,
	ks_live_t1.leave_time,
	ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select 
	live_id,
	live_nm,
	enter_time as action_time,
	1 as flag
from data1
union all
select
	live_id,
	live_nm,
	leave_time as action_time,
-1 as flag
from data1
),
data3 as(
select 
live_id,
live_nm,
	action_time,
sum(flag)over(partition by live_id,live_nm order by action_time) as each_cnt
from data2
),
data4 as(
select
	live_id,
	live_nm,
	action_time,
	each_cnt,
	max(each_cnt)over(partition by live_id,live_nm) as max_cnt
from data3
)
select 
	live_id,
live_nm,
max_cnt as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from data4
where each_cnt = max_cnt
group by live_id,live_nm,max_cnt
2025-02-18 快手面试真题(2)同时在线人数峰值 
with data1 as(
select
	ks_live_t1.live_id,
	ks_live_t2.live_nm,
	enter_time,
	leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
	live_id,
	live_nm,
	enter_time as action_time,
	1 as flag
from data1
union all
select
	live_id,
	live_nm,
	leave_time as action_time,
	-1 as flag
from data1
)
select
	live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
	action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
order by max_online_users desc;
2025-02-18 快手面试真题(2)同时在线人数峰值 
with data1 as(
select
	ks_live_t1.live_id,
	ks_live_t2.live_nm,
	enter_time,
	leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
	live_id,
	live_nm,
	enter_time as action_time,
	1 as flag
from data1
union
select
	live_id,
	live_nm,
	leave_time as action_time,
	-1 as flag
from data1
)
select
	live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
	action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
order by max_online_users desc;
2025-02-18 快手面试真题(2)同时在线人数峰值 
with data1 as(
select
	ks_live_t1.live_id,
	ks_live_t2.live_nm,
	enter_time,
	leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
	live_id,
	live_nm,
	enter_time as action_time,
	1 as flag
from data1
union
select
	live_id,
	live_nm,
	leave_time as action_time,
	-1 as flag
from data1
)
select
	live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
	action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
2025-02-18 分类别人均在线时长最火直播间 
with data1 as(
select 
	ks_live_t1.live_id,
	ks_live_t2.live_type,
	ks_live_t2.live_nm,
	sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as enter_duration,
	count(distinct ks_live_t1.usr_id) as enter_cnt
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
where enter_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59'
group by live_type,live_id,live_nm
),
data2 as(
select 
live_type,
	live_id,
	live_nm,
	enter_duration,
	enter_cnt,
	enter_duration / enter_cnt as avg_duration
from data1
),
data3 as(
select 
live_type,
	live_id,
	live_nm,
	enter_duration,
	enter_cnt,
	avg_duration,
	row_number()over(partition by live_type order by avg_duration desc) as rn
from data2
)
select 
	live_id,
live_nm,
live_type,
enter_duration as total_duration,
enter_cnt as total_users,
avg_duration
from data3
where rn = 1
order by live_id;
2025-02-18 分类别的最火直播间 
with data1 as(
select
	ks_live_t1.live_id,
live_type,
	live_nm,
	count(*) as enter_cnt,
	row_number()over(partition by ks_live_t2.live_type order by count(*) desc)as rn
from ks_live_t1
left join ks_live_t2 
on ks_live_t1.live_id = ks_live_t2.live_id
where date_format(ks_live_t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by ks_live_t1.live_id,ks_live_t2.live_type,ks_live_t2.live_nm
)
select
	live_id,
live_nm,
live_type,
enter_cnt
from 
	data1
where rn = 1
order by live_id;
2025-02-18 销售金额前10的商品信息(2) 
select
	order_date,
goods_id,
total_gmv,
ranking
from(
select 
order_date,
goods_id,
total_gmv,
row_number()over(partition by order_date order by total_gmv) as ranking
from(
 select 
date_format(order_time,'%Y-%m-%d') as order_date,
goods_id,
sum(order_gmv) as total_gmv
from order_info
wheredate_format(order_time,'%Y-%m')='2024-10'
group by date_format(order_time,'%Y-%m-%d'),goods_id
)a
)b
where ranking <= 3
2025-02-18 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
with data0 as (
    select 
        usr_id, 
        trx_time,
        trx_amt,
        mch_nm,
        lag(trx_time, 3) over (partition by usr_id order by trx_time) as prev_3_trx_time
    from 
        cmb_usr_trx_rcd
    where 
        mch_nm = '红玫瑰按摩保健休闲'
),
data1 as (
    select 
        usr_id, 
        trx_time,
        trx_amt,
        mch_nm,
			datediff(trx_time,prev_3_trx_time) as diff
	from
		data0
)
select distinct usr_id
from data1
where diff <= 3
order by usr_id
2025-02-18 窗口函数(6)隔三差五去召妓,统计间隔用偏移 
select
	usr_id,
trx_time,
trx_amt,
mch_nm,
pre_trx_time,
datediff(trx_time,pre_trx_time) as days_since_last_fvck
from(
 select 
usr_id,
trx_time,
trx_amt,
mch_nm,
lag(trx_time,1) over(partition by mch_nm order by trx_time) as pre_trx_time
from cmb_usr_trx_rcd
where usr_id=5201314520
and mch_nm = '红玫瑰按摩保健休闲'
)a
2025-02-18 窗口函数(6)隔三差五去召妓,统计间隔用偏移 
select 
	usr_id,
trx_time,
trx_amt,
mch_nm,
lag(trx_time,1) over(partition by mch_nm order by trx_amt) as pre_trx_time,
datediff(trx_time,lag(1) over(partition by mch_nm order by trx_amt)) as days_since_last_fvck
from cmb_usr_trx_rcd
where usr_id=5201314520
and mch_nm = '红玫瑰按摩保健休闲'
order by trx_time
2025-02-18 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
with data0 as (
select 
	cmb_usr_trx_rcd.mch_nm,
	trx_time,
	trx_amt
from cmb_usr_trx_rcd
left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
where cmb_mch_typ.mch_typ = '休闲娱乐' and usr_id=5201314520
),
data1 as (
select date_format(trx_time,'%Y-%m') as trx_mon,
 trx_amt
from data0
where date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
)
,data2 as (
select 
trx_mon,
sum(trx_amt) as monthly_amt
from data1
group by trx_mon
)
select
	trx_mon,
sum(monthly_amt) over(order by trx_mon) as trx_amt
from data2
2025-02-18 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
with data1 as (
select date_format(trx_time,'%Y-%m') as trx_mon,
 trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520
and date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
)
,data2 as (
select 
trx_mon,
sum(trx_amt) as monthly_amt
from data1
group by trx_mon
)
select
	trx_mon,
sum(monthly_amt) over(order by trx_mon) as trx_amt
from data2
2025-02-18 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with data0 as (
select usr_id,
	 cmb_usr_trx_rcd.mch_nm,
		 trx_time,
		 trx_amt,
		 mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
 mch_nm,
 count(*) as cnt,
 row_number()over(partition by mch_typ order by count(*) desc) as rn
from data0
where usr_id = '5201314520'
and mch_typ is not null
and mch_typ in('交通出行','休闲娱乐','咖啡奶茶')
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
		 count(*) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt desc
limit 1
)
select 
	 'all' as mch_typ,
 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data2
union all 
select mch_typ,
	 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data1
where rn = 1;
2025-02-18 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with data0 as (
select usr_id,
	 cmb_usr_trx_rcd.mch_nm,
		 trx_time,
		 trx_amt,
		 mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
 mch_nm,
 count(*) as cnt,
 row_number()over(partition by mch_typ order by count(*) desc) as rn
from data0
where usr_id = '5201314520'
and mch_typ is not null
and mch_typ in('交通出行','休闲娱乐','咖啡奶茶')
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
		 count(*) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt
limit 1
)
select 
	 'all' as mch_typ,
 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data2
union all 
select mch_typ,
	 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data1
where rn = 1;
2025-02-18 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with data0 as (
select usr_id,
	 cmb_usr_trx_rcd.mch_nm,
		 trx_time,
		 trx_amt,
		 mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
 mch_nm,
 count(1) as cnt,
 row_number()over(partition by mch_typ,mch_nm order by count(1) desc) as rn
from data0
where usr_id = '5201314520'
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
		 count(1) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt
limit 1
)
select 
	 'all' as mch_typ,
 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data2
union all 
select mch_typ,
	 mch_nm,
 cnt as trx_cnt,
 1 as rnk
from data1
where rn = 1;
2025-02-18 窗口函数(1)年度前三和每月前三,搞懂排序窗口函数 
with data1 as (
select mch_nm,
		 sum(trx_amt) as sum_trx_amt
from
	cmb_usr_trx_rcd
where
	usr_id = '5201314520'
	and year(trx_time) = '2024'
group by mch_nm
order by sum_trx_amt desc
limit 3
),
data2 as (
select mch_nm,
		 date_format(trx_time,'%Y-%m') as trx_month,
		 sum(trx_amt) as sum_trx_amt,
		 row_number()over(partition by date_format(trx_time,'%Y-%m') order by sum(trx_amt) desc) as rn
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = '2024'
group by mch_nm,trx_month
),
data3 as (
select trx_month,
		 mch_nm,
		 sum_trx_amt
from
	data2
where rn <= 3
)
select 
	'2024' as trx_mon,
mch_nm,
sum_trx_amt
from data1
union all
select
	trx_month as trx_mon,
mch_nm,
sum_trx_amt
from data3
order by trx_mon,
		 sum_trx_amt desc;
2024-11-30 招建银行信用卡中心客户挽留-电商平台分类 
select
		mch_nm as merchant_name,
 case
when mch_nm rlike '.*-.*(多多|寻梦).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟|优酷|阿里).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条|巨量|飞书).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a
2024-11-30 招建银行信用卡中心客户挽留-电商平台分类 
select
		mch_nm as merchant_name,
 case
when mch_nm rlike '.*-.*(多多|寻梦信息).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟|优酷|阿里).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a
2024-11-30 招建银行信用卡中心客户挽留-电商平台分类 
select
		mch_nm as merchant_name,
 case
when mch_nm rlike '.*-.*(多多|寻梦信息).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a