排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-26 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
题解中的"INTERVAL 3 minute"错了,应为“INTERVAL 2 hour”
啥也没说
2024-11-26 时间日期(3)按月统计日花费,一天都不要浪费 
“day(last_day(max(trx_time))) as days_of_mon,”这一行的days_of_mon多了个s
啥也没说
2024-11-25 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
累计去过“20次以上”应该不包括20才对,如果想要包括20可以写成“20次及以上”
严谨,已改!
2024-11-22 分类(1)姿势太多很过分,分类要用CaseWhen 
到底哪里错了?我能看到的trx_amt分别就是288、388、588、668、888、928、1088……呀

select (case when trx_amt=288 then '1.Withhand'
       		 when trx_amt=388 then '2.WithMimi'
       		 when trx_amt=588 then '3.BlowJobbie'
       		 when trx_amt=668 then '4.Doi'
       		 when trx_amt=888 then '5.DoubleFly'
        else '6.other' end)as ser_typ,
	   count(1) as trx_cnt,
       min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by ser_typ;
已提供正确答案

提交记录

提交日期 题目名称 提交代码
2024-12-05 登录天数分布 
WITH user_login_days AS (
SELECT usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM user_login_log
WHERE DATE(login_time) BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE()
GROUP BY usr_id
)
select sum(case when login_days between 1 and 5 then 1 else 0 end) as days_1_to_5,
	 sum(case when login_days between 6 and 10 then 1 else 0 end) as days_6_to_10,
 sum(case when login_days between 11 and 20 then 1 else 0 end) as days_11_to_20,
 sum(case when login_days>20 then 1 else 0 end) as days_over_20
from user_login_days
2024-12-05 登录天数分布 
with user_login_days as(
			select usr_id,
	 			 date(login_time) as login_date
			from user_login_log
			where date(login_time)>=date_sub(current_date(),interval 180 day)),
distinct_login_days as(
			select usr_id,
	 	 		 count(1) as login_days
			from user_login_days
			group by usr_id)
select sum(case when login_days between 1 and 5 then 1 else 0 end) as days_1_to_5,
	 sum(case when login_days between 6 and 10 then 1 else 0 end) as days_6_to_10,
 sum(case when login_days between 11 and 20 then 1 else 0 end) as days_11_to_20,
 sum(case when login_days>20 then 1 else 0 end) as days_over_20
from distinct_login_days
2024-12-01 统计每个用户使用过的不同车型数量 
select cust_uid,
	 count(distinct car_cls) as unique_car_classes
from didi_sht_rcd
group by cust_uid
order by unique_car_classes desc;
2024-12-01 查询所有终点是餐饮类地点的行程记录 
select t1.* 
from didi_sht_rcd t1
left join loc_nm_ctg t2
on t1.end_loc=t2.loc_nm
where t2.loc_ctg='餐饮'
order by start_tm
2024-12-01 按照车类统计行程次数 
select car_cls,
	 count(1) as trip_count
from didi_sht_rcd
group by car_cls
order by trip_count desc
2024-12-01 查询所有起点或终点为“海底捞西丽店”的行程记录 
select cust_uid,
	 start_loc,
	 end_loc,
 start_tm,
 car_cls
from didi_sht_rcd t1
where start_loc='海底捞西丽店' or end_loc='海底捞西丽店'
order by start_tm
2024-12-01 登录天数分布 
WITH user_login_days AS (
    SELECT usr_id,
       DATE(login_time) AS login_date
    FROM user_login_log
    WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
),
	distinct_login_days AS (
    SELECT usr_id,
       COUNT(1) AS login_days
    FROM user_login_days
    GROUP BY usr_id
)
SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    distinct_login_days;
2024-12-01 通勤、午休、临睡个时间段活跃人数分布 
select count(distinct case when time(login_time) between '07:00:00' and '09:00:00'
or time(login_time) between '18:00:00' and '20:00:00' then usr_id end) as commute,
	 count(distinct case when time(login_time) between '11:00:00' and '13:00:00' then usr_id end) as lunch_break,
 count(distinct case when time(login_time) between '22:00:00' and '23:59:59'
or time(login_time) between '00:00:00' and '01:00:00' then usr_id end) as bedtime
from user_login_log
where login_time>=date_sub(current_date(),interval 1 month)
	and login_time<current_date()
2024-12-01 通勤、午休、临睡个时间段活跃人数分布 
select count(distinct case when time(login_time) between '07:00:00' and '09:00:00'
or login_time between '18:00:00' and '20:00:00' then usr_id end) as commute,
	 count(distinct case when time(login_time) between '11:00:00' and '13:00:00' then usr_id end) as lunch_break,
 count(distinct case when time(login_time) between '22:00:00' and '23:59:59'
or login_time between '00:00:00' and '01:00:00' then usr_id end) as bedtime
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
	and login_time<current_date()
2024-12-01 上月活跃用户数 
select count(distinct usr_id) as active_users
from user_login_log
where login_time >= date_sub(current_date(),interval 30 day)
	and login_time < current_date()
2024-12-01 上月活跃用户数 
select count(distinct usr_id) as active_users
from user_login_log
where login_time<current_date() and login_time >= date_sub(current_date(),interval 30 day)
2024-12-01 上月活跃用户数 
select count(distinct usr_id)
from user_login_log
where login_time<current_date() and login_time >= date_sub(current_date(),interval 30 day)
2024-12-01 上月活跃用户数 
select count(distinct usr_id)
from user_login_log
where login_time>current_date() and login_time <= date_sub(current_date(),interval 30 day)
2024-12-01 上月活跃用户数 
select count(distinct usr_id) as active_users
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
and login_time<current_date()
2024-12-01 上月活跃用户数 
select count(distinct usr_id) as active_users
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
and login_time<date_format(current_date(),'%Y-%m-30 00:00:00')
2024-12-01 被收藏次数最多的商品 
select g.gd_id,g.gd_nm,
	 count(1) as fav_count
from gd_inf g
left join xhs_fav_rcd f
on g.gd_id=f.mch_id
group byg.gd_id
order by fav_count desc
limit 1;
2024-12-01 每年在深交所上市的银行有多少家 
select year(list_date) as Y,
	 count(1) as cnt
from stock_info
where ts_code like '%sz%' and industry='银行'
group by year(list_date)
order by year(list_date);
2024-12-01 每年在深交所上市的银行有多少家 
select year(list_date) as Y,
	 count(1) as cnt
from stock_info
where area='深圳' and industry='银行'
group by year(list_date)
order by year(list_date);
2024-12-01 每年在深交所上市的银行有多少家 
select year(list_date) as Y,
	 count(1) as cnt
from stock_info
where area='深圳' and name like '%银行%'
group by year(list_date)
order by year(list_date);
2024-12-01 一线城市历年平均气温 
select year(dt) as Y,
	 cast(avg(case when city='beijing' then tmp_h end) as decimal(4,2)) as beijing,
 cast(avg(case when city='shanghai' then tmp_h end) as decimal(4,2)) as shanghai,
 cast(avg(case when city='shenzhen' then tmp_h end) as decimal(4,2)) as shenzhen,
 cast(avg(case when city='guangzhou' then tmp_h end) as decimal(4,2)) as guangzhou
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
order by year(dt);