右下角图片

排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-26 销售金额前10的商品信息(2) 
题干中说:请输出每天GMV倒数前三的商品信息,在题解中才知道是要2024年10月的每天。
👍,已修改。
2024-11-26 销售金额前10的商品信息 
示例的输出order_id但是答案要求是goods_id,这个是坑还是搞错了。
细致啊,宝!已修改
2024-10-28 德州扑克起手牌-最强起手牌KK+ 
QQ不也是心跳牌么
笔误。感谢🙏,已修改题干

提交记录

提交日期 题目名称 提交代码
2024-11-26 抖音面试真题(2)一次性计算T+1、T+3、T+7、T+14留存率 
WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
retention_days AS (
    SELECT
        dul1.usr_id,
        dul1.login_date AS first_login_date,
        dul2.login_date AS next_day_login_date,
        DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
    FROM
        daily_unique_logins dul1
    LEFT JOIN
        daily_unique_logins dul2
    ON
        dul1.usr_id = dul2.usr_id AND
        dul2.login_date BETWEEN dul1.login_date + INTERVAL 1 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
    first_login_date,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 1 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;
2024-11-26 销售金额前10的商品信息(2) 
select *
from (select date(order_time) order_date
 , goods_id
 , sum(order_gmv) total_gmv
 , row_number() over(partition by date(order_time) order by sum(order_gmv) asc) ranking
from order_info
where substr(order_time,1,7) = '2024-10'
group by date(order_time), goods_id
order by order_date,ranking) a
where ranking <= 3
2024-11-26 销售金额前10的商品信息(2) 
select *
from (select date (order_time) order_date
 , goods_id
 , sum(order_gmv) total_gmv
 , row_number() over(partition by date(order_time) order by sum(order_gmv) asc) ranking
from order_info
group by date (order_time), goods_id
order by order_date,ranking) a
where ranking <= 3
2024-11-26 销售金额前10的商品信息(2) 
select *
from
(
select date(order_time) order_date
	,goods_id
,sum(order_gmv) total_gmv
,row_number() over(partition by date(order_time) order by sum(order_gmv)) ranking
from order_info
group by date(order_time),goods_id
order by order_date) a
where ranking <= 3
2024-11-26 销售金额前10的商品信息 
select *
from
(
select goods_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
where ranking <= 10
2024-11-26 销售金额前10的商品信息 
select *
from
(
select goods_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
limit 10
2024-11-26 销售金额前10的商品信息 
select *
from
(
select goods_id order_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
limit 10
2024-11-26 销售金额前10的商品信息 
select *
from
(
select goods_id order_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
where ranking <= 10
2024-11-26 查询所有起点和终点都属于餐饮类别的行程 
select d.* from didi_sht_rcd d
inner join loc_nm_ctg l
on d.start_loc = l.loc_nm
inner join loc_nm_ctg lo
on d.end_loc = lo.loc_nm
where l.loc_ctg = '餐饮'
and lo.loc_ctg = '餐饮'
order by start_tm
2024-11-26 统计每个用户使用过的不同车型数量 
select cust_uid,count(distinct car_cls) unique_car_classes from didi_sht_rcd
group by cust_uid
order by unique_car_classes desc
2024-11-26 查询所有终点是餐饮类地点的行程记录 
select d.* from didi_sht_rcd d
inner join loc_nm_ctg l
on d.end_loc = l.loc_nm
where l.loc_ctg = '餐饮'
order by start_tm
2024-11-26 按照车类统计行程次数 
select car_cls,count(cust_uid) trip_countfrom didi_sht_rcd
group by car_cls
order by trip_count desc
2024-10-28 字符串与通配符(2)好多关键词做规则,可以使用rlike 
select * from cmb_usr_trx_rcd limit 5
2024-10-28 条件过滤(2)半夜活动有猫腻,Hour函数给给力 
select * from cmb_usr_trx_rcd where date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id='5201314520'
order by trx_time
2024-10-28 条件过滤(1)异地男友有异常,数分闺蜜来帮忙 
select * from cmb_usr_trx_rcd where date(trx_time) between '2024-09-01' and '2024-09-30'
and usr_id = '5201314520'
order by trx_time
2024-10-28 条件过滤(1)异地男友有异常,数分闺蜜来帮忙 
select * from cmb_usr_trx_rcd where date(trx_time) between '2024-09-01' and '2024-09-30'
and usr_id = '5201314520'
2024-10-28 条件过滤(1)异地男友有异常,数分闺蜜来帮忙 
select * from cmb_usr_trx_rcd where date(trx_time) between '2024-09-01' and '2024-09-30'
2024-10-28 德州扑克起手牌-最强起手牌KK+ 
select *
from hand_permutations
where 
    concat(card1, card2) like '%A%A%' or
    concat(card1, card2) like '%A%K%' or
    concat(card1, card2) like '%K%K%' or
    concat(card1, card2) like '%K%A%'
order by id;
2024-10-28 德州扑克起手牌-最强起手牌KK+ 
select *
from hand_permutations
where 
    concat(card1, card2) like '%A%A%' or
    concat(card1, card2) like '%A%K%' or
    concat(card1, card2) like '%K%K%' or
    concat(card1, card2) like '%K%A%' or
    concat(card1, card2) like '%Q%Q%'
order by id;
2024-10-28 德州扑克起手牌-最强起手牌KK+ 
select *
from hand_permutations
where 
    concat(card1, card2) like '%A%A%' or
    concat(card2, card1) like '%A%K%' or
    concat(card1, card2) like '%K%K%' or
    concat(card1, card2) like '%A%K%' or
    concat(card1, card2) like '%Q%Q%'
order by id;