右下角图片

排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-25 Halo出行-通勤活跃用户标签开发  未解决
2024-11-04 分类别的最火直播间  未解决
2024-10-29 条件过滤(3)Hour函数很给力,组合条件要仔细  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-25 小宇宙电台的同期群分析 
答案在哪看?
我发你。
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
题解的查询结果包含2024-Q4,题干要求是2023Q1-至今的
没毛病,😁,今天11月21号,也是Q4,这里不限制时间,所以是取最新啦。
2024-11-18 找出所有类别组合的最热门路线 
光星电子有限公司----流花山公园南门,光星电子有限公司----欢乐谷,都是企业到公园。同一类别trip_count都是8,并列第一,是不是应该用denserank。我用denserank报错来着
都是取第一,rank、dense_rank一样了。感谢抓虫
2024-11-07 时间日期(5)三腿爱往会所走,全当良心喂了狗 
要求计算在一起多少小时,为什么不是用'2022-10-03'和now(),current_date计算时间差,而是min()与now()和current_date?
有道理,已修正。重新提交就可以了
2024-11-05 表连接(5)哪些没被分出来,用左用内你来猜 
is null 3Q
如果有学到东西,请多多推荐哦!
开发题目不易,如果有纰漏的地方,也欢迎继续评论。感谢!😍
2024-11-04 表连接(5)哪些没被分出来,用左用内你来猜 
坑在哪???????
细品,啥叫没被分出来。where后面是不是还少了个过滤条件呢?

提交记录

提交日期 题目名称 提交代码
2024-11-25 收到520红包用户的平均年龄 
select 
round(avg(datediff('2021-02-13',bth_dt)/365),2) as avg_age
from tx_red_pkt_rcd as r 
join tx_usr_bas_info as i
onr.rcv_usr_id=i.usr_id
where pkt_amt=520.00
and date(rcv_datetime)='2021-02-13'
2024-11-25 收到520红包用户的平均年龄 
select 
round(avg(datediff(now(),bth_dt)/365),2) as avg_age
from tx_red_pkt_rcd as r 
join tx_usr_bas_info as i
onr.snd_usr_id=i.usr_id
where pkt_amt=520
and date(snd_datetime)='2021-02-13'
2024-11-25 最受新一线城市欢迎的红包金额 
with a as(
select snd_usr_id
	 ,pkt_amt
 ,snd_datetime
 ,i.cty
 ,m.cty_cls
from 
		tx_red_pkt_rcd as r 
join
		tx_usr_bas_info as i
on
		r.snd_usr_id=i.usr_id
join
		tx_cty_map as m
on
		m.cty=i.cty
where 
		date(snd_datetime)='2021-02-13'
and 	
		m.cty_cls='新一线')
select pkt_amt,count(*) as send_count from a 
group by 1
order by 2 desc
limit 1
2024-11-25 互相发过红包的好友关系对 
select distinct r1.snd_usr_id as user_a
	,r1.rcv_usr_id as user_b
from tx_red_pkt_rcd as r1
join tx_red_pkt_rcd as r2
on r1.snd_usr_id=r2.rcv_usr_id 
and r1.rcv_usr_id=r2.snd_usr_id
where date(r1.snd_datetime)='2021-02-13'
and date(r2.snd_datetime)='2021-02-13'
and r1.snd_usr_id <r1.rcv_usr_id
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm = '红玫瑰按摩保健休闲'
and usr_id=5201314520
order by 1)
selectdistinct trx_quarter,
 count(case when trx_amt=288 then trx_amt else null end)over(order by 				 trx_quarter) as WithHand,
 count(case when trx_amt=888 then trx_amt else null end)over(order by 	 				trx_quarter) as Doi
