排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-30 Halo出行-通勤活跃用户标签开发  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-25 小宇宙电台的同期群分析  
终于可以了。。。
 
啥也没说
2024-12-25 小宇宙电台的同期群分析  
我也求一个答案。我提交的代码跑出来都是0分
 
啥也没说
2024-12-25 小宇宙电台的同期群分析  
示例里是真实结果吗？提交答案始终不对，也看不出来哪里不对。。
 
是真实的结果。
你再提交试试，刚看了你的答案应该可以得满分。

提交记录

提交日期 题目名称 提交代码
2025-02-25 抖音面试真题（1）T+1日留存率  
with data1 as (
    select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
),
data2 as (
    select 
        T.usr_id, 
		T_1.usr_id as r_usr_id,
        T.login_date as T_date, 
        T_1.login_date as T_1_date 
    from 
        data1 as T 
    left join 
        data1 as T_1 
    on 
        T.usr_id = T_1.usr_id 
        and datediff(T.login_date, T_1.login_date) = -1
)
select 
    T_date as first_login_date, 
concat(round(count(distinct r_usr_id)/count(distinct usr_id)*100, 2), '%') 
from 
    data2 
group by 
    T_date 
order by 
    T_date;
2025-02-25 抖音面试真题（1）T+1日留存率  
with data1 as (
    select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
),
data2 as (
    select 
        T.usr_id, 
        T.login_date as T_date, 
        T_1.login_date as T_1_date 
    from 
        data1 as T 
    left join 
        data1 as T_1 
    on 
        T.usr_id = T_1.usr_id 
        and datediff(T.login_date, T_1.login_date) = -1
)
select 
    T_date as first_login_date, 
    concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate 
from 
    data2 
group by 
    T_date 
order by 
    T_date;
2025-02-25 抖音面试真题（1）T+1日留存率  
with base_data as (
select distinct
	usr_id
	,login_time
from 
	user_login_log
where 
	datediff(current_date,date(login_time))<=30
)
,liucun_ratio as (
select 
 	t.usr_id
	,t.login_time as t_date
	,t_1.login_time as t_1_date
from
	base_data as t 
left join base_data as t_1
on t.usr_id = t_1.usr_id and datediff(t_1.login_time,t.login_time)=1
)
select 
	substr(t_date,1,10) as t_date,
concat(round(avg(t_1_date is not null)*100, 2), '%')as retention_rate
from 
	liucun_ratio
group by substr(t_date,1,10)
order by substr(t_date,1,10)
2025-02-25 抖音面试真题（1）T+1日留存率  
with base_data as (
select distinct
	usr_id
	,login_time
from 
	user_login_log
where 
	datediff(current_date,date(login_time))<=30
)
,liucun_ratio as (
select 
 	t.usr_id
	,t.login_time as t_date
	,t_1.login_time as t_1_date
from
	base_data as t 
left join base_data as t_1
on t.usr_id = t_1.usr_id and datediff(t_1.login_time,t.login_time)=1
)
select 
	substr(t_date,1,10) as t_date,
concat(round(count(distinct if(t_1_date is not null,usr_id,0))/count(distinct usr_id)*100,2),'%') as retention_rate
from 
	liucun_ratio
group by substr(t_date,1,10)
order by substr(t_date,1,10)
2025-02-25 抖音面试真题（1）T+1日留存率  
with base_data as (
select distinct
	usr_id
	,login_time
from 
	user_login_log
where 
	datediff(current_date,date(login_time))<=30
)
,liucun_ratio as (
select 
 	t.usr_id
	,t.login_time as t_date
	,t_1.login_time as t_1_date
from
	base_data as t 
left join base_data as t_1
on t.usr_id = t_1.usr_id and datediff(t_1.login_time,t.login_time)=1
)
select 
	substr(t_date,1,10) as t_date,
concat(round(count(distinct if(t_1_date is not null,usr_id,0))/count(distinct usr_id),2)*100,'%') as retention_rate
from 
	liucun_ratio
