右下角图片

排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2024-11-12 招建银行信用卡中心客户挽留-电商平台分类 
select 
	mch_nm as merchant_name,
	case when mch_nm like '%拼多多%' then '拼多多'
	 when mch_nm like '%京东%'	then '京东'
 when mch_nm like'%抖音%' then '抖音'
 when mch_nm like '%淘系%' then '淘系'
 else '其他'
 end as platform
from 
ccb_trx_rcd
group by mch_nm
2024-11-12 登录天数分布 
select
	sum(case when days between 1 and 5 then 1 else 0 end) as days_1_to_5,
sum(case when days between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when days between 11 and 20 then 1 else 0 end) as days_11_to_20,
sum(case when days >20 then 1 else 0 end) as days_over_20
from
(select
usr_id,
count(distinct login_day) as days
from
(select 
usr_id,
date(login_time) as login_day
from 
user_login_log
where 
login_time>date_sub('2024-10-01 12:23:24',interval 180 day)
and login_time<='2024-10-01 12:23:24'
)t
group by usr_id
)t1
2024-11-12 通勤、午休、临睡个时间段活跃人数分布 
select
	count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from 
	user_login_log
where
	login_time<=last_day(date_sub(current_date,interval 2 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 3 month)),interval 1 day)
2024-11-12 通勤、午休、临睡个时间段活跃人数分布 
select
	count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from 
	user_login_log
where
	login_time<=last_day(date_sub(current_date,interval 1 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 2 month)),interval 1 day)
2024-11-12 通勤、午休、临睡个时间段活跃人数分布 
select
	count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from 
	user_login_log
2024-11-12 通勤、午休、临睡个时间段活跃人数分布 
select
	sum((case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then 1 else 0 end)) as commute,
sum((case when hour(login_time) between 11 and 13 then 1 else 0 end)) as lunch_break,
sum((case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 then 1 else 0 end)) as bedtime
from 
	user_login_log
2024-11-12 上月活跃用户数 
SELECT
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
where
	login_time<=last_day(date_sub(current_date,interval 1 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 2 month)),interval 1 day)
2024-11-12 上月活跃用户数 
SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(current_date(), INTERVAL 30 day), '%Y-%m-30 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-30 00:00:00')
2024-11-12 被收藏次数最多的商品 
select 
	gd_inf.gd_id,
gd_inf.gd_nm,
count(*) as fav_count
from
	gd_inf	
right join 
	xhs_fav_rcd 
on gd_inf.gd_id=xhs_fav_rcd.mch_id
group by 	
	gd_id,
gd_nm
limit 1
2024-11-12 被收藏次数最多的商品 
select 
	gd_inf.gd_id,
gd_inf.gd_nm,
count(*) as fav_count
from
	gd_inf	
right join 
	xhs_fav_rcd 
on gd_inf.gd_id=xhs_fav_rcd.mch_id
group by 	
	gd_id,
gd_nm
2024-11-12 每年在深交所上市的银行有多少家 
select 
	year(list_date) as Y,
count(name) as cnt
from 
	stock_info
where
	area = '深圳'
 and industry = '银行'
group by
	year(list_date)
order by 1
2024-11-12 每年在深交所上市的银行有多少家 
select 
	year(list_date) as Y,
count(name='深交所') as cnt
from 
	stock_info
group by
	year(list_date)
order by 1
2024-11-12 一线城市历年平均气温 
select 
	year(dt) as Y,
cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as beijing,
cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as shanghai,
cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as shenzhen,
cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as guangzhou
from 
	weather_rcd_china
group by year(dt)
2024-11-11 冬季下雪天数 
select
	city,
	sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from
	weather_rcd_china
where 
  month(dt) in (12,1,2)
group by city
order by 2 desc
2024-11-11 冬季下雪天数 
select
	city,
	sum(case when con like '%雪%' then 1 else 0 end) as snowny_days
from
	weather_rcd_china
where 
  month(dt) in (12,1,2)
group by city
order by 2 desc
2024-11-11 冬季下雪天数 
select
	city,
	sum(case when con like '%雪%' then 1 else 0 end) as cloudy_days
from
	weather_rcd_china
where 
  month(dt) in (12,1,2)
group by city
order by 2 desc
2024-11-11 冬季下雪天数 
select
	city,
	sum(case when con like '%雪%' then 1 else 0 end) as cloudy_days
from
	weather_rcd_china
group by city
order by 2 desc
2024-11-11 多云天气天数 
select
	city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 0 else 1 end)/count(con)*100 as decimal(4,2)),'%') as p
from
	weather_rcd_china
where year(dt)=2021
group by city
order by 3 desc
2024-11-11 多云天气天数 
select
	city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 0 else 1 end)/count(con)*100 as decimal(4,2)),'%') as p
from
	weather_rcd_china
group by city
order by 3 desc
2024-11-11 城市平均最高气温 
select
	distinct city,
	CAST(avg(tmp_h) over(partition by city,year(dt)) AS DECIMAL(4,2)) as avg_tmp_h
from 
	weather_rcd_china
WHERE YEAR(dt)=2021
order by 2 desc