from a 
order by 1
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
with quarterly_transactions as (
    select
        *,
        concat(year(trx_time), '-', 'Q', quarter(trx_time)) as trx_quarter, 
        count(case when trx_amt = 288 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as withhand, 
        count(case when trx_amt = 888 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as doi 
    from
        cmb_usr_trx_rcd
    where
        usr_id = 5201314520  and mch_nm='红玫瑰按摩保健休闲'
)
select
    trx_quarter,
    withhand,
    doi
from
    quarterly_transactions
group by
    trx_quarter,
    withhand,
    doi 
order by
    trx_quarter;
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm like '%红玫瑰%'
and usr_id=5201314520
order by 1)
selectdistinct trx_quarter,
 count(case when trx_amt=288 then trx_amt else null end)over(order by 				 trx_quarter) as WithHand,
 count(case when trx_amt=888 then trx_amt else null end)over(order by 	 				trx_quarter) as Doi
from a 
order by 1
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm like '%红玫瑰%'
and usr_id=5201314520
order by 1),
b as (select trx_quarter,
 count(case when trx_amt=288 then trx_amt else null end)over(order by 				 trx_quarter) as WithHand,
 count(case when trx_amt=888 then trx_amt else null end)over(order by 	 				trx_quarter) as Doi
from a 
order by 1)
select trx_quarter
	,sum(WithHand) WithHand
,sum(Doi)Doi
from b
group by 1
order by 1
2024-11-20 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
with a as(
select date_format(date_value,'%Y-%m') as trx_mon
from date_table
where year(date_value)=2023
and month(date_value) between 1 and 12
group by 1
 ),
b as (
select date_format(trx_time,'%Y-%m') as trx_mon
 ,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as r 
join cmb_mch_typ as t on r.mch_nm=t.mch_nm
where usr_id=5201314520
and year(trx_time)=2023
and mch_typ='休闲娱乐'
group by 1
order by 1
)
select a.trx_mon
 ,sum(trx_amt)over(order by a.trx_mon ) as trx_amt
from a 
left join b
on a.trx_mon=b.trx_mon
2024-11-20 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
with month_sequence as (
    select 
        date_format(dv.date_value, '%Y-%m') as trx_mon
    from 
        date_table dv
    where 
        year(dv.date_value) = 2023
        and month(dv.date_value) between 1 and 12
    group by date_format(dv.date_value, '%Y-%m')
),
monthly_trx as (
    select 
        date_format(t.trx_time, '%Y-%m') as trx_mon, 
        sum(t.trx_amt) as monthly_trx_amt
    from 
        cmb_usr_trx_rcd t
    join 
        cmb_mch_typ m on t.mch_nm = m.mch_nm
    where 
        t.usr_id = 5201314520 
        and m.mch_typ = '休闲娱乐'
        and year(t.trx_time) = 2023
    group by 
        trx_mon
),
combined_trx as (
    select 
        ms.trx_mon, 
        coalesce(mt.monthly_trx_amt, 0) as monthly_trx_amt
    from 
        month_sequence ms
    left join 
        monthly_trx mt on ms.trx_mon = mt.trx_mon
),
cumulative_trx as (
    select 
        trx_mon, 
        monthly_trx_amt,
        sum(monthly_trx_amt) over (order by trx_mon) as cumulative_trx_amt
    from 
        combined_trx
)
select 
    trx_mon, 
    cumulative_trx_amt as trx_amt
from 
    cumulative_trx
order by 
    trx_mon;
2024-11-20 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select 
    a.trx_mon, 
    ifnull(b.last_day, '1900-01-01') as last_day, 
    ifnull(b.day_of_mon, 0) as day_of_mon, 
    ifnull(b.trx_amt, 0) as trx_amt, 
    ifnull(b.trx_cnt, 0) as trx_cnt, 
    ifnull(round(b.avg_day_amt, 2), 0) as avg_day_amt,
    ifnull(round(b.avg_day_cnt, 2), 0) as avg_day_cnt 
from 
(select distinct date_format(date_value, '%Y-%m') as trx_mon
     from date_table
     where date_format(date_value, '%Y-%m') between '2023-01' and '2024-06'
    ) a
