排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-01-26 小丑竟是我自己  未解决
2025-01-25 海王:1小时发3个红包  已解决
2025-01-20 德州扑克起手牌-同花两高张  未解决
2025-01-20 通勤、午休、临睡个时间段活跃人数分布  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-02-03 餐厅的客单价增长趋势 
//自己记录用
**考察coalesce用法,计算同环比场景,被做对比的数据为0时,取0
**考察nullif用法,计算比例,同环比,上期或同期为0, 做分母,避免被除数为0,nullif判断被除数是否为0,为0则取null
**考察round用法,计算同环比,保留小数,比值先乘以100,再四舍五入保留小数部位
**考察开窗函数
啥也没说
2025-01-26 小丑竟是我自己 
比例是 (1-A/B)*100 吗
是的~,你再试下应该能通过了
2025-01-26 小丑竟是我自己 
知识点:
rcv_datetime != '1900-01-01 00:00:00'  1900-01-01代表没有列值的记录,此处用作判断没有被接收的红包的记录
啥也没说
2025-01-21 德州扑克起手牌-同花两高张 
select count(1) as cnt,
			(select count(1) from hand_permutations) as ttl_cnt,
            round(count(1)/(select count(1) from hand_permutations),4) as p
from 
(select distinct card1,card2
from hand_permutations 
where right(card1,1)=right(card2,1)
and (left(card1,length(card1)-1) between 'A' and 'K') 
and (left(card2,length(card2)-1) between 'A' and 'K')) t1
啥也没说
2025-01-21 文科潜力股 
SELECT *
 FROM scores 
 where ((subject ='历史' and score>90)
 or (subject ='地理' and score>90)
 or (subject ='政治' and score>90))
 and exam_date='2024-06-30'
 order by score desc,student_id,subject
啥也没说
2025-01-21 找出所有以酒店为起点或终点的类别组合的最热门路线 
注意关联条件的列名,以及题解是求最热门的路线组合,需要再次分区(分组)排名
啥也没说
2025-01-21 至少两门科目大于等于110分的学生 
select *
 from subject_score
 where (chinese>=110 and math>=110)
 or (chinese>=110 and english>=110)
 or (math>=110 and english>=110)
 order by student_id

select a1.*
from subject_score a1
inner join 
(select student_id,
			(c1+m1+e1) as tll
from
 (select student_id,
 			case when chinese>=110 then 1 else 0 end as c1,
            case when math>=110 then 1 else 0 end as m1,
            case when english>=110 then 1 else 0 end as e1
 from subject_score) t 
) a2 
on a1.student_id=a2.student_id
where a2.tll>=2


