排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-03-19 表连接（2）渣男去过我对象没去过，那就用LeftJoin  已解决
2025-03-19 字符串与通配符（2）好多关键词做规则，可以使用rlike  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-03-18 分类（1）姿势太多很过分，分类要用CaseWhen  
这闺蜜完蛋，想知道的这么具体
 
啥也没说
2025-03-18 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
with trx_summary as(
  select
    trx_amt,
    count(trx_amt) as total_trx_cnt,
    count(distinct(usr_id)) as unique_usr_cnt
  from cmb_usr_trx_rcd
  where mch_nm = '红玫瑰按摩保健休闲'
  and date(trx_time)between '2023-01-01' and '2024-06-30'
  group by trx_amt
  )
select
  trx_amt,
  total_trx_cnt,
  unique_usr_cnt,
  total_trx_cnt/unique_usr_cnt as avg_trx_per_user
from trx_summary
group by trx_amt
order by avg_trx_per_user desc
limit 5
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-03-26 10月1日后再也没活跃过的用户  
select count(distinct usr_id) as inactive_user_count
from
(select usr_id,max(login_time)
 from user_login_log
 group by usr_id
 having max(login_time)<='2024-10-01'
)tmp
2025-03-26 10月1日后再也没活跃过的用户  
select count(usr_id) as inactive_user_count
from
(select usr_id,max(login_time)
 from user_login_log
 group by usr_id
 having max(login_time)<='2024-10-01'
)tmp
2025-03-25 销售金额前10的商品信息（2）  
SELECT
order_date,
goods_id,
total_gmv,
ranking
FROM (
SELECT
DATE(order_time) AS order_date,
goods_id,
SUM(order_gmv) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY DATE(order_time)ORDER BY SUM(order_gmv) ASC
) AS ranking
FROM order_info
WHERE DATE(order_time) like '2024-10%'
GROUP BY DATE(order_time),goods_id
) t
WHERE ranking <= 3
ORDER BY order_date,ranking
2025-03-25 销售金额前10的商品信息（2）  
SELECT
order_date,
goods_id,
total_gmv,
ranking
FROM (
SELECT
DATE(order_time) AS order_date,
goods_id,
SUM(order_gmv) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY DATE(order_time)ORDER BY SUM(order_gmv) DESC
) AS ranking
FROM order_info
WHERE DATE(order_time) like '2024-10%'
GROUP BY DATE(order_time),goods_id
) t
WHERE ranking <= 3
ORDER BY order_date,ranking
2025-03-25 销售金额前10的商品信息（2）  
SELECT
order_date,
goods_id,
total_gmv,
ranking
FROM (
SELECT
DATE(order_time) AS order_date,
goods_id,
SUM(order_gmv) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY DATE(order_time)ORDER BY SUM(order_gmv) DESC
) AS ranking
FROM order_info
WHERE DATE(order_time) like '2024-10%'
GROUP BY DATE(order_time),goods_id
) t
WHERE ranking <= 3
ORDER BY order_date
2025-03-25 销售金额前10的商品信息（2）  
SELECT
order_date,
goods_id,
total_gmv,
ranking
FROM (
SELECT
DATE(order_time) AS order_date,
goods_id,
SUM(order_gmv) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY DATE(order_time)ORDER BY SUM(order_gmv) DESC
) AS ranking
FROM order_info
WHERE DATE(order_time) like '2024-10%'
GROUP BY DATE(order_time),goods_id
) t
WHERE ranking <= 3
ORDER BY order_date, ranking
2025-03-25 销售金额前10的商品信息（2）  
SELECT
order_date,
goods_id,
total_gmv,
ranking
FROM (
SELECT
DATE(order_time) AS order_date,
goods_id,
SUM(order_gmv) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY DATE(order_time)ORDER BY SUM(order_gmv) DESC
) AS ranking
FROM order_info
WHERE DATE(order_time) like '2024-10%'
GROUP BY DATE(order_time), goods_id
) t
WHERE ranking <= 3
ORDER BY order_date, ranking
2025-03-24 销售金额前10的商品信息  
select
goods_id,
sum(order_gmv) as total_gmv
from order_info
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc
limit 10
2025-03-24 销售金额前10的商品信息  
select
goods_id,
sum(order_gmv) as total_gmv
from order_info
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv
limit 10
2025-03-24 销售金额前10的商品信息  
select
goods_id as oods_id,
sum(order_gmv) as total_gmv
from order_info
where date(order_time)='2024-09-10'
group by oods_id
order by total_gmv
limit 10
2025-03-19 表连接（3）一直使用一张表，现在开始两张表  
select 
mch_typ,
count(mch_nm) as total_mch,
count(distinct mch_nm) as unique_mch_cnt
from cmb_mch_typ  
group by mch_typ
order by total_mch desc
2025-03-19 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select 
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select mch_nm,
count(trx_amt) as trx_cnt
from cmb_usr_trx_rcd
where 
year(trx_time)in(2023,2024)
and usr_id='5201314520'
group by mch_nm
having trx_cnt>=20)a
left join 
(select distinct mch_nm
 from cmb_usr_trx_rcd
 where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm
order by trx_cnt desc
2025-03-19 表连接（1）你们难道都去过？那就试试用InnerJoin  
select a.*
from(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521')a
inner join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5201314520' )b
on a.mch_nm = b.mch_nm
order by 1 desc
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and ((truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
or 
upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA')
group by trx_mon
order by trx_mon
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and ((truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2))
or 
upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>='2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and (truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
or 
upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and (truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
or 
upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and (truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
or 
upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon ASC
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and (truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
or upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon ASC
2025-03-19 小结（2）越花越多是死罪，按月统计Substr  
select
substr(trx_time,1,7) as trx_mon,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and date(trx_time)>'2022-11-01'and date(trx_time)<='2024-12-31'
and trx_amt>=200
and (truncate(trx_amt,0) like '%88'or truncate(trx_amt,0) like '%98')
and hour(trx_time)in (0,23,1,2)
orupper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
group by trx_mon
order by trx_mon ASC