LEFT JOIN
(SELECT 
trx_mon,
last_day,
day_of_mon,
SUM(trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(trx_amt) / day_of_mon AS avg_day_amt,
COUNT(*) / day_of_mon AS avg_day_cnt
FROM (
SELECT 
r.trx_time,
DATE_FORMAT(r.trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(r.trx_time) AS last_day,
DAY(LAST_DAY(r.trx_time)) AS day_of_mon,
r.trx_amt
FROM 
cmb_usr_trx_rcd AS r
LEFT JOIN 
cmb_mch_typ AS t 
ON 
r.mch_nm = t.mch_nm
WHERE 
r.usr_id = 5201314520
AND (trx_time BETWEEN '2023-01-01' AND '2024-06-30')
AND HOUR(r.trx_time) IN (23, 0, 1, 2)
AND (t.mch_typ = '休闲娱乐' OR t.mch_typ IS NULL)
AND r.trx_amt >= 288
) AS subquery
GROUP BY 
trx_mon, last_day, day_of_mon
ORDER BY 
trx_mon) as b
on a.trx_mon=b.trx_mon
order by 1
2024-11-20 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select a.trx_mon, 
	coalesce(d.last_day, '1900-01-01') last_day, 
coalesce(d.days_of_mon, 0) day_of_mon, 
coalesce(d.trx_amt, 0) trx_amt, 
coalesce(d.trx_cnt, 0) trx_cnt,
	coalesce(round(d.avg_day_amt, 2), 0) avg_day_amt,
coalesce(round(d.avg_day_cnt, 2), 0) avg_day_cnt
from 
(select distinct left(trx_time, 7) trx_mon from cmb_usr_trx_rcd
where trx_time rlike '(2023|2024-0[1-6]).*'
order by trx_mon) a
left join
(select left(b.trx_time, 7) trx_mon,
last_day(max(b.trx_time)) last_day,
day(last_day(max(b.trx_time))) days_of_mon,
sum(b.trx_amt) trx_amt,
count(*) trx_cnt,
sum(b.trx_amt) / day(last_day(max(b.trx_time))) avg_day_amt,
count(*) / day(last_day(max(b.trx_time))) avg_day_cnt
from cmb_usr_trx_rcd b
left join cmb_mch_typ c on b.mch_nm = c.mch_nm
where b.usr_id = 5201314520 and b.trx_amt >= 288
and b.trx_time rlike '(2023|2024-0[1-6]).*'
and hour(b.trx_time) in (0, 1, 2, 23)
and (c.mch_typ is null or c.mch_typ = '休闲娱乐')
group by trx_mon order by trx_mon
) d
on a.trx_mon = d.trx_mon
order by a.trx_mon
2024-11-20 热门搜索关键词 
select key_word,count(*) as search_count
from jx_query_rcd 
group by 1
order by 2 desc
limit 5
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round((repeat_users/nullif(total_users,0))*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/nullif(total_users,0)*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
2024-11-20 二级菜系复购率 
WITH user_transactions AS (
    SELECT 
        mch_typ2,
        cust_uid,
        DATE_FORMAT(trx_dt, '%Y') AS year
    FROM mt_trx_rcd1
),
user_transaction_counts AS (
    SELECT 
        mch_typ2,
        year,
        cust_uid,
        COUNT(*) AS transaction_count
    FROM user_transactions
    GROUP BY mch_typ2, year, cust_uid
),
total_users AS (
    SELECT 
        mch_typ2,
        year,
        COUNT(DISTINCT cust_uid) AS total_users
    FROM user_transactions
    GROUP BY mch_typ2, year
),
repeat_users AS (
    SELECT 
        mch_typ2,
        year,
        COUNT(cust_uid) AS repeat_users
    FROM user_transaction_counts
    WHERE transaction_count >= 2
    GROUP BY mch_typ2, year
)
SELECT 
    tu.mch_typ2,
    tu.year,
    tu.total_users,
    ru.repeat_users,
    ROUND((ru.repeat_users / NULLIF(tu.total_users, 0)) * 100, 2) AS repurchase_rate
FROM total_users tu
LEFT JOIN repeat_users ru ON tu.mch_typ2 = ru.mch_typ2 AND tu.year = ru.year
ORDER BY tu.mch_typ2, tu.year;
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
left join b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a 
left join bon a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
2024-11-20 二级菜系复购率 
with a as (
select mch_typ2
 ,year
 ,count(cnt) as repeat_users
from (
select mch_typ2
 ,year(trx_dt) as year
 ,cust_uid
 ,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t 
group by 1,2
 ),
b as (
select mch_typ2
 ,year(trx_dt) as year
 ,count(distinct cust_uid) as total_users
from mt_trx_rcd1 
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a 
join bon a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2