排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2024-12-28 从商品角度统计收藏到购买的转化率  已解决
2024-12-25 Halo出行-通勤活跃用户标签开发  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
  select user1 as user_id,
      user2 as friend_id
 from friend_relationships
 union
 select user2 as user_id,
      user1 as friend_id
 from friend_relationships
),
a2 as (
  select a1.user_id,
         friend_id,
         steps
  from a1
       join user_steps u
       on a1.friend_id = u.user_id
),
a3 as (
  select user_id,
         user_id as friend_id,
         steps
  from user_steps
  union
  select user_id,
         friend_id,
         steps
  from a2
),
a4 as (
  select user_id,
         friend_id,
         steps,
         rank() over (partition by user_id order by steps desc) as row_num
  from a3
)
select user_id,
       row_num
from a4
where user_id = friend_id
执行得到的结果相同,为什么说我错了呀
已修正,是该用rank的,之前正确答案用了row_number,已改正。
2024-12-29 抖音面试真题(1)T+1日留存率 
with At as (
  select distinct usr_id,
         date(login_time) as login_date
  from user_login_log
  where datediff(curdate(), login_time) <= 30
)
select At.login_date,
       concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
     left join At Bt on At.usr_id = Bt.usr_id
                        and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date
啥也没说
2024-12-28 登录天数分布 
with new1 as (
  select *,
         date(login_time) as login_date
  from user_login_log
  where datediff(curdate(), date(login_time)) <= 180
),
new2 as (
  select usr_id,
         count(distinct login_date) as freq
  from new1
  group by usr_id
),
new3 as (
  select *,
         if(freq between 1 and 5, 1,
            if(freq between 6 and 10, 2,
               if(freq between 11 and 20, 3, 4))) as type
  from new2
)
select sum(type = 1) as days_1_to_5,
       sum(type = 2) as days_6_to_10,
       sum(type = 3) as days_11_to_20,
       sum(type = 4) as days_over_20
       from new3
啥也没说
2024-12-27 绘制小时进入人数曲线 
select lpad(hour_entered, 2, '0') as hour_entered, enter_count
from(
  select hour(enter_time) as hour_entered,
  count(1) as enter_count
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time)) as new_table
order by hour_entered asc这样才对吧
严谨!就按你的来👍
2024-12-25 字符串与通配符(2)好多关键词做规则,可以使用rlike 
加不加.*没区别吧
没区别。不过这题还有一个坑,你把rlike的case when分之放前面,like的放后面试试

提交记录

提交日期 题目名称 提交代码
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
having online_users > 0
order by online_users desc
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
2024-12-29 快手面试真题(1)同时在线人数 
WITH OnlineUsers AS (
    SELECT 
        t1.live_id,
        t1.usr_id
    FROM 
        ks_live_t1 t1
    WHERE 
        '2021-09-12 23:48:38' BETWEEN t1.leave_time AND t1.enter_time
)
SELECT 
    ou.live_id,
    t2.live_nm,
    COUNT(distinct ou.usr_id) AS online_users
FROM 
    OnlineUsers ou
JOIN 
    ks_live_t2 t2
ON 
    ou.live_id = t2.live_id
GROUP BY 
    ou.live_id, t2.live_nm
ORDER BY 
    online_users DESC
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(distinct case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
2024-12-29 快手面试真题(1)同时在线人数 
select t1.live_id,
 live_nm,
 count(case when '2021-09-12 11:48:38' between enter_time and leave_time
then 1 else null end) as online_users
from ks_live_t1 t1
 join ks_live_t2 t2
 on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user2 = u.user_id
 union
 select user2 as user_id,
      user1 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user1 = u.user_id
union
select user_id,
 user_id as friend_id,
 steps
from user_steps
),
a2 as (
select user_id,
 friend_id,
 steps,
 row_number() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
 row_num
from a2
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user2 = u.user_id
 union
 select user2 as user_id,
      user1 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user1 = u.user_id
union
select user_id,
 user_id as friend_id,
 steps
from user_steps
),
a2 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
 row_num
