排名

用户解题统计

过去一年提交了

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

收藏

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-02-06 十大恩客你排第一,给钱金主数你多 
在2022-2023两年期间,谁在红浪漫消费的金额最多?

输出客户id、总金额,取前5行。
题目要的跟代码给的不一样啊
改了改了😁
2025-02-03 时间日期(1)按月统计日花费,一天都不要浪费 
select
	substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7) as trx_mon
    ,max(last_day(trx_time)) as last_day
from
	cmb_usr_trx_rcd
where
	year(trx_time) in (2023,2024)
group by
	substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7)
order by
	trx_mon
我想问一个语法方面的问题,就是有了group分组之后,是不是输出的列也是要聚合好的。我一开始lastday那里没有加max,就是无法输出。
select a,b from t group by a会报错的;select a, max(b) from t group by a不会报错。你的语句不加max,就是犯了第一种错
2025-01-31 输出特定日期上市的银行 
select
	*
from
	stock_info
where 
	 (list_date between '2026-06-01' and '2006-09-01')
     and industry ='银行'
     and area='北京'
order by
	list_date
不懂为啥没有输出
你写成2026了哥哥
2024-12-26 小结(1)大数据早就能扫黄,找足证据不慌张 
明白了,between 2 and4 里面只有2、3
between 2 and 5 里面有 2、3、4
别闹。。 between and 是前闭后闭的 234;2345
2024-12-26 小结(1)大数据早就能扫黄,找足证据不慌张 
第一种跟第二种输出答案不一样
只有一个答案是正确的
2024-12-24 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
select
	trx_amt
	,count(trx_amt) as total_trx_cnt
    ,count(distinct usr_id) as unique_usr_cnt
    ,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'
	and year(trx_time)=2023 and month(trx_time) in (1,2,3,4,5,6,7,8,9,10,11,12)
	or year(trx_time)=2024 and month(trx_time) in (1,2,3,4,5,6)
group by trx_amt
order by avg_trx_per_user desc
limit 5

我输出的结果中total_trx_cnt 数量不对,288.888,1288都多了两个
啥也没说
2024-12-23 分组与聚合函数(2)擦边营收怎么样,聚合函数可看出 
我想问问为什么selet中trx_time不能用date_format(trx_time,%y-%m-%d) as trx_date
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-02-06 十大恩客你排第一,给钱金主数你多 
select
    usr_id
    , sum(trx_amt) as sum_trx_amt
from 
    cmb_usr_trx_rcd
where 
    mch_nm = '红玫瑰按摩保健休闲'
    and year(trx_time) between 2023 and 2024
group by usr_id
order by 2 desc
limit 10
2025-02-06 十大恩客你排第一,给钱金主数你多 
select
	usr_id
,sum(trx_amt)
from
	cmb_usr_trx_rcd
where
	mch_nm='红玫瑰按摩保健休闲'
and year(trx_time) in (2022,2023)
group by
	usr_id
order by
	sum(trx_amt) desc
limit
	5
2025-02-06 年花万元逛窑子,真他妈是个败家子(2) 
SELECT
YEAR(trx_time) AS Y,
month(trx_time) AS m,
SUM(trx_amt) AS sum_trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) IN (2022, 2023, 2024)
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY
YEAR(trx_time),
month(trx_time)
ORDER BY
YEAR(trx_time),
month(trx_time)
2025-02-06 年花万元逛窑子,真他妈是个败家子(2) 
SELECT
YEAR(trx_time) AS Y,
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2) AS m,
SUM(trx_amt) AS sum_trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) IN (2022, 2023, 2024)
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY
YEAR(trx_time),
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2)
ORDER BY
YEAR(trx_time),
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2);
2025-02-06 年花万元逛窑子,真他妈是个败家子(1) 
select
	year(trx_time) as Y
,sum(trx_amt) as sum_trx_amt
from
	cmb_usr_trx_rcd
where
	usr_id=5201314520
and year(trx_time) in (2022,2023,2024)
and mch_nm ='红玫瑰按摩保健休闲'
group by
	year(trx_time)
order by
	1