group by substr(t_date,1,10)
order by substr(t_date,1,10)
2025-02-25 抖音面试真题（1）T+1日留存率  
with base_data as (
select distinct
	usr_id
	,login_time
from 
	user_login_log
where 
	datediff(current_date,date(login_time))<=30
)
,liucun_ratio as (
select 
 	t.usr_id
	,t.login_time as t_date
	,t_1.login_time as t_1_date
from
	base_data as t 
left join base_data as t_1
on t.usr_id = t_1.usr_id and datediff(t_1.login_time,t.login_time)=1
)
select 
	substr(t_date,1,10) as t_date,
concat(round(count(distinct if(t_1_date is not null,usr_id,0))/count(distinct usr_id),2)*100,'%') as retention_rate
from 
	liucun_ratio
group by substr(t_date,1,10)
2025-02-25 抖音面试真题（1）T+1日留存率  
with base_data as (
select distinct
	usr_id
	,login_time
from 
	user_login_log
where 
	datediff(current_date,date(login_time))<=30
)
,liucun_ratio as (
select 
 	t.usr_id
	,t.login_time as t_date
	,t_1.login_time as t_1_date
from
	base_data as t 
left join base_data as t_1
on t.usr_id = t_1.usr_id and datediff(t_1.login_time,t.login_time)=1
)
select 
	substr(t_date,1,10) as t_date,
count(distinct if(t_1_date is not null,usr_id,0))/count(distinct usr_id) as retention_rate
from 
	liucun_ratio
group by substr(t_date,1,10)
2025-02-25 查询所有起点或终点为“海底捞西丽店”的行程记录  
select 
cust_uid
,start_loc
,end_loc
,start_tm
,car_cls
from didi_sht_rcd
where start_loc = '海底捞西丽店' or end_loc = '海底捞西丽店'
order by start_tm
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-20'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-20'
)
,valid_start_locations AS (
SELECT '望京' AS loc UNION ALL
SELECT '望京南' UNION ALL
SELECT '阜通' UNION ALL
SELECT '将台西'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-20'
)
,valid_start_locations AS (
SELECT '望京' AS loc UNION ALL
SELECT '望京南' UNION ALL
SELECT '阜通' UNION ALL
SELECT '将台西'
),
valid_end_locations AS (
SELECT '北京机床研究所' AS loc UNION ALL
SELECT '中关村电子城' UNION ALL
SELECT '凯德广场' UNION ALL
SELECT '天启大厦' UNION ALL
SELECT '恒通国际商务园' UNION ALL
SELECT '瀚海国际大厦' UNION ALL
SELECT '西门子大厦'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc IN (SELECT loc FROM valid_start_locations) and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-20'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,active_mark1+ifnull(active_mark2,0) as active_tag
from (select user_id,0 as active_mark1 from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
,1 as active_mark2
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-15'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
 ,(MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,
 (MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,
(YEAR(next_month) - YEAR(month_time)) * 12 + (MONTH(next_month) - MONTH(month_time)) as mark
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-07-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,TIMESTAMPDIFF(MONTH,month_time,next_month)as mark 
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-08-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,TIMESTAMPDIFF(MONTH,month_time,next_month)as mark 
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-06-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,TIMESTAMPDIFF(MONTH,month_time,next_month)as mark 
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id
2024-12-31 Halo出行-通勤活跃用户标签开发  
with user_all as ( 
select user_id
,date_format(start_time,'%Y-%m-01') as month_time 
,date(start_time) as day_time 
,start_loc
,end_loc
from hello_bike_riding_rcd 
where start_time >= '2024-05-01'
)
,active_user as ( 
select 
user_id
,month_time 
 ,lead(month_time,2)over(partition by user_id) as next_month
from user_all a 
where (a.start_loc in ('望京','望京南','阜通','将台西') and a.end_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦')) or ( a.end_loc in ('望京','望京南','阜通','将台西') and a.start_loc in ('北京机床研究所','中关村电子城','凯德广场','天启大厦','恒通国际商务园','瀚海国际大厦','西门子大厦'))
group by user_id,month_time
having count(distinct day_time)>=5 
)
select a.user_id
,IF(b.user_id IS NULL, 0, 1) as active_tag
from (select user_id from user_all group by user_id)a
left join (
select
user_id
,TIMESTAMPDIFF(MONTH,month_time,next_month)as mark 
from active_user where next_month is not null 
	)b
on b.mark = 2 and a.user_id = b.user_id