排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-23 分组与聚合函数(2)擦边营收怎么样,聚合函数可看出 
select 
	DATE_FORMAT(trx_time, '%Y-%m-%d') as trx_date,
	max(trx_amt) as max_trx_amt,
    min(trx_amt) as min_trx_amt,	
    avg(trx_amt) as avg_trx_amt,	
    sum(trx_amt) as total_trx_amt
from 
	cmb_usr_trx_rcd
where
	DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30'
and 
	mch_nm = '红玫瑰按摩保健休闲'    
group by DATE_FORMAT(trx_time, '%Y-%m-%d')
order by trx_date这个错在哪
已修正这个问题,感谢指正。请联系微信【sqlboy-2021】 获赠月度会员!
2024-12-23 分组与聚合函数(2)擦边营收怎么样,聚合函数可看出 
select 
	DATE_FORMAT(trx_time, '%Y-%m-%d') as trx_date,
	max(trx_amt) as max_trx_amt,
    min(trx_amt) as min_trx_amt,	
    avg(trx_amt) as avg_trx_amt,	
    sum(trx_amt) as total_trx_amt
from 
	cmb_usr_trx_rcd
where
	DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30'
and 
	mch_nm = '红玫瑰按摩保健休闲'    
group by DATE_FORMAT(trx_time, '%Y-%m-%d')
order by trx_date
请问这个错在哪
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-01-08 德州扑克起手牌-同花 
select id,card1,card2
from hand_permutations
where right(card1,1) = right(card2,1)
2025-01-08 德州扑克起手牌- 手对 
select id,card1,card2
from hand_permutations
where left(card1,1) = left(card2,1)
2025-01-08 德州扑克起手牌- A花 
select *
from hand_permutations
where right(card1,1) = right(card2,1)
and (left(card1,1) = 'A' OR left(card2,1) = 'A')
2024-12-31 德州扑克起手牌- A花 
select id,card1,card2
from hand_permutations
where (left(card1,1) = 'A' and right(card1,1)='♠') or (left (card2,1) = 'A' and right(card2,1)='♠')
2024-12-31 德州扑克起手牌- A花 
select id,card1,card2
from hand_permutations
where left(card1,1) = 'A' or left (card2,1) = 'A'
2024-12-31 德州扑克起手牌-最强起手牌KK+ 
select id,card1,card2
from hand_permutations
where (left(card1,1) = 'A' OR left(card1,1) = 'K') AND (left(card2,1) = 'A' OR left(card2,1) = 'K')
order by id asc
2024-12-31 德州扑克起手牌-最强起手牌KK+ 
select id,card1,card2
from hand_permutations
where((right(card1,1) = '♠️' or right(card1,1) = '♥') and (right(card2,1) = '♠️' or right(card2,1) = '♥')) and (left(card1,1) = 'A' OR left(card1,1) = 'K') AND (left(card2,1) = 'A' OR left(card2,1) = 'K')
order by id asc
2024-12-31 德州扑克起手牌-最强起手牌KK+ 
select id,card1,card2
from hand_permutations
where(right(card1,1) = '♠️' or right(card1,1) = '♥') and (right(card2,1) = '♠️' or right(card2,1) = '♥')
order by id asc
2024-12-31 德州扑克起手牌-最强起手牌KK+ 
select id,card1,card2
from hand_permutations
where left(card1,1) != left(card2,1)
or right(card1,1) != right(card2,1)
order by id desc
2024-12-31 抖音面试真题(1)T+1日留存率 
with data1 as (
select 
distinct usr_id,
date(login_time) as login_time
from user_login_log
where datediff(current_date,date(login_time)) <= 30
),
data2 as (
select 
t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from 
data1 as t 
left join 
data1 as t1
on 
t.usr_id = t1.usr_id
and datediff(t1.login_time,t.login_time) = 1
)
select 
t_date as first_login_date,
concat(round(avg(t1_date is not null)*100, 2), '%') as t1_retention_rate
from 
data2
group by 
t_date
order by 
t_date
2024-12-31 抖音面试真题(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(CASE WHEN T_1_date IS NOT NULL THEN 1 ELSE 0 END) * 100, 2), '%') AS T1_retention_rate
FROM
data2
GROUP BY
T_date
ORDER BY
T_date;
2024-12-31 抖音面试真题(1)T+1日留存率 
with data1 as (
select usr_id,date(login_time) as login_time
from user_login_log
),
data2 as (
select t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from data1 as t 
left join 
data1 as t1
on t.usr_id = t1.usr_id
where datediff(t.login_time,t1.login_time)= -1
)
select 
t_date,
concat(round(avg(t1_date is not null)*100, 2),'%') as t1_retention_rate
from data2
group by t_date
order by t_date
2024-12-24 抖音面试真题(1)T+1日留存率 
with data1 as (
select distinct usr_id ,date(login_time) as login_time
from user_login_log
where datediff(current_date,date(login_time)) <= 30),
data2 as (
select t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from data1 as t 
left join
data1 as t1
on t.usr_id = t1.usr_id
and datediff(t.login_time,t1.login_time) = -1
)
select t_date as login_date,
concat(round(avg(t1_date is not null)*100,2),'%') as t1_retention_rate
from data2
group by t_date
order by t_date
2024-12-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
2024-12-24 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select a.mch_nm as asshole_tried,a.trx_cnt,b.mch_nm as darling_tried
from 
(selectmch_nm ,count(1) as trx_cnt
 from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in (2023,2024)
group by mch_nm
having count(1) >= 20) a
left join
(
select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) in (2023,2024)
) b
on a.mch_nm = b.mch_nm
order by 2 desc
2024-12-24 表连接(1)你们难道都去过?那就试试用InnerJoin 
select a.*
from (
select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5201314520'and year(trx_time) = 2024
) as a
inner join 
(select distinct mch_nm 
from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) = 2024) b
on a.mch_nm = b.mch_nm
order by mch_nm desc
2024-12-24 表连接(1)你们难道都去过?那就试试用InnerJoin 
select a.*
from 
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id= 5201314520 and year(trx_time) = 2024) as a 
left join 
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id= 5201314521 and year(trx_time) = 2024) as b
on a.mch_nm = b.mch_nm
order by mch_nm desc
2024-12-24 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select *
from cmb_usr_trx_rcd
where 
trx_amt = (select trx_amt
from cmb_usr_trx_rcd 
where usr_id = 5201314520
and year(trx_time) = 2024
group by trx_amt
order by max(trx_amt) desc
limit 1)
2024-12-24 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select *
from cmb_usr_trx_rcd
where mch_nm = ( 
	select mch_nm
from cmb_usr_trx_rcd 
where usr_id = 5201314520
and year(trx_time) = 2024
group by mch_nm
order by max(trx_amt) desc
limit 1)
and trx_amt = (select trx_amt
from cmb_usr_trx_rcd 
where usr_id = 5201314520
and year(trx_time) = 2024
group by trx_amt
order by max(trx_amt) desc
limit 1)
2024-12-24 字符串与通配符(2)好多关键词做规则,可以使用rlike 
select 
	case 
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(mch_nm) rlike'.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;