2025-02-06 想念她的味道,工资献给98号 
select
	*
from	
	cmb_usr_trx_rcd
where
	usr_id=5201314520
and year(trx_time) in (2022,2023,2024)
and day(trx_time) in (28,29,30,31,1)
and trx_amt >300
order by
	trx_time
2025-02-06 与X轴有且只有一个交点的一元二次函数 
select
	*
from
	numbers_for_fun
where
	b*b-4*a*c=0
and a!=0
2025-02-06 开口向上且经过原点的一元二次函数 
select
	*
from
	numbers_for_fun
where
	a>0
and c=0
2025-02-06 开口向上的一元二次函数 
select
 	*
 from
 	numbers_for_fun
 where
 	a>0
2025-02-06 必过(-1, 0)的一元一次函数 
select
	*
from
	numbers_for_fun
where
	a=0
and c-b=0
and b!=0
2025-02-03 双脚踏进足浴门,从此再无心上人 
select
	*
from
	cmb_usr_trx_rcd
where
	date_format(trx_time,'%Y-%m-%d') between '2024-06-08' and '2024-06-10'
and 
	hour(trx_time) in (11,12,18,19)
and 
	mch_nm='红玫瑰按摩保健休闲'
2025-02-03 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
WITH monthly_trx AS (
SELECT
t1.trx_mon,
COALESCE(t2.trx_amt, 0) AS monthly_trx_amt
FROM
(
SELECT
DISTINCT DATE_FORMAT(date_value, '%Y-%m') AS trx_mon
FROM
date_table
WHERE
YEAR(date_value) = 2023
) AS t1
LEFT JOIN
(
SELECT
DATE_FORMAT(a.trx_time, '%Y-%m') AS trx_mon,
SUM(a.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd AS a
JOIN cmb_mch_typ AS b ON a.mch_nm = b.mch_nm
WHERE
a.usr_id = 5201314520
AND b.mch_typ = '休闲娱乐'
AND YEAR(a.trx_time) = 2023
GROUP BY
DATE_FORMAT(a.trx_time, '%Y-%m')
) AS t2
ON t1.trx_mon = t2.trx_mon
),
cumulative_month AS (
SELECT
trx_mon,
monthly_trx_amt,
SUM(monthly_trx_amt) OVER (ORDER BY trx_mon) AS cumulative_trx_amt
FROM
monthly_trx
)
SELECT
trx_mon,
cumulative_trx_amt AS trx_amt
FROM
cumulative_month
ORDER BY
trx_mon;
2025-02-03 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
WITH monthly_trx AS (
SELECT
DATE_FORMAT(t1.trx_time, '%Y-%m') AS trx_mon,
SUM(t1.trx_amt) AS monthly_trx_amt
FROM
cmb_usr_trx_rcd AS t1
JOIN
cmb_mch_typ AS t2 ON t1.mch_nm = t2.mch_nm
WHERE
t1.usr_id = 5201314520
AND t2.mch_typ = '休闲娱乐'
AND DATE_FORMAT(t1.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-12'
GROUP BY
DATE_FORMAT(t1.trx_time, '%Y-%m')
),
cumulative_trx AS (
SELECT
trx_mon,
monthly_trx_amt,
SUM(monthly_trx_amt) OVER (ORDER BY trx_mon) AS cumulative_trx_amt
FROM
monthly_trx
)
SELECT
trx_mon,
cumulative_trx_amt AS trx_amt
FROM
cumulative_trx
ORDER BY
trx_mon;
2025-02-03 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
select
	distinct date_format(trx_time,'%Y-%m') astrx_mon
,sum(t1.trx_amt)over(partition by date_format(trx_time,'%Y-%m') ) as trx_amt
from
	cmb_usr_trx_rcd as t1
join
cmb_mch_typ as t2
on t1.mch_nm=t2.mch_nm
where
	t1.usr_id=5201314520
and 
	t2.mch_typ='休闲娱乐'
and 
	date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
2025-02-03 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
select
	date_format(trx_time,'%Y-%m') astrx_mon
,sum(t1.trx_amt)over(partition by date_format(trx_time,'%Y-%m') ) as trx_amt
from
	cmb_usr_trx_rcd as t1
join
cmb_mch_typ as t2
on t1.mch_nm=t2.mch_nm
where
	t1.usr_id=5201314520
and 
	t2.mch_typ='休闲娱乐'
and 
	date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
2025-02-03 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with
	t1 as 
(
	select
		'all' as mch_typ
		,mch_nm
		,count(trx_time) as trx_cnt
		,dense_rank()over(order by count(trx_time) desc) as rnk
	from
		cmb_usr_trx_rcd
	where
		usr_id=5201314520
	group by
		mch_nm
),
t2 as 
(
	select
		s2.mch_typ
		,s1.mch_nm
		,count(s1.trx_time) as trx_cnt
		,dense_rank()over(partition by s2.mch_typ order by count(s1.trx_time)desc) as rnk
	from
		cmb_usr_trx_rcd as s1
		inner join
		cmb_mch_typ as s2
		on s1.mch_nm=s2.mch_nm
 	where
		s1.usr_id=5201314520
		and 
			s2.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
	group by
		s2.mch_typ
		,s1.mch_nm
),
filtered_t2 as
	(
	select
		mch_typ
		,mch_nm
		,trx_cnt
		,rnk
	from
		t2
	where
		rnk<=1
),
filtered_t1 as 
	(
	select
		mch_typ
		,mch_nm
		,trx_cnt
		,rnk
	from
		t1
	where
		rnk<=1
)
select
	mch_typ
,mch_nm
,trx_cnt
,rnk
from
	filtered_t1
union all
select
	mch_typ
,mch_nm
,trx_cnt
,rnk
from
	filtered_t2
order by
	mch_typ,
rnk,
mch_nm,
trx_cnt
2025-02-03 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with
	t1 as 
(
	select
		'all' as mch_typ
		,mch_nm
		,count(trx_time) as trx_cnt
		,dense_rank()over(order by count(trx_time) desc) as rnk
	from
		cmb_usr_trx_rcd
	where
		usr_id=5201314520
	group by
		mch_nm
),
t2 as 
(
	select
		s2.mch_typ
		,s1.mch_nm
		,count(s1.trx_time) as trx_cnt
		,dense_rank()over(partition by s2.mch_typ order by count(s1.trx_time)desc) as rnk
	from
		cmb_usr_trx_rcd as s1
		left join
		cmb_mch_typ as s2
		on s1.mch_nm=s2.mch_nm
 	where
		s1.usr_id=5201314520
		and 
			s2.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
	group by
		s2.mch_typ
		,s1.mch_nm
),
flitered_t2 as
	(
	select
		mch_typ
		,mch_nm
		,trx_cnt
		,rnk
	from
		t2
	where
		rnk<=1
),
filtered_t1 as 
	(
	select
		mch_typ
		,mch_nm
		,trx_cnt
		,rnk
	from
		t1
	where
		rnk<=1
)
select
	mch_typ
,mch_nm
,trx_cnt
,rnk
from
	t1
union all
select
	mch_typ
,mch_nm
,trx_cnt
,rnk
from
	t2
order by
	mch_typ,
rnk,
mch_nm,
trx_cnt
2025-02-03 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
with all_merchants_top_2 as (
    select 
        'all' as mch_typ, 
        mch_nm, 
        count(*) as trx_cnt,
        dense_rank() over (order by count(*) desc) as rnk
    from 
        cmb_usr_trx_rcd
    where 
        usr_id = 5201314520
    group by 
        mch_nm
),
category_merchants_top_2 as (
    select 
        m.mch_typ, 
        m.mch_nm, 
        count(t.trx_amt) as trx_cnt,
        dense_rank() over (partition by m.mch_typ order by count(t.trx_amt) desc) as rnk
    from 
        cmb_mch_typ m
    join 
        cmb_usr_trx_rcd t on m.mch_nm = t.mch_nm
    where 
        t.usr_id = 5201314520 and m.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
    group by 
        m.mch_typ, m.mch_nm
),
filtered_category_merchants_top_2 as (
    select 
        mch_typ, 
        mch_nm, 
        trx_cnt,
        rnk
    from 
        category_merchants_top_2
    where 
        rnk <= 1
),
filtered_all_merchants_top_2 as (
    select 
        mch_typ, 
        mch_nm, 
        trx_cnt,
        rnk
    from 
        all_merchants_top_2
    where 
        rnk <= 1
)
select 
    mch_typ, 
    mch_nm, 
    trx_cnt,
    rnk
from 
    filtered_all_merchants_top_2
union all
select 
    mch_typ, 
    mch_nm, 
    trx_cnt,
    rnk
from 
    filtered_category_merchants_top_2
order by 
    mch_typ, 
    rnk,
    mch_nm,trx_cnt
2025-02-03 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select
    t1.trx_mon,
    COALESCE(t4.last_day, '1900-01-01') AS last_day,
    COALESCE(t4.day_of_mon, 0) AS day_of_mon, 
    COALESCE(t4.trx_amt, 0) AS trx_amt, 
    COALESCE(t4.trx_cnt, 0) AS trx_cnt, 
    COALESCE(ROUND(t4.avg_day_amt, 2), 0) AS avg_day_amt, 
    COALESCE(ROUND(t4.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'
) as t1
left join
(
select
	date_format(t2.trx_time,'%Y-%m') as trx_mon
	,last_day(max(t2.trx_time)) as last_day
	,day(last_day(max(t2.trx_time))) as day_of_mon
	,sum(t2.trx_amt) as trx_amt
	,count(*) as trx_cnt
	,sum(t2.trx_amt)/ day(last_day(max(t2.trx_time))) as avg_day_amt
	,count(*)/day(last_day(max(t2.trx_time))) as avg_day_cnt
from
	cmb_usr_trx_rcd as t2
	left join
	cmb_mch_typ as t3
	on t2.mch_nm=t3.mch_nm
where
		date_format(t2.trx_time,'%Y-%m') between '2023-01' and '2024-06'
	and	
		t2.usr_id=5201314520
	and 
		t2.trx_amt>=288
	and 
		hour(t2.trx_time) in (23,0,1,2) 
	and 
		(t3.mch_typ='休闲娱乐' or t3.mch_typ is null)
	group by
		date_format(t2.trx_time,'%Y-%m')
	order by
		1
) as t4
on t1.trx_mon=t4.trx_mon
order by
	t1.trx_mon
2025-02-03 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select
    t1.trx_mon,
    COALESCE(t4.last_day, '1900-01-01') AS last_day,
    COALESCE(t4.day_of_mon, 0) AS day_of_mon, 
    COALESCE(t4.trx_amt, 0) AS trx_amt, 
    COALESCE(t4.trx_cnt, 0) AS trx_cnt, 
    COALESCE(ROUND(t4.avg_day_amt, 2), 0) AS avg_day_amt, 
    COALESCE(ROUND(t4.avg_day_cnt, 2), 0) AS avg_day_cnt
from
	(
select
	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'
) as t1
left join
(
select
	date_format(t2.trx_time,'%Y-%m') as trx_mon
	,last_day(max(t2.trx_time)) as last_day
	,day(last_day(max(t2.trx_time))) as day_of_mon
	,sum(t2.trx_amt) as trx_amt
	,count(*) as trx_cnt
	,sum(t2.trx_amt)/ day(last_day(max(t2.trx_time))) as avg_day_amt
	,count(*)/day(last_day(max(t2.trx_time))) as avg_day_cnt
from
	cmb_usr_trx_rcd as t2
	left join
	cmb_mch_typ as t3
	on t2.mch_nm=t3.mch_nm
where
		date_format(t2.trx_time,'%Y-%m') between '2023-01' and '2024-06'
	and	
		t2.usr_id=5201314520
	and 
		t2.trx_amt>=288
	and 
		hour(t2.trx_time) in (23,0,1,2) 
	and 
		(t3.mch_typ='休闲娱乐' or t3.mch_typ is null)
	group by
		date_format(t2.trx_time,'%Y-%m')
	order by
		1
) as t4
on t1.trx_mon=t4.trx_mon
order by
	t1.trx_mon