排名

用户解题统计

过去一年提交了

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

错题集 数据思维刷题中答错的题目

模块 知识点 题目 你的答案 正确答案 操作
暂无错题，继续保持！

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2026-03-08 找出所有以酒店为起点或终点的类别组合的最热门路线  
select
  start_loc,
  end_loc,
  start_ctg,
  end_ctg,
  cnt
from
(select
  start_loc,
  end_loc,
  count(*) cnt,
  start_ctg,
  end_ctg,
  row_number()over(partition by case when end_ctg='酒店' then start_ctg else end_ctg end order by count(*) desc) as rnk
from
(select 
  a.start_loc,
  a.end_loc,
  b.loc_ctg as start_ctg,
  c.loc_ctg as end_ctg
from didi_sht_rcd a
left join loc_nm_ctg b on a.start_loc=b.loc_nm
left join loc_nm_ctg c on a.end_loc=c.loc_nm
where (b.loc_ctg='酒店' or c.loc_ctg='酒店')and c.loc_ctg is not null and b.loc_ctg is not null)t1
group by start_loc,end_loc,start_ctg,end_ctg
order by cnt desc
)t2
where rnk=1
order by cnt desc,start_loc asc 请问我的代码在哪里的逻辑上有问题啊

啥也没说
2026-03-04 不分类别的最火直播间  
月活是一个月活跃的人数，日活是那天活跃的人数，所以23点是这个时点还是整一个小时呢
 
啥也没说
2026-03-02 登录天数分布  
select
  count(case when cnt between 1 and 5 then usr_id end),
  count(case when cnt between 6 and 10 then usr_id end),
  count(case when cnt between 11 and 20 then usr_id end),
  count(case when cnt >20 then usr_id end)
from
(select
  usr_id,
  count(distinct login_date) as cnt
from
(select
  usr_id,
  date(login_time) as login_date
from user_login_log
where login_time>=date_sub(curdate(),interval 180 day))t1

group by usr_id)t2

啥也没说

提交记录

提交日期 题目名称 提交代码
2026-05-12 按交易所统计软件服务、银行上市公司数量（3）  
select 
    year(list_date) as Y
    ,
    case when right(ts_code,2)='SZ' then '深交所'
    when right(ts_code,2)='SH' then '上交所' 
    when right(ts_code,2)='BJ' then '北交所'
    end as jys
    ,industry
    ,count(1) as cnt
from stock_info
where industry in ('银行','软件服务') and year(list_date) between '2019' and '2024'
group by 1,2,3
order by 1,2,3
2026-05-12 按交易所统计软件服务、银行上市公司数量（2）  
select 
    case when right(ts_code,2)='SZ' then '深交所'
    when right(ts_code,2)='SH' then '上交所' 
    when right(ts_code,2)='BJ' then '北交所'
    end as jys
    ,industry
    ,count(1) as cnt
from stock_info
where industry in ('银行','软件服务')
group by 1,2
order by 3 desc
2026-05-12 按交易所统计软件服务、银行上市公司数量  
select 
    right(ts_code,2) as jys
    ,industry
    ,count(1) as cnt
from stock_info
where industry in ('银行','软件服务')
group by 1,2
order by 3 desc
2026-05-12 国庆假期后第一天涨幅高于1%的股票  
SELECT 
    ts_code, 
    open_price, 
    close_price
FROM 
    daily_stock_prices
WHERE 
    trade_date = '2023-10-09' AND pct_change > 1;
2026-05-12 北交所每年上市数量  
select 
    year(list_date) as Y,
    count(distinct ts_code) as cnt
from 
    stock_info
where ts_code like '%BJ'
group by 
    year(list_date)
order by 
   1
2026-05-12 每年地产与软件服务上市公司对比  
select 
    year(list_date) as Y
    ,sum(case when industry in ('全国地产','区域地产') then 1 else 0 end) as '地产'
    ,sum(case when industry ='软件服务' then 1 else 0 end) as '软件服务' 
from 
    stock_info
where 
    year(list_date) between 2000 and 2024
group by 
    year(list_date)
order by 1
2026-05-12 每年在深交所上市的银行有多少家  
select 
    year(list_date) as Y 
    ,count(distinct ts_code) as cnt
from 
    stock_info
where 
    ts_code like '%SZ'
    and industry = '银行'
