sql学习记录【SQL大厂笔试真题】

SQLW1 每个月Top3的周杰伦歌曲

从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

1.select month,ranking,song_name,play_pv from (
select month,row_number() over(partition by month order by play_pv desc,song_id asc) as ranking,song_name,play_pv from (
select month(a.fdate) as month,b.song_id,b.song_name,count(a.song_id) as play_pv
from play_log a
inner join song_info b on b.song_id=a.song_id and b.singer_name='周杰伦'
inner join user_info c on c.user_id=a.user_id and c.age>=18 and age<=25
where year(a.fdate)=2022
group by month,song_name,song_id) s ) t
where ranking<=3
order by month,ranking asc;
2.select month,ranking,song_name,play_pv from(
select month(a.fdate) as month,row_number() over(partition by month(a.fdate) order by  count(a.song_id) desc,a.song_id asc)
 as ranking,b.song_name,count(a.song_id) as play_pv 
 from play_log a
inner join song_info b on b.song_id=a.song_id and b.singer_name='周杰伦'
inner join user_info c on c.user_id=a.user_id and c.age>=18 and c.age<=25
where year(a.fdate)=2022
group by month(a.fdate),b.song_name,a.song_id) t 
where ranking<=3;

优化语句：

WITH ranked_songs AS (
    SELECT 
        EXTRACT(MONTH FROM a.fdate) AS month,
        b.song_id,
        b.song_name,
        COUNT(a.song_id) AS play_pv,
        ROW_NUMBER() OVER(PARTITION BY EXTRACT(MONTH FROM a.fdate) 
            ORDER BY COUNT(a.song_id) DESC, b.song_id ASC) AS ranking
    FROM 
        play_log a
    JOIN 
        song_info b ON b.song_id = a.song_id AND b.singer_name = '周杰伦'
    JOIN 
        user_info c ON c.user_id = a.user_id AND c.age between 18 AND 25
    WHERE 
        a.fdate BETWEEN '2022-01-01' AND '2022-12-31'
    GROUP BY 
        EXTRACT(MONTH FROM a.fdate), b.song_id, b.song_name
)

SELECT 
    month, ranking, song_name, play_pv
FROM 
    ranked_songs
WHERE 
    ranking <= 3;

SQLW2 最长连续登录天数

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”

select user_id,max(num2) as max_consec_days from 
(select user_id,date1,count(date1) as num2 from 
(select user_id,fdate,row_number() over(partition by user_id order by fdate asc) as num1,date_sub(fdate,interval row_number() over(partition by user_id order by fdate asc) day) as date1
from tb_dau
where fdate between '2023-01-01' and '2023-01-31') a 
group by user_id,date1) b
group by user_id

SQLW3 分析客户逾期情况

请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比？要求输出还款能力级别、逾期客户占比；

select pay_ability,concat(round(sum(if(overdue_days is null,0,1))/count(*)*100,1),'%') as overdue_ratio
from loan_tb a
join customer_tb b on b.customer_id=a.customer_id
group by pay_ability
order by overdue_ratio desc;

SQLW4 获取指定客户每月的消费额

现需要查询 Tom 这个客户在 2023 年每月的消费金额（按月份正序显示），请编写 SQL 语句实现上述需求。

select substr(t_time,1,7) as time, sum(t_amount) as total
from trade a
join customer b on b.c_id=a.t_cus and b.c_name='Tom'
where year(a.t_time)=2023 and t_type=1
group by time
order by time asc;

SQLW5 查询连续入住多晚的客户信息

请查询该酒店从6月12日开始连续入住多晚的客户信息？要求输出：客户id、房间号、房间类型、连续入住天数（按照入住天数升序排序）

select user_id,a.room_id,b.room_type,datediff(checkout_time,checkin_time) as days
from checkin_tb a
join guestroom_tb b on b.room_id=a.room_id
where checkin_time>='2022-06-12'
having days>1
order by days asc,room_id asc,user_id desc;

SQLW6 统计所有课程参加培训人次

请统计该公司所有课程参加培训人次？

select sum(ifnull(length(course)-length(replace(course,',',''))+1,0)) as staff_nums
from cultivate_tb;

SQLW7 查询培训指定课程的员工信息

请查询培训课程course3的员工信息？注：只要培训的课程中包含course3课程就计入结果，要求输出：员工id、姓名，按照员工id升序排序；

select a.staff_id,b.staff_name
from cultivate_tb a 
join staff_tb b on b.staff_id=a.staff_id
where a.course like '%course3%'
order by a.staff_id;

SQLW8 推荐内容准确的用户平均评分

请统计推荐内容准确的用户平均评分？(结果保留3位小数)。注：（1）准确定义：推荐的内容标签与用户喜好标签一致；如推荐多次给同一用户，有一次及以上准确就归为准确。

select round(avg(score),3) as avg_score 
from user_action_tb where user_id in 
(select distinct user_id
from recommend_tb a 
join user_action_tb b on b.user_id=a.rec_user and b.hobby_l=a.rec_info_l);

SQLW9 每个商品的销售总额

使用上述表格，编写一个SQL查询，返回每个商品的销售总量，先按照商品类别升序排序，再按销售总量降序排列，同时包括商品名称和销售总量。此外，还需要在结果中包含每个商品在其所属类别内的排名，排名相同的商品可以按照 product_id 升序排序。

select b.name as product_name,total_sales,row_number() over(partition by b.category order by total_sales desc) as category_rank
from (select product_id,sum(quantity) as total_sales from orders 
group by product_id) a
join products b on b.product_id=a.product_id
order by b.category asc,a.total_sales desc,a.product_id asc;

优化语句：

SELECT 
    b.name AS product_name,
    SUM(a.quantity) AS total_sales,
    ROW_NUMBER() OVER(PARTITION BY b.category ORDER BY SUM(a.quantity) DESC) AS category_rank
FROM orders a
JOIN products b ON a.product_id = b.product_id
GROUP BY b.product_id
ORDER BY b.category ASC, total_sales DESC, b.product_id ASC;

SQLW10 统计各岗位员工平均工作时长

请统计该公司各岗位员工平均工作时长？注：如员工未打卡该字段数据会存储为NULL，那么不计入在内；要求输出：员工岗位类别、平均工作时长（以小时为单位输出并保留三位小数），按照平均工作时长降序排序；

select b.post,round(avg(timestampdiff(second,first_clockin,last_clockin)/3600),3) as work_hours
from attendent_tb a
join staff_tb b on b.staff_id=a.staff_id
where a.first_clockin is not null and last_clockin is not null
group by post
order by work_hours desc;

SQLW11 查询连续登陆的用户

请查询连续登陆不少于3天的新注册用户？注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。要求：输出user_id并升序排序；

select a.user_id from (
select user_id,log_time,lead(log_time,2) over(partition by user_id order by log_time asc) as log_time1
from login_tb
order by user_id ) a
join register_tb b on b.user_id=a.user_id
where datediff(log_time1,log_time)=2;

查询有重复登录数据的语句：

WITH DailyFirstLogin AS (
    SELECT 
        user_id, 
        DATE(log_time) AS login_date, 
        MIN(log_time) AS min_log_time
    FROM login_tb
    GROUP BY user_id, login_date
),
LoginSequence AS (
    SELECT 
        user_id, 
        min_log_time, 
        LEAD(min_log_time, 2) OVER(PARTITION BY user_id ORDER BY min_log_time) AS log_time1
    FROM DailyFirstLogin
)
SELECT b.user_id
FROM LoginSequence a
JOIN register_tb b ON b.user_id = a.user_id
WHERE DATEDIFF(a.log_time1, a.min_log_time) = 2;