排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2025-01-15 小宇宙电台的同期群分析  
SELECTlogin_date
 ,concat(new_rate,", ",ret_rate,", ",back_rate) AS pct
FROM
(
	SELECTlogin_date
	 ,round(100*COUNT(distinct CASE WHEN flag = 1 THEN usr_id else null end) /COUNT(distinct usr_id),2) AS new_rate
	 ,round(100*COUNT(distinct CASE WHEN flag = 2 THEN usr_id else null end)/COUNT(distinct usr_id),2)AS ret_rate
	 ,round(100*COUNT(distinct CASE WHEN flag = 3 THEN usr_id else null end)/COUNT(distinct usr_id),2)AS back_rate
	FROM
	(
		SELECTusr_id
		 ,login_date
		 ,min_login_date
		 ,CASE WHEN login_date = min_login_date THEN 1
		 WHEN DATEDIFF(login_date,lag_login_date) in (1,2,3) THEN 2
		 WHEN DATEDIFF(login_date,lag_login_date) >= 4 THEN 3ELSE null END AS flag
		FROM
		(
			SELECTdistinct usr_id
			 ,substr(login_time,1,10) AS login_date
			 ,substr(MIN(login_time) over(PARTITION BY usr_id ORDER BYlogin_time),1,10) AS min_login_date
				 ,substr(lag(login_time) over(PARTITION BY usr_id ORDER BYlogin_time),1,10) AS lag_login_date
			FROM user_login_log
		) AS a
	) AS a
	WHERE login_date >= '2024-01-01'
	GROUP BYlogin_date
) AS a
ORDER BY login_date
2025-01-15 小宇宙电台的同期群分析  
SELECTlogin_date
 ,concat(new_rate,", ",ret_rate,", ",back_rate) AS pct
FROM
(
	SELECTlogin_date
	 ,round(100*COUNT(distinct CASE WHEN flag = 1 THEN usr_id else null end) /COUNT(distinct usr_id),2) AS new_rate
	 ,round(100*COUNT(distinct CASE WHEN flag = 2 THEN usr_id else null end)/COUNT(distinct usr_id),2)AS ret_rate
	 ,round(100*COUNT(distinct CASE WHEN flag = 3 THEN usr_id else null end)/COUNT(distinct usr_id),2)AS back_rate
	FROM
	(
		SELECTusr_id
		 ,login_date
		 ,min_login_date
		 ,CASE WHEN login_date = min_login_date THEN 1
		 WHEN DATEDIFF(login_date,lag_login_date) < 4 AND DATEDIFF(login_date,lag_login_date) > 0 THEN 2
		 WHEN DATEDIFF(login_date,lag_login_date) >= 4 THEN 3ELSE null END AS flag
		FROM
		(
			SELECTdistinct usr_id
			 ,substr(login_time,1,10) AS login_date
			 ,substr(MIN(login_time) over(PARTITION BY usr_id ORDER BYlogin_time),1,10) AS min_login_date
				 ,substr(lag(login_time) over(PARTITION BY usr_id ORDER BYlogin_time),1,10) AS lag_login_date
			FROM user_login_log
		) AS a
	) AS a
	WHERE login_date >= '2024-01-01'
	GROUP BYlogin_date
) AS a
ORDER BY login_date
2025-01-15 小宇宙电台的同期群分析  
select login_date,concat(new_rate,", ",ret_rate,", ",back_rate) as pct
from (
select
login_date
,round(100*count(distinct case when login_date=min_login_datethen usr_id else null end)
/count(distinct usr_id),2) as new_rate
,round(100*count(distinct case when datediff(login_date,min_login_date)<4and datediff(login_date,min_login_date)>0 then usr_id else null end)/count(distinct usr_id),2) as ret_rate
,round(100*count(distinct case when datediff(login_date,min_login_date)>=4then usr_id else null end)/count(distinct usr_id),2) as back_rate
from (
select distinct
usr_id
,substr(login_time,1,10) as login_date
,substr(min(login_time) over(partition by usr_id order by login_time),1,10) as min_login_date
from user_login_log
) AS a
where login_date>='2024-01-01'
group by login_date
) AS a
order by login_date
2025-01-15 招建银行信用卡中心客户挽留-电商平台分类  
select
mch_nm as merchant_name,
case when mch_nmlike '%拼多多%' then '拼多多'
 when mch_nmlike '%寻梦%' then '拼多多'
when mch_nm like '%京东%'then '京东'
when mch_nm RLIKE '抖音|成都所见所得|空间变换科技|格物致品网络科技|合众易宝' then '抖音'
when mch_nmRLIKE '淘宝|天猫|杭州今日卖场' then '淘系'
when mch_nmRLIKE '小红书|行吟' then '小红书'
else '其他' end as platform
from (selectmch_nm
from ccb_trx_rcd
group by 1
) as a
2025-01-15 招建银行信用卡中心客户挽留-电商平台分类  
select
mch_nm as merchant_name,
case when mch_nmlike '%拼多多%' then '拼多多'
 when mch_nmlike '%寻梦%' then '拼多多'
when mch_nm like '%京东%'then '京东'
when mch_nm RLIKE '抖音|成都所见所得|空间变换科技|格物致品网络科技|合众易宝' then '抖音'
when mch_nmRLIKE '淘宝|天猫|杭州今日卖场' then '淘系'
when mch_nmRLIKE '小红书|行吟' then '小红书'
else '其他' end as platform
from (select distinct mch_nm
from ccb_trx_rcd
) as a
2025-01-15 招建银行信用卡中心客户挽留-电商平台分类  
select
mch_nm as merchant_name,
case when mch_nmRLIKE '寻梦|拼多多' then '拼多多'
when mch_nm like '%京东%'then '京东'
when mch_nm RLIKE '抖音|成都所见所得|空间变换科技|格物致品网络科技|合众易宝' then '抖音'
when mch_nmRLIKE '淘宝|天猫|杭州今日卖场' then '淘系'
when mch_nmRLIKE '小红书|行吟' then '小红书'
else '其他' end as platform
from (select distinct mch_nm
from ccb_trx_rcd
) as a