排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2024-12-31 登录天数分布  
with user_login_days as (
				select usr_id,date(login_time)
				from user_login_log
				where date(login_time) >= date_sub(curdate(),interval 180 day)
),
dis_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 
    dis_login_days;
2024-12-31 登录天数分布  
with user_login_days as (
				select usr_id,date(login_time)
				from user_login_log
				where date(login_time) >= date_sub(curdate(),interval 181 day)
),
dis_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 
    dis_login_days;
2024-12-31 登录天数分布  
with user_login_days as (
				select usr_id,date(login_time)
				from user_login_log
				where date(login_time) >= date_sub(curdate(),interval 179 day)
),
dis_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 
    dis_login_days;
2024-12-31 通勤、午休、临睡个时间段活跃人数分布  
SELECT
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00'
             OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN usr_id
    END) AS commute,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
    END) AS lunch_break,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN 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_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');
2024-12-27 德州扑克起手牌-同花  
select round(count(case when right(card1,1) = right(card2,1) then 1 else null end)/2) as cnt,
round(count(1)/2) as ttl_cnt,
round((count(case when right(card1,1) = right(card2,1) then 1 else null end)/2) / (count(1)/2),3) as p
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select round(count(case when right(card1,1) = right(card2,1) then 1 else null end)/2) as cnt,
round(count(1)/2) as ttl_cnt,
round(count(case when right(card1,1) = right(card2,1) then 1 else null end)/2 / count(1)/2,3) as p
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt 
    , count(1)/2 as ttl_cnt 
    ,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2/count(1)/2 AS DECIMAL(4,4)) as p 
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select round(count(case when right(card1,1) = right(card2,1) then 1 else null end)/2) as cnt,
round(count(1)/2) as ttl_cnt,
round(count(case when right(card1,1) = right(card2,1) then 1 else null end) / count(1),3) as p
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select count(case when right(card1,1) = right(card2,1) then 1 else null end) as cnt,
round(count(1)/2) as ttl_cnt,
round(count(case when right(card1,1) = right(card2,1) then 1 else null end) / count(1),3) as p
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select count(case when right(card1,1) = right(card2,1) then 1 else null end) as cnt,
count(1)/2 as ttl_cnt,
round(count(case when right(card1,1) = right(card2,1) then 1 else null end) / count(1),3) as p
from hand_permutations
2024-12-27 德州扑克起手牌-同花  
select count(case when right(card1,1) = right(card2,1) then 1 else null end) as cnt,
count(1) as ttl_cnt,
round(count(case when right(card1,1) = right(card2,1) then 1 else null end) / count(1),3) as p
from hand_permutations
2024-12-27 德州扑克起手牌- 手对  
select * from hand_permutations 
where left(card1,1) = left(card2,1)
2024-12-27 德州扑克起手牌- A花  
select * from hand_permutations 
where right(card1,1) = right(card2,1)
and (card1 like 'A%' or card2 like 'A%')
2024-12-27 德州扑克起手牌- A花  
select * from hand_permutations 
where right(card1,1) = right(card2,2)
and card1 like 'A%' or card2 like 'A%'
2024-12-27 德州扑克起手牌-最强起手牌KK+  
select * from hand_permutations 
where (card1 like 'K%' and card2 like 'K%')
OR(card1 like 'A%' and card2 like 'K%')
OR(card1 like 'K%' and card2 like 'A%')
OR(card1 like 'A%' and card2 like 'A%')
2024-12-23 上月活跃用户数  
SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');
2024-12-23 被收藏次数最多的商品  
select gd_id , gd_nm ,count(1) as 	fav_count
from gd_inf as g join xhs_fav_rcd as xf on g.gd_id = xf.mch_id
group by g.gd_id
order by fav_count desc 
limit 1
2024-12-23 每年在深交所上市的银行有多少家  
select year(list_date) as Y,
count(distinct ts_code) as cnt
from stock_info
where ts_code like '%SZ' and industry = '银行'
group by Y
2024-12-23 每年在深交所上市的银行有多少家  
select year(list_date) as Y,
count(distinct ts_code) as cnt
from stock_info
where area = '深圳' and industry = '银行'
group by Y
order by Y
2024-12-23 每年在深交所上市的银行有多少家  
select year(list_date) as Y,
count(case when area = '深圳' then 1 else 0 end) as cnt
from stock_info
group by Y
order by Y