select *
from subject_score
where if(chinese>=110,1,0)+if(math>=110,1,0)+if(english>=110,1,0) >=2
啥也没说
2025-01-21 渣男腰子可真行,端午中秋干不停 
多个where筛选条件,注意and和or的运算优先级,括号的使用
啥也没说
2025-01-21 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
计算客单数和消费人数,人均客单数的区别
啥也没说
2025-01-21 条件过滤(3)Hour函数很给力,组合条件要仔细 
注意输出排序
啥也没说
2025-01-21 通勤、午休、临睡个时间段活跃人数分布 
使用变量?
啥也没说
2025-01-21 通勤、午休、临睡个时间段活跃人数分布 
select 
(select count(distinct usr_id)
from user_login_log
where 
 ((substr(login_time,12,8) between '07:30:00' and '09:30:00')
or (substr(login_time,12,8) between '18:30:00' and '20:30:00'))
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as commute,
(select count(distinct usr_id)
from user_login_log
where (substr(login_time,12,8) between '11:30:00' and '14:00:00')
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as lunch_break,
(select count(distinct usr_id)
from user_login_log
where (substr(login_time,12,8) between '22:30:00' and '01:00:00')
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as bedtime
啥也没说
2025-01-21 性别已知的听歌用户 
在听歌记录表取值,关联用户信息表
啥也没说
2025-01-20 各地区包含“中国”或“中”字开头的公司数量及比例 
和上题例不同,公司名称条件不用去除以“中国”结尾的,且包含“中”字的公司不需要限定以中字开头。
注意order by之后不接聚合函数,order by和聚合函数有不同的语义和执行顺序。

输出结果proportiton的小数位为4位,结果未通过。
啥也没说
2025-01-20 每个行业最早上市的公司 
只考虑那些名称以“中国”开头或包含“中”字的公司:
1. 以“中国”开头的公司,需要同时剔除以“中国”结尾的公司
2. 包含“中”字的公司,题解为以“中”字开头的公司,不是名称出现“中”字的公司
这里有一些误解
啥也没说
2025-01-19 条件过滤(1)异地男友有异常,数分闺蜜来帮忙 
使用date_format函数可以吗
可以的。实际业务中,如果用于过滤,怎么方便怎么来;如果是用于输出,要严格遵守输出要求,因为后续就要导入到pythonexcel啥的,会有格式要求,那就推荐date_format。😁

提交记录

提交日期 题目名称 提交代码
2025-02-03 深圳气温异常年份 
select dt as year,
		 cast(avg_tmp_h as decimal(10,2)) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2
2025-02-03 深圳气温异常年份 
select dt as year,
		 round(avg_tmp_h,2) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2
2025-02-03 深圳气温异常年份 
select dt as year,
		 round(avg_tmp_h,2) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when avg_tmp_h-lag(avg_tmp_h,1) over()>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(tmp_h) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2
2025-02-03 餐厅的客单价增长趋势 
WITH monthly_avg_spending AS (
    SELECT 
        mch_nm,
        DATE_FORMAT(trx_dt, '%Y-%m') AS month,
        AVG(trx_amt) AS avg_spending
    FROM mt_trx_rcd1
    GROUP BY mch_nm, month
),
lagged_avg_spending AS (
    SELECT 
        mas.mch_nm,
        mas.month,
        mas.avg_spending,
        LAG(mas.avg_spending) OVER (PARTITION BY mas.mch_nm ORDER BY mas.month) AS prev_avg_spending
    FROM monthly_avg_spending mas
)
SELECT 
    las.mch_nm,
    las.month,
    las.avg_spending,
    ROUND(((las.avg_spending - COALESCE(las.prev_avg_spending, 0)) / NULLIF(las.prev_avg_spending, 0)) * 100, 2) AS growth_rate
FROM lagged_avg_spending las
ORDER BY las.mch_nm, las.month;
2025-01-27 餐厅的客单价增长趋势 
select mch_nm,
			month,
avg_spending,
case when lag(avg_spending,1) over(partition by mch_nm order by month) is null 
		then 'null'
else round((avg_spending/
lag(avg_spending,1) over(partition by mch_nm order by month)-1)*100,2)
end as growth_rate
from 
(select mch_nm,
 			date_format(trx_dt,'%Y-%m') as month,
avg(trx_amt) as avg_spending 
from mt_trx_rcd1
group by 1,2
order by 1,2) t1
2025-01-26 小丑竟是我自己 
select snd_usr_id as User_ID,
			sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)
							as Total_Received_Amount,
			case when sum(pkt_amt)=0 then 0
		else (1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100 end as Clown_Index
from tx_red_pkt_rcd
group by snd_usr_id
having case when sum(pkt_amt)=0 then 0
		else (1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100 end between 88 and 99
order by Clown_Index desc
2025-01-26 小丑竟是我自己 
select snd_usr_id as User_ID,
			sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)
							as Total_Received_Amount,
			case when sum(pkt_amt)=0 then 0
		else round((1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100,6) end as Clown_Index
from tx_red_pkt_rcd
group by snd_usr_id
having case when sum(pkt_amt)=0 then 0
		else round((1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100,6) end between 88 and 99
order by Clown_Index desc
2025-01-24 海王:1小时发3个红包 
with t2 as (select t1.snd_usr_id,
 		t1.rcv_usr_id,
t1.pkt_amt,
t1.snd_datetime,
a2.cty as recipient_city,
first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime) as f_time,
timestampdiff(HOUR,first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime),t1.snd_datetime) as H
from tx_red_pkt_rcd t1 
inner join tx_usr_bas_info a1 on t1.snd_usr_id=a1.usr_id and a1.gdr='M'
inner join tx_usr_bas_info a2 on t1.rcv_usr_id=a2.usr_id and a2.gdr='f'
where substr(t1.snd_datetime,1,10)='2021-02-13'
order by t1.snd_usr_id,t1.snd_datetime) 
select snd_usr_id,rcv_usr_id,snd_datetime,pkt_amt,recipient_city
from (select *,
			count(rcv_usr_id) over(partition by snd_usr_id) as cnt
					from t2 
					where H in (0)
 )t3
where cnt>=3
2025-01-24 海王:1小时发3个红包 
with t2 as (select t1.snd_usr_id,
 		t1.rcv_usr_id,
t1.pkt_amt,
t1.snd_datetime,
a2.cty as recipient_city,
first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime) as f_time,
timestampdiff(HOUR,first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime),t1.snd_datetime) as H
from tx_red_pkt_rcd t1 
inner join tx_usr_bas_info a1 on t1.snd_usr_id=a1.usr_id and a1.gdr='M'
inner join tx_usr_bas_info a2 on t1.rcv_usr_id=a2.usr_id and a2.gdr='f'
where substr(t1.snd_datetime,1,10)='2021-02-13'
order by t1.snd_usr_id,t1.snd_datetime) 
select snd_usr_id,rcv_usr_id,snd_datetime,pkt_amt,recipient_city
from (select *,
			count(rcv_usr_id) over(partition by snd_usr_id) as cnt
					from t2 
					where H in (0,1)
 )t3
where cnt>=3
2025-01-24 快手面试真题(4)按购买金额统计用户数 
with t1 as (select substr(trx_time,1,7) as rx_month,
 							usr_id,
				sum(trx_amt) as sum_amt ,
 							case when sum(trx_amt)>=0 and sum(trx_amt)<=100 then '0-100'
		 				when sum(trx_amt)>100 and sum(trx_amt)<=1000 then '100-1k'
 				when sum(trx_amt)>1000 and sum(trx_amt)<=10000 then '1k-1w'
 				when sum(trx_amt)>10000 then '>1w'
 				end as amt_range
					from cmb_usr_trx_rcd
					group by 1,2
					order by 1,2)
select distinct rx_month,
			amt_range,
round(count(usr_id) over(partition by rx_month,amt_range)/count(usr_id) over(partition by rx_month)*100,2) as percentage
from t1
2025-01-24 快手面试真题(4)按购买金额统计用户数 
with t1 as (select substr(trx_time,1,7) as rx_month,
 							usr_id,
				sum(trx_amt) as sum_amt ,
 							case when sum(trx_amt)>=0 and sum(trx_amt)<=100 then '0-100'
		 				when sum(trx_amt)>100 and sum(trx_amt)<=1000 then '100-1k'
 				when sum(trx_amt)>1000 and sum(trx_amt)<=10000 then '1k-1w'
 				when sum(trx_amt)>10000 then '>1w'
 				end as amt_range
					from cmb_usr_trx_rcd
					group by 1,2
					order by 1,2)
select distinct rx_month,
			amt_range,
count(usr_id) over(partition by rx_month,amt_range) as cnt,
count(usr_id) over(partition by rx_month) as total,
round(count(usr_id) over(partition by rx_month,amt_range)/count(usr_id) over(partition by rx_month)*100,2) as p
from t1
2025-01-23 快手面试真题(4)按购买金额统计用户数 
select distinct t2.rx_month,
			t2.amt_range,
round(t2.cnt/t3.ttl*100,2) as percentage
from
(select rx_month,
			amt_range,
count(distinct usr_id) as cnt
from
(select substr(trx_time,1,7) as rx_month,
			usr_id,
			case 
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=0
			and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<100 then '0-100'
whensum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=100
 				 and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<1000 then '100-1k'
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=1000
			 and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<10000 then '1k-1w'
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd) t1
group by 1,2
order by 1,2) t2 
inner join 
(select substr(trx_time,1,7) as rx_month,
 count(distinct usr_id) as ttl 
from cmb_usr_trx_rcd
group by substr(trx_time,1,7)) t3 
on t2.rx_month=t3.rx_month
2025-01-23 快手面试真题(4)按购买金额统计用户数 
select t2.rx_month,
			t2.amt_range,
round(t2.cnt/t3.ttl*100,2) as percentage
from
(select rx_month,
			amt_range,
count(distinct usr_id) as cnt
from
(select substr(trx_time,1,7) as rx_month,
			usr_id,
			case 
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=0
			and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<100 then '0-100'
whensum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=100
 				 and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<1000 then '100-1k'
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=1000
			 and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<10000 then '1k-1w'
	when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd) t1
group by 1,2
order by 1,2) t2 
inner join 
(select substr(trx_time,1,7) as rx_month,
 count(distinct usr_id) as ttl 
from cmb_usr_trx_rcd
group by substr(trx_time,1,7)) t3 
on t2.rx_month=t3.rx_month
2025-01-23 只买iPhone的用户 
select user_id
from apple_pchs_rcd
group by user_id
having count(distinct case when product_type='iPhone' then product_type end)=1
and count(distinct case when product_type<>'iPhone' then product_type end)=0
order by user_id
2025-01-23 特定渠道的中档单价用户 
select *
 from apple_pchs_rcd
 where order_channel='官网' 
 			and payment_method='Apple Pay'
and product_price>=3000
order by order_id
2025-01-21 文科潜力股 
SELECT *
 FROM scores 
 where ((subject ='历史' and score>90)
 or (subject ='地理' and score>90)
 or (subject ='政治' and score>90))
 and exam_date='2024-06-30'
 order by score desc,student_id,subject
2025-01-21 文科潜力股 
SELECT *
 FROM scores 
 where ((subject ='历史' and score>90)
 or (subject ='地理' and score>90)
 or (subject ='政治' and score>90))
 and exam_date='2024-06-30'
 order by score desc,
 student_id,
 subject
2025-01-21 文科潜力股 
SELECT *
 FROM scores 
 where ((subject in ('历史') and score>90)
 or (subject in ('地理') and score>90)
 or (subject in ('政治') and score>90))
 and exam_date='2024-06-30'
 order by score desc,
 student_id,
 subject
2025-01-21 文科潜力股 
SELECT *
 FROM scores 
 where (subject in ('历史') and score>90)
 or (subject in ('地理') and score>90)
 or (subject in ('政治') and score>90)
 order by score desc,
 student_id,
 subject
2025-01-21 找出三个班级的女生 
select *
from students
where class_code in ('C219','C220','C221')
and gender='f'
order by student_id