【SQL每日一练】day18：大厂笔试真题

题目一：统计所有课程参加培训人次

题目要求：

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

表结构：

运行结果示例：

思路：

        本题我们分别统计出参加各类培训的人员，使用union all合并结果，查询合并后的所有数据数量即可。

运行代码示例：

select count(*) as course_pv from ( 
    select info_id
    from cultivate_tb
    where course like '%course1%'
    union all
    select info_id
    from cultivate_tb
    where course like '%course2%'
    union all
    select info_id
    from cultivate_tb
    where course like '%course3%'
) t

题目二：最长连续登录天数

题目要求：

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

表结构：

drop table if exists tb_dau;
create table `tb_dau` (
    `fdate` date,
    `user_id` int
);
insert into tb_dau(fdate, user_id)
values 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-04', 10000);

运行结果示例：

思路：

        利用ROW_NUMBER 对每个user_id登录时长进行升序排序，获得每个用户每次登录的相对序列号。通过 DATEDIFF 函数，将登录日期和登录的相对序列号进行差分，找出连续的登录区间，统计每个连续区间内的天数，最后取最大值。

运行代码示例：

WITH consecutive_logins AS (
    SELECT 
        user_id,
        fdate,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS seq_num
    FROM tb_dau
    WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31' -- 限制在2023年1月内
),
login_groups AS (
    SELECT 
        user_id,
        fdate,
        seq_num,
        DATE_SUB(fdate, INTERVAL seq_num DAY) AS grp_id -- 计算组ID，利用 fdate 和 seq_num 的差值找到连续组
    FROM consecutive_logins
),
grouped_logins AS (
    SELECT 
        user_id,
        COUNT(*) AS consec_days -- 计算每个组的连续天数
    FROM login_groups
    GROUP BY user_id, grp_id
)
SELECT 
    user_id,
    MAX(consec_days) AS max_consec_days -- 获取最长的连续天数
FROM grouped_logins
GROUP BY user_id;

题目三：每个月Top3的周杰伦歌曲

题目要求：

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

表结构：

drop table if exists play_log;
create table `play_log` (
    `fdate` date,
    `user_id` int,
    `song_id` int
);
insert into play_log(fdate, user_id, song_id)
values 
('2022-01-08', 10000, 0),
('2022-01-16', 10000, 0),
('2022-01-20', 10000, 0),
('2022-01-25', 10000, 0),
('2022-01-02', 10000, 1),
('2022-01-12', 10000, 1),
('2022-01-13', 10000, 1),
('2022-01-14', 10000, 1),
('2022-01-10', 10000, 2),
('2022-01-11', 10000, 3),
('2022-01-16', 10000, 3),
('2022-01-11', 10000, 4),
('2022-01-27', 10000, 4),
('2022-02-05', 10000, 0),
('2022-02-19', 10000, 0),
('2022-02-07', 10000, 1),
('2022-02-27', 10000, 2),
('2022-02-25', 10000, 3),
('2022-02-03', 10000, 4),
('2022-02-16', 10000, 4);

drop table if exists song_info;
create table `song_info` (
    `song_id` int,
    `song_name` varchar(255),
    `singer_name` varchar(255)
);
insert into song_info(song_id, song_name, singer_name) 
values
(0, '明明就', '周杰伦'),
(1, '说好的幸福呢', '周杰伦'),
(2, '江南', '林俊杰'),
(3, '大笨钟', '周杰伦'),
(4, '黑键', '林俊杰');

drop table if exists user_info;
create table `user_info` (
    `user_id`   int,
    `age`       int
);
insert into user_info(user_id, age) 
values
(10000, 18)

运行结果示例：

思路：

        本题中，我们需要将用户的播放记录 play_log 表、歌曲信息 song_info 表和用户信息 user_info 表连接起来，并过滤掉不满足条件的记录，保留下18-25岁的用户，且歌手为“周杰伦”的数据。之后按月进行分组统计，并进行排序

运行代码示例：

WITH ranked_songs AS (
    SELECT 
        MONTH(pl.fdate) AS month,
        si.song_name,
        COUNT(*) AS play_pv,
        ROW_NUMBER() OVER (PARTITION BY MONTH(pl.fdate) ORDER BY COUNT(*) DESC, si.song_id ASC) AS ranking
    FROM play_log pl
    JOIN user_info ui ON pl.user_id = ui.user_id
    JOIN song_info si ON pl.song_id = si.song_id
    WHERE 
        ui.age BETWEEN 18 AND 25  -- 筛选18到25岁用户
        AND si.singer_name = '周杰伦'  -- 筛选周杰伦的歌曲
        AND YEAR(pl.fdate) = 2022  -- 筛选2022年的记录
    GROUP BY month, si.song_name, si.song_id
)
SELECT 
    month, 
    ranking, 
    song_name, 
    play_pv
FROM ranked_songs
WHERE ranking <= 3  
ORDER BY month, ranking;

题目四：查询连续登录的用户

题目要求：

请查询连续登陆不少于3天的新注册用户？

注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

要求：输出user_id并升序排序；

表结构：

用户注册信息表register_tb(user_id-用户id, reg_time-注册时间, reg_port-注册端口)，如下所示：

用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间,  log_port-登录端口)，如下所示：

运行结果示例：

思路：

        连续登录问题，思路可以之前讲解过的例题，这里我们换一种思路，不再使用窗口函数，而是使用表连接来做（如果要求连续登录天数过大则无法使用该方法，因此还是要掌握使用窗口函数的解法）。

运行代码示例：

select a.user_id
from login_tb AS a
inner join login_tb AS b
inner join login_tb AS c
inner join register_tb as r
on a.user_id = b.user_id and a.user_id = c.user_id and a.user_id = r.user_id
where datediff (a.log_time,b.log_time) = 1 and datediff (a.log_time,c.log_time) = 2 

题目五：查询培训指定课程的员工信息

题目要求：

请查询培训课程course3的员工信息？

注：只要培训的课程中包含course3课程就计入结果

要求输出：员工id、姓名，按照员工id升序排序；

表结构：

员工信息表staff_tb(staff_id-员工id,staff_name-员工姓名,staff_gender-员工性别,post-员工岗位类别,department-员工所在部门)，如下所示：

员工培训信息表cultivate_tb(info_id-信息id,staff_id-员工id,course-培训课程)，如下所示：

注：该公司共开设了三门课程，员工可自愿原则性培训0-3项；

运行结果示例：

思路：

        我们按照题目一的思路使用模糊查询找到所有参加课程3培训的员工id，再以该表作为子查询到员工表中查询员工信息即可。

运行代码示例：

select staff_id,staff_name
from staff_tb
where staff_id in (
    select staff_id from cultivate_tb where course like "%course3%"
)

题目六：每个商品的销售总额

题目要求：

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

表结构：

products示例表如下，包括product_id（商品编号）、name（商品名称）和category（商品类别）字段；

orders示例表如下，包括order_id（订单编号）、product_id（商品编号）、quantity（销售数量）和order_date（下单日期）字段；

运行结果示例：

思路：

        我们可以在子表中查询出每个商品的销售总量，再使用窗口函数根据类别进行排名。

运行代码示例：

select
name product_name,cnt as total_sales,row_number()over(partition by category order by cnt desc ) category_rank
from(
select
category,name,sum(quantity) cnt
from orders
inner join products using(product_id)
group by category,name
order by category,cnt desc
)t