排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月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-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%' then '拼多多' 
when mch_nm like '%京东%' or mch_nm = '网银在线-平台商户'then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%' then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%' then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' or mch_nm like '%全球购%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%' then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司') or mch_nm like '%全球购%' then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%' or mch_nm like '%全球购%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%'or mch_nm like '%全球购%'then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%' or mch_nm like '%格物%' then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmlike '%淘宝%' or mch_nm like '%天猫%'then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户','京东支付-北京京东世纪贸易有限公司')then '京东' 
when mch_nmin ('支付宝-天猫','支付宝-淘宝','财付通-格物致品网络科技有限公司') then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东')then '京东' 
when mch_nmin ('支付宝-天猫','支付宝-淘宝','财付通-格物致品网络科技有限公司') then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm
2024-12-30 招建银行信用卡中心客户挽留-电商平台分类  
with main as (
select distinct mch_nm from ccb_trx_rcd
)
select mch_nm as merchant_name,
case when mch_nm like '%拼多多%' or mch_nm like '%寻梦信息%'then '拼多多' 
when mch_nm in ('微信-京东平台商户','支付宝-京东','网银在线-平台商户')then '京东' 
when mch_nmin ('支付宝-天猫','支付宝-淘宝','财付通-格物致品网络科技有限公司') then '淘系'
when mch_nm like '%小红书%'then '小红书'
when mch_nm like '%抖音%' or mch_nm like '%北京空间变换科%' or mch_nm like '%所见所得科技%'or mch_nm like '%合众%' then '抖音' 
else '其他'
end as platform from main
order by mch_nm