社区首页 >专栏 >LeetCode面试SQL-获取最近第二次活动

LeetCode面试SQL-获取最近第二次活动

数据仓库晨曦

发布于 2024-10-09 13:22:51

12000

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

一、题目

表: t3_user_activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | string  |
| activity      | string  |
| start_date    | string  |
| end_date      | string  |
+---------------+---------+

该表不包含主键该表包含每个用户在一段时间内进行的活动的信息名为 username 的用户在 startDate 到 endDate 日内有一次活动

写一条SQL查询展示每一位用户 最近第二次 的活动

如果用户仅有一次活动，返回该活动.

一个用户不能同时进行超过一项活动，以任意顺序返回结果

下面是查询结果格式的例子：

t3_user_activity 表:

+-----------+-----------+-------------+-------------+
| username  | activity  | start_date  |  end_date   |
+-----------+-----------+-------------+-------------+
| Alice     | Travel    | 2020-02-12  | 2020-02-20  |
| Alice     | Dancing   | 2020-02-21  | 2020-02-23  |
| Alice     | Travel    | 2020-02-24  | 2020-02-28  |
| Bob       | Travel    | 2020-02-11  | 2020-02-18  |
+-----------+-----------+-------------+-------------+

Result:

+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+

Alice 最近第二次的活动是从 2020-02-24 到 2020-02-28 的旅行, 在此之前的 2020-02-21 到 2020-02-23 她进行了舞蹈 Bob 只有一条记录，我们就取这条记录

二、分析

本题难点在于：需要根据数据内容，决定取出时第二次还是仅有的一次。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

三、SQL

1.计算参加活动的次序

使用row_numberh函数开窗，计算出按照时间倒叙的排名，由于活动不能同时进行，所以用startDate或者endDate都可以 执行SQL

select username,
       activity,
       start_date,
       end_date,
       row_number() over (partition by username order by start_date desc) as rn
from t3_user_activity；

SQL结果

+-----------+-----------+-------------+-------------+-----+
| username  | activity  | start_date  |  end_date   | rn  |
+-----------+-----------+-------------+-------------+-----+
| Alice     | Travel    | 2020-02-24  | 2020-02-28  | 1   |
| Alice     | Dancing   | 2020-02-21  | 2020-02-23  | 2   |
| Alice     | Travel    | 2020-02-12  | 2020-02-20  | 3   |
| Bob       | Travel    | 2020-02-11  | 2020-02-18  | 1   |
+-----------+-----------+-------------+-------------+-----+

2.再次使用row_number开窗

先限制rn小于等于2，然后再次使用row_number进行开窗，根据用户分组，rn倒叙排列，得到rn1 执行SQL

select username,
       activity,
       start_date,
       end_date,
       rn,
       row_number() over (partition by username order by rn desc) as rn1
from (select username,
             activity,
             start_date,
             end_date,
             row_number() over (partition by username order by start_date desc) as rn
      from t3_user_activity) t
where rn <= 2

SQL结果

+-----------+-----------+-------------+-------------+-----+------+
| username  | activity  | start_date  |  end_date   | rn  | rn1  |
+-----------+-----------+-------------+-------------+-----+------+
| Alice     | Dancing   | 2020-02-21  | 2020-02-23  | 2   | 1    |
| Alice     | Travel    | 2020-02-24  | 2020-02-28  | 1   | 2    |
| Bob       | Travel    | 2020-02-11  | 2020-02-18  | 1   | 1    |
+-----------+-----------+-------------+-------------+-----+------+

3.限制rn1，取出最终结果

查看上面结果，我们发现rn1=1的数据即为想要的数据结果，限制查询出结果。

执行SQL

select username,
       activity,
       start_date,
       end_date
from (select username,
             activity,
             start_date,
             end_date,
             rn,
             row_number() over (partition by username order by rn desc) as rn1
      from (select username,
                   activity,
                   start_date,
                   end_date,
                   row_number() over (partition by username order by start_date desc) as rn
            from t3_user_activity) t
      where rn <= 2) tt
where rn1 = 1 ;

SQL结果

+-----------+-----------+-------------+-------------+
| username  | activity  | start_date  |  end_date   |
+-----------+-----------+-------------+-------------+
| Alice     | Dancing   | 2020-02-21  | 2020-02-23  |
| Bob       | Travel    | 2020-02-11  | 2020-02-18  |
+-----------+-----------+-------------+-------------+

四、建表语句和数据插入

--建表语句
CREATE TABLE t3_user_activity(
username string,
activity string,
start_date string,
end_date string
) COMMENT '用户活动表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入数据
insert into t3_user_activity(username,activity,start_date,end_date)
values
('Alice','Travel','2020-02-12','2020-02-20'),
('Alice','Dancing','2020-02-21','2020-02-23'),
('Alice','Travel','2020-02-24','2020-02-28'),
('Bob','Travel','2020-02-11','2020-02-18');