路静

分类

/ 注册

全部文章（共49篇）

题解 | 淘宝店铺的实际销售额与客单价

select sum(sales_num*goods_price) sales_total, round(sum(sales_num*goods_price)/count(distinct user_id),3) per_trans from sales_tb s join goods_tb g o...

2025-07-11

0 39

题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

select room_id, room_name, user_count from( select u.room_id, count(distinct user_id) user_count, room_name from user_view_tb u left j...

2025-07-11

0 71

题解 | 被重复观看次数最多的3个视频

select cid, concat(pv,'.000') pv, row_number() over(order by pv desc) rk from ( select p.cid,release_date, count(uid) pv from course_info_...

2025-07-11

0 48

题解 | 查询成绩

select count(*) from (select avg(score) av from SC group by sId having av>60) as t 如果直接在主查询中使用分组则会出现多行数据，count的结果为对每一组的内部的满足条件的记录的次数汇总，因此为了避免这种情况，应...

2025-07-10

0 29

题解 | 请写出计算粉丝ctr的sql语句

select round(re_cnt/sh_cnt,4) fans_ctr from ( select sum(read_num) re_cnt, sum(show_num) sh_cnt from a join b on a.author_id=b.author_id j...

2025-07-10

0 54

题解 | 网易云音乐推荐(网易校招笔试真题)

select music_name from music_likes ml left join music m on ml.music_id = m.id where user_id in(select follower_id from follow where user_id = 1) a...

2025-07-10

0 52

题解 | 支付间隔平均值

select cast(avg(abs(timestampdiff(second,a.logtime,b.logtime))) as signed) gap from order_log a join select_log b on a.order_id=b.order_id 在什么情况下roun...

2025-07-10

0 41

题解 | 统计用户获得积分

select user_id, sum(floor(timestampdiff(minute,visit_time,leave_time)/10)) point from visit_tb group by user_id order by point desc 左右两个逻辑的选择要看示例数据在 S...

2025-07-09

0 64

题解 | 统计员工薪资扣除比例

select staff_id, staff_name, dock_ratio from ( select staff_id, staff_name, concat(round(dock_salary/normal_salary*100,1),'%') as dock_rat...

2025-07-09

0 53

题解 | 统计用户从访问到下单的转化率

select date, concat(round(id_cnt/vi_cnt*100,1),'%') cr from ( select date(order_time) date, count(distinct m.user_id) id_cnt, count(distin...

2025-07-09

8 84