from a2
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with friend_steps as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
    union
    select user_id,
           user_id as friend_id,
           steps
    from user_steps
),
ranked_steps as (
    select fs.user_id,
           fs.friend_id,
           fs.steps,
           row_number() over (partition by fs.user_id order by fs.steps desc) as row_num
    from friend_steps fs
)
select rs.user_id,
       rs.row_num
from ranked_steps rs
where rs.user_id = rs.friend_id;
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user2 = u.user_id
 union
 select user2 as user_id,
      user1 as friend_id,
 steps
 from friend_relationships f
 join user_steps u
 on f.user1 = u.user_id
),
a2 as (
select a1.user_id,
 friend_id,
 steps
from a1
union
select user_id,
 user_id as friend_id,
 steps
from user_steps
),
a3 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id
 from friend_relationships
 union
 select user2 as user_id,
      user1 as friend_id
 from friend_relationships
),
a2 as (
select a1.user_id,
 friend_id,
 steps
from a1
 join user_steps u
 on a1.friend_id = u.user_id
union
select user_id,
 user_id as friend_id,
 steps
from user_steps
),
a3 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id
 from friend_relationships
 union
 select user2 as user_id,
      user1 as friend_id
 from friend_relationships
),
a2 as (
select a1.user_id,
 friend_id,
 steps
from a1
 join user_steps u
 on a1.friend_id = u.user_id
),
a3 as (
select user_id,
 user_id as friend_id,
 steps
from user_steps
union
select user_id,
 friend_id,
 steps
from a2
),
a4 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
 row_num
from a4
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id
 from friend_relationships
 union
 select user2 as user_id,
      user1 as friend_id
 from friend_relationships
),
a2 as (
select a1.user_id,
 friend_id,
 steps
from a1
 join user_steps u
 on a1.friend_id = u.user_id
),
a3 as (
    select user_id,
 friend_id,
 steps
from a2
union
    select user_id,
       user_id as friend_id,
       steps
    from user_steps
),
a4 as (
select user_id,
 friend_id,
 steps,
 row_number() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
 row_num
from a4
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
    select user_id,
 friend_id,
 steps
from a1
union
    select user_id,
           user_id as friend_id,
           steps
    from user_steps
),
a3 as (
select user_id,
 friend_id,
 steps,
 row_number() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
    union
    select user_id,
           user_id as friend_id,
           steps
    from user_steps
),
a2 as (
select user_id,
 friend_id,
 steps,
 row_number() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
 row_num
from a2
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
 user_id as friend_id,
 steps
from user_steps
union
select user_id,
 friend_id,
 steps
from a1
),
a3 as (
select user_id,
 friend_id,
 steps,
 row_number() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
 user_id as friend_id,
 steps
from user_steps
union
select user_id,
 friend_id,
 steps
from a1
),
a3 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
    select t1.user1 as user_id,
           t1.user2 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user2 = t2.user_id
    union
    select t1.user2 as user_id,
           t1.user1 as friend_id,
           t2.steps
    from friend_relationships t1
    join user_steps t2 on t1.user1 = t2.user_id
    union
    select user_id,
           user_id as friend_id,
           steps
    from user_steps
),
a2 as (
select user_id,
 user_id as friend_id,
 steps
from user_steps
union
select user_id,
 friend_id,
 steps
from a1
),
a3 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id
2024-12-29 好友步数排名-考虑反向好友关系 
with a1 as (
select user1 as user_id,
      user2 as friend_id,
 steps
 from friend_relationships t1
 join user_steps t2 on t1.user2 = t2.user_id
union
 select user2 as user_id,
      user1 as friend_id,
 steps
 from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
 user_id as friend_id,
 steps
from user_steps
union
select user_id,
 friend_id,
 steps
from a1
),
a3 as (
select user_id,
 friend_id,
 steps,
 rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
 row_num
from a3
where user_id = friend_id