group by 
    year(list_date)
2026-05-10 时间日期（3）按月统计日花费，一天都不要浪费  
select 
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time)) ) as day_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from cmb_usr_trx_rcd a
left join cmb_mch_typ m
on a.mch_nm = m.mch_nm
where a.usr_id=5201314520 and year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1
2026-05-10 时间日期（2）按月统计日花费，一天都不要浪费  
select 
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as day_of_mon
from cmb_usr_trx_rcd a
left join cmb_mch_typ b on a.mch_nm=b.mch_nm
where a.usr_id='5201314520' and year(a.trx_time) in (2023,2024) and b.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1
2026-05-10 时间日期（1）按月统计日花费，一天都不要浪费  
select 
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day
from cmb_usr_trx_rcd 
where usr_id='5201314520' and year(trx_time) in (2023,2024)
group by trx_mon
order by 1
2026-04-27 夜深与专车比例的相关系数  
WITH TimeExtracted AS (
    SELECT 
        cust_uid,
        start_loc,
        end_loc,
        CAST(SUBSTR(start_tm, 4, 2) AS SIGNED) AS hour,
        car_cls
    FROM didi_sht_rcd
),
FilteredData AS (
    SELECT *
    FROM TimeExtracted
    WHERE hour BETWEEN 18 AND 23
),
HourlyCounts AS (
    SELECT
        hour,
        COUNT(*) AS total_orders,
        SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders
    FROM FilteredData
    GROUP BY hour
),
PercentageAOrders AS (
    SELECT
        hour,
        (A_orders * 100.0 / total_orders) AS percentage_A_orders
    FROM HourlyCounts
),
AggregatedData AS (
    SELECT
        COUNT(*) AS n,
        SUM(hour) AS sum_x,
        SUM(percentage_A_orders) AS sum_y,
        SUM(hour * percentage_A_orders) AS sum_xy,
        SUM(hour * hour) AS sum_x2,
        SUM(percentage_A_orders * percentage_A_orders) AS sum_y2
    FROM PercentageAOrders
)
SELECT
    cast((n * sum_xy - sum_x * sum_y) / 
    (SQRT(n * sum_x2 - sum_x * sum_x) * SQRT(n * sum_y2 - sum_y * sum_y)) as decimal(10,2)) AS R
FROM AggregatedData;
2026-04-26 表连接（5）哪些没被分出来，用左用内你来猜  
select 
t.mch_typ,
u.mch_nm,
count(u.mch_nm),
sum(trx_amt)
from cmb_usr_trx_rcd u 
left join cmb_mch_typ t on u.mch_nm=t.mch_nm
where u.usr_id='5201314520' and year(trx_time)=2024
group by t.mch_typ,
u.mch_nm
having t.mch_typ is null
2026-04-26 表连接（4）渣男把钱花在哪儿，维表可以来帮忙  
select 
m.mch_typ,
count(u.mch_nm),
sum(u.trx_amt)
from cmb_usr_trx_rcd u
left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where u.usr_id='5201314520' and year(u.trx_time)=2024
group by m.mch_typ
2026-04-26 表连接（3）一直使用一张表，现在开始两张表  
select 
mch_typ,
count(mch_nm),
count(distinct mch_nm)
from cmb_mch_typ 
group by mch_typ
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where (date(trx_time)>'2022-11-01' and date(trx_time)<='2024-12-31') and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5) like '88%' or substr(trx_amt,-5)like '98%') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where (date(trx_time)>'2022-11-01' and date(trx_time)<='2024-12-31') and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where date(trx_time) between'2022-11-01' and '2024-12-31' and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where trx_time between'2022-11-01' and '2024-12-31' and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where trx_time between'2022-11-01' and '2024-12-31' and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and usr_id='5201314520' and upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA')
and (hour(trx_time)=23 or (hour(trx_time) between 0 and 2))
group by trx_mon
order by trx_mon
2026-04-25 小结（2）越花越多是死罪，按月统计Substr  
select
 date_format(trx_time,'%Y-%m') as trx_mon,
 count(1) as trx_cnt,
 sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd 
where trx_time between'2022-11-01' and '2024-12-31' and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and usr_id='5201314520' and mch_nm rlike ('足疗|保健|按摩|养生|SPA')
group by trx_mon
order by trx_mon