数据库"/>
力扣数据库
力扣数据库
sql
- 力扣数据库
- 2494. 合并在同一个大厅重叠的活动
- 二、2474购买量严格增加的客户
- 三、2394. 开除员工
- 2388. 将表中的空值更改为前一个值
- 2362. 生成发票
- 2346. 以百分比计算排名
- 2324. 产品销售分析 IV
- 2308. 按性别排列表格
- 2292. 连续两年有 3 个及以上订单的产品
- 618. 学生地理信息报告
- 601. 体育馆的人流量(连续天数问题)
- 569. 员工薪水中位数
- 571. 给定数字的频率查询中位数
- 579. 查询员工的累计薪水(近n个月的问题)
- 1127. 用户购买平台
- 1225. 报告系统状态的连续日期
- 1336. 每次访问的交易次数
- 1384. 按年度列出销售总额
- 1479. 周内每天的销售情况
- 1635. Hopper 公司查询 I
- 2752. 在连续天数上进行了最多交易次数的顾客
- 2720. 受欢迎度百分比( count()over()计算总数的用法)
- 2701. 连续递增交易(连续且增长问题)
- 2494. 合并在同一个大厅重叠的活动
- 2474. 购买量严格增加的客户
- 2362. 生成发票
- 2253. 动态取消表的旋转（动态列转行）
- 2252. 表的动态旋转(动态 行转列)
- 2199. 找到每篇文章的主题(数组炸裂，字符串切分)
- 2173. 最多连胜的次数(连续问题)
- 2153. 每辆车的乘客人数 II
- 1767. 寻找没有被执行的任务对
- 1892. 页面推荐Ⅱ
- 1917. Leetcodify 好友推荐
- 1919. 兴趣相同的朋友
- 1972. 同一天的第一个电话和最后一个电话
- 2004. 职员招聘人数
2494. 合并在同一个大厅重叠的活动
- 合并在同一个大厅重叠的活动
SQL架构
表: HallEvents+-------------+------+
| Column Name | Type |
+-------------+------+
| hall_id | int |
| start_day | date |
| end_day | date |
+-------------+------+
该表中没有主键。它可能包含重复字段。
该表的每一行表示活动的开始日期和结束日期，以及活动举行的大厅。编写一个 SQL 查询来合并在 同一个大厅举行 的所有重叠活动。如果两个活动 至少有一天 相同，那么它们就是重叠的。以任意顺序返回结果表。查询结果格式如下所示。示例 1:输入:
HallEvents 表:
+---------+------------+------------+
| hall_id | start_day | end_day |
+---------+------------+------------+
| 1 | 2023-01-13 | 2023-01-14 |
| 1 | 2023-01-14 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 2 | 2022-12-13 | 2022-12-17 |
| 3 | 2022-12-01 | 2023-01-30 |
+---------+------------+------------+
输出:
+---------+------------+------------+
| hall_id | start_day | end_day |
+---------+------------+------------+
| 1 | 2023-01-13 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 3 | 2022-12-01 | 2023-01-30 |
+---------+------------+------------+
解释: 有三个大厅。
大厅 1:
- 两个活动 ["2023-01-13", "2023-01-14"] 和 ["2023-01-14", "2023-01-17"] 重叠。我们将它们合并到一个活动中 ["2023-01-13", "2023-01-17"]。
- 活动 ["2023-01-18", "2023-01-25"] 不与任何其他活动重叠，所以我们保持原样。
大厅 2:
- 两个活动 ["2022-12-09", "2022-12-23"] 和 ["2022-12-13", "2022-12-17"] 重叠。我们将它们合并到一个活动中 ["2022-12-09", "2022-12-23"]。
大厅 3:
- 大厅只有一个活动，所以我们返回它。请注意，我们只分别考虑每个大厅的活动。
insert into leetcode.HallEvent values
(3, ‘2023-01-13’, ‘2023-01-31’), (2, ‘2023-01-04’, ‘2023-01-15’), (2, ‘2023-01-20’, ‘2023-01-22’), (2, ‘2022-12-08’, ‘2023-01-04’), (2, ‘2022-12-13’, ‘2023-01-26’), (3, ‘2022-12-14’, ‘2022-12-22’), (2, ‘2022-12-09’, ‘2023-01-05’), (3, ‘2023-01-05’, ‘2023-01-21’), (3, ‘2022-12-10’, ‘2023-01-26’), (3, ‘2022-12-04’, ‘2022-12-09’)
#通过排序获取最大的结束日期
#当起始日期大于上一条的最大日期后标记1，否则0
#将标记合并成组
#分组获组内最大和最小的值
select hall_id, min(start_day) start_day, max(end_day) end_day
from (select *, sum(range_start) over (partition by hall_id order by start_day) range_grpfrom (select *,if(start_day > lag(max_end_day_so_far) over (partition by hall_id order by start_day), 1,0) range_startfrom (select hall_id,start_day,end_day,max(end_day) over (partition by hall_id order by start_day) max_end_day_so_farfrom leetcode.HallEvent) a) b) c
group by hall_id, range_grp
二、2474购买量严格增加的客户
2474. 购买量严格增加的客户
SQL架构
表: Orders+--------------+------+
| Column Name | Type |
+--------------+------+
| order_id | int |
| customer_id | int |
| order_date | date |
| price | int |
+--------------+------+
order_id 是该表的主键。
每行包含订单的 id、订购该订单的客户 id、订单日期和价格。编写一个 SQL 查询，报告 总购买量 每年严格增加的客户 id。客户在一年内的 总购买量 是该年订单价格的总和。如果某一年客户没有下任何订单，我们认为总购买量为 0。
对于每个客户，要考虑的第一个年是他们 第一次下单 的年份。
对于每个客户，要考虑的最后一年是他们 最后一次下单 的年份。
以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Orders 表:
+----------+-------------+------------+-------+
| order_id | customer_id | order_date | price |
+----------+-------------+------------+-------+
| 1 | 1 | 2019-07-01 | 1100 |
| 2 | 1 | 2019-11-01 | 1200 |
| 3 | 1 | 2020-05-26 | 3000 |
| 4 | 1 | 2021-08-31 | 3100 |
| 5 | 1 | 2022-12-07 | 4700 |
| 6 | 2 | 2015-01-01 | 700 |
| 7 | 2 | 2017-11-07 | 1000 |
| 8 | 3 | 2017-01-01 | 900 |
| 9 | 3 | 2018-11-07 | 900 |
+----------+-------------+------------+-------+
输出:
+-------------+
| customer_id |
+-------------+
| 1 |
+-------------+
解释:
客户 1: 第一年是 2019 年，最后一年是 2022 年- 2019: 1100 + 1200 = 2300- 2020: 3000- 2021: 3100- 2022: 4700我们可以看到总购买量每年都在严格增加，所以我们在答案中包含了客户 1。客户 2: 第一年是2015年，最后一年是2017年- 2015: 700- 2016: 0- 2017: 1000我们没有把客户 2 包括在答案中，因为总的购买量并没有严格地增加。请注意，客户 2 在 2016 年没有购买任何物品。客户 3: 第一年是 2017 年，最后一年是 2018 年- 2017: 900- 2018: 900
insert into Orders_2474 values (‘1’, ‘1’, ‘2019-07-01’, ‘1100’), (‘2’, ‘1’, ‘2019-11-01’, ‘1200’), (‘3’, ‘1’, ‘2020-05-26’, ‘3000’), (‘4’, ‘1’, ‘2021-08-31’, ‘3100’), (‘5’, ‘1’, ‘2022-12-07’, ‘4700’), (‘6’, ‘2’, ‘2015-01-01’, ‘700’), (‘7’, ‘2’, ‘2017-11-07’, ‘1000’), (‘8’, ‘3’, ‘2017-01-01’, ‘900’), (‘9’, ‘3’, ‘2018-11-07’, ‘900’)
#首先药获取的是每个customer_id每年的统计
#然后使用开窗函数进行连续性计算，当年份排序减去价格排序如果连续结果是一致的
#rank的原因是剔除相等的数据
select customer_id
from (select customer_id, year, price_sum, year - rank() over (partition by customer_id order by price_sum) difffrom (select customer_id,year(order_date) year,sum(price) price_sumfrom Ordersgroup by customer_id, year(order_date)) b) c
group by customer_id
having count(distinct diff) = 1
三、2394. 开除员工
表: Employees+--------------+------+
| Column Name | Type |
+--------------+------+
| employee_id | int |
| needed_hours | int |
+--------------+------+
employee_id 是该表的主键。
每一行都包含员工的 id 和他们获得工资所需的最低工作时数。表: Logs+-------------+----------+
| Column Name | Type |
+-------------+----------+
| employee_id | int |
| in_time | datetime |
| out_time | datetime |
+-------------+----------+
(employee_id, in_time, out_time) 是该表的主键。
该表的每一行都显示了员工的时间戳。in_time 是员工开始工作的时间，out_time 是员工结束工作的时间。
所有时间都在 2022 年 10 月。out_time 可以是 in_time 之后的一天，这意味着该员工在午夜之后工作。在公司里，每个员工每个月必须工作一定的小时数。员工在工作段中工作。员工工作的小时数可以通过员工在所有工作段中工作的分钟数的总和来计算。每个工作段的分钟数是四舍五入的。例如，如果员工在一个时间段中工作了 51 分 2 秒，我们就认为它是 52 分钟。
编写一个 SQL 查询来报告将被开除的员工的 id。换句话说，报告没有工作所需时间的员工的 id。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Employees 表:
+-------------+--------------+
| employee_id | needed_hours |
+-------------+--------------+
| 1 | 20 |
| 2 | 12 |
| 3 | 2 |
+-------------+--------------+
Logs 表:
+-------------+---------------------+---------------------+
| employee_id | in_time | out_time |
+-------------+---------------------+---------------------+
| 1 | 2022-10-01 09:00:00 | 2022-10-01 17:00:00 |
| 1 | 2022-10-06 09:05:04 | 2022-10-06 17:09:03 |
| 1 | 2022-10-12 23:00:00 | 2022-10-13 03:00:01 |
| 2 | 2022-10-29 12:00:00 | 2022-10-29 23:58:58 |
+-------------+---------------------+---------------------+
输出:
+-------------+
| employee_id |
+-------------+
| 2 |
| 3 |
+-------------+
解释:
员工 1:- 参加了三个工作段:- 在 2022-10-01, 他工作了 8 个小时。- 在 2022-10-06, 他工作了 8 小时 4 分钟。- 在 2022-10-12, 他工作了 4 小时 1 分钟。请注意，他一直工作到午夜。- 员工 1 在各个时段总共工作了 20 小时5分钟，不被开除。
员工 2:- 参加了一个工作段:- 在 2022-10-29, 他工作了 11 小时 59 分钟。- 员工 2 没有工作足够的时长，将被开除。
员工 3:- 没有任何工作段。- 员工 3 没有工作足够的时长，将被开除。来源：力扣（LeetCode）
链接：
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
ceiling 向上取整
sum（out）-sum(in)可以获取到总的时间
unix_timestamp转换时间戳
select a.employee_id
from Employees a
left join Logs b on a.employee_id = b.employee_id
group by a.employee_id
having coalesce(ceiling((sum(unix_timestamp(out_time)) - sum(unix_timestamp(in_time))) / 60) / 60, 0) <
max(a.needed_hours)
INSERT INTO leetcode.Employees (employee_id, needed_hours) VALUES (1, 20);
INSERT INTO leetcode.Employees (employee_id, needed_hours) VALUES (2, 12);
INSERT INTO leetcode.Employees (employee_id, needed_hours) VALUES (3, 2);
INSERT INTO leetcode.Logs (employee_id, in_time, out_time) VALUES (1, ‘2022-10-01 09:00:00’, ‘2022-10-01 17:00:00’);
INSERT INTO leetcode.Logs (employee_id, in_time, out_time) VALUES (1, ‘2022-10-06 09:05:04’, ‘2022-10-06 17:09:03’);
INSERT INTO leetcode.Logs (employee_id, in_time, out_time) VALUES (1, ‘2022-10-12 23:00:00’, ‘2022-10-13 03:00:01’);
INSERT INTO leetcode.Logs (employee_id, in_time, out_time) VALUES (2, ‘2022-10-29 12:00:00’, ‘2022-10-29 23:58:58’);
2388. 将表中的空值更改为前一个值
2388. 将表中的空值更改为前一个值
SQL架构
表: CoffeeShop+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| drink | varchar |
+-------------+---------+
id 是该表的主键。
该表中的每一行都显示了订单 id 和所点饮料的名称。一些饮料行为 null。编写一个 SQL 查询，将 drink 的 null 值替换为前面最近一行不为 null 的 drink。保证表第一行的 drink 不为 null。返回 与输入顺序相同的 结果表。查询结果格式示例如下。示例 1:输入:
CoffeeShop 表:
+----+------------------+
| id | drink |
+----+------------------+
| 9 | Mezcal Margarita |
| 6 | null |
| 7 | null |
| 3 | Americano |
| 1 | Daiquiri |
| 2 | null |
+----+------------------+
输出:
+----+------------------+
| id | drink |
+----+------------------+
| 9 | Mezcal Margarita |
| 6 | Mezcal Margarita |
| 7 | Mezcal Margarita |
| 3 | Americano |
| 1 | Daiquiri |
| 2 | Daiquiri |
+----+------------------+
解释:
对于 ID 6，之前不为空的值来自 ID 9。我们将 null 替换为 “Mezcal Margarita”。
对于 ID 7，之前不为空的值来自 ID 9。我们将 null 替换为 “Mezcal Margarita”。
对于 ID 2，之前不为空的值来自 ID 1。我们将 null 替换为 “Daiquiri”。
请注意，输出中的行与输入中的行相同。
使用临时变量 :=对临时变量尽心赋值
select id,@temp:=if(drink is null ,@temp,drink) as drink
from CoffeeShop ;
insert into CoffeeShop values
(‘9’, ‘Mezcal Margarita’), (‘6’, null), (‘7’, null), (‘3’, ‘Americano’), (‘1’, ‘Daiquiri’), (‘2’, null)
2362. 生成发票
SQL架构
表: Products+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id | int |
| price | int |
+-------------+------+
product_id 是该表的主键。
该表中的每一行显示了一个产品的 ID 和一个单位的价格。表: Purchases+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id | int |
| product_id | int |
| quantity | int |
+-------------+------+
(invoice_id, product_id) 是该表的主键
该表中的每一行都显示了从发票中的一种产品订购的数量。编写一个 SQL 查询来显示价格最高的发票的详细信息。如果两个或多个发票具有相同的价格，则返回 invoice_id 最小的发票的详细信息。以 任意顺序 返回结果表。查询结果格式示例如下。示例 1:输入:
Products 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1 | 100 |
| 2 | 200 |
+------------+-------+
Purchases 表:
+------------+------------+----------+
| invoice_id | product_id | quantity |
+------------+------------+----------+
| 1 | 1 | 2 |
| 3 | 2 | 1 |
| 2 | 2 | 3 |
| 2 | 1 | 4 |
| 4 | 1 | 10 |
+------------+------------+----------+
输出:
+------------+----------+-------+
| product_id | quantity | price |
+------------+----------+-------+
| 2 | 3 | 600 |
| 1 | 4 | 400 |
+------------+----------+-------+
解释:
发票 1: price = (2 * 100) = $200
发票 2: price = (4 * 100) + (3 * 200) = $1000
发票 3: price = (1 * 200) = $200
发票 4: price = (10 * 100) = $1000
最高价格是 1000 美元，最高价格的发票是 2 和 4。
我们返回 ID 最小的发票 2 的详细信息
建表语句
– auto-generated definition
create table Products
(
product_id int null,
price int null
);
INSERT INTO leetcode.Products (product_id, price) VALUES (1, 100);
INSERT INTO leetcode.Products (product_id, price) VALUES (2, 200);
– auto-generated definition
create table Purchases
(
invoice_id int null,
product_id int null,
quantity int null
);
INSERT INTO leetcode.Purchases (invoice_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO leetcode.Purchases (invoice_id, product_id, quantity) VALUES (3, 2, 1);
INSERT INTO leetcode.Purchases (invoice_id, product_id, quantity) VALUES (2, 2, 3);
INSERT INTO leetcode.Purchases (invoice_id, product_id, quantity) VALUES (2, 1, 4);
INSERT INTO leetcode.Purchases (invoice_id, product_id, quantity) VALUES (4, 1, 10);
with tmp1 as ( select invoice_id,p.product_id,quantity,price,sum(quantity*p.price)over(partition by invoice_id) sum_invo
from Products p left join Purchases pu on p.product_id=pu.product_id )
select product_id,quantity,quantity*price price
from (select *, rank() over (order by invoice_id) rwfrom tmp1where sum_invo = (select max(sum_invo) from tmp1)) a
where a.rw=1
2346. 以百分比计算排名
SQL架构
表: Students+---------------+------+
| Column Name | Type |
+---------------+------+
| student_id | int |
| department_id | int |
| mark | int |
+---------------+------+
student_id 是该表的主键。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。编写一个 SQL 查询，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。 percentage 应该 四舍五入到小数点后两位。 student_rank_in_the_department 由 mark 的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Students 表:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2 | 2 | 650 |
| 8 | 2 | 650 |
| 7 | 1 | 920 |
| 1 | 1 | 610 |
| 3 | 1 | 530 |
+------------+---------------+------+
输出:
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7 | 1 | 0.0 |
| 1 | 1 | 50.0 |
| 3 | 1 | 100.0 |
| 2 | 2 | 0.0 |
| 8 | 2 | 0.0 |
+------------+---------------+------------+
解释:
对于院系 1:- 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0- 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0- 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0
对于院系 2:- 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0- 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
– auto-generated definition
create table Students
(
student_id int null,
department_id int null,
mark int null
);
insert into Students
values
(‘8’, ‘4’, ‘860’), (‘13’, ‘3’, ‘40’), (‘3’, ‘1’, ‘130’), (‘9’, ‘3’, ‘40’), (‘2’, ‘3’, ‘20’), (‘12’, ‘3’, ‘390’), (‘4’, ‘2’, ‘270’), (‘6’, ‘4’, ‘670’), (‘11’, ‘2’, ‘150’), (‘7’, ‘2’, ‘130’), (‘14’, ‘3’, ‘790’), (‘10’, ‘1’, ‘900’), (‘1’, ‘4’, ‘910’), (‘5’, ‘1’, ‘610’)
select student_id, department_id, coalesce(round((rw - 1) * 100 / (cut - 1), 2), 0.0) percentage
from (select student_id,department_id,rank() over (partition by department_id order by mark desc ) rw,count(student_id) over (partition by department_id) cutfrom Students) a
2324. 产品销售分析 IV
2324. 产品销售分析 IV
SQL架构
表: Sales+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| user_id | int |
| quantity | int |
+-------------+-------+
sale_id 是该表的主键。
product_id 是 product 表的外键。
该表的每一行都显示了产品的 ID 和用户购买的数量。表: Product+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id | int |
| price | int |
+-------------+------+
product_id 是该表的主键
该表的每一行都表示每种产品的价格。编写一个 SQL 查询，为每个用户获取其消费最多的产品 id。如果同一用户在两个或多个产品上花费了最多的钱，请获取所有花费了最多的钱的产品。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Sales 表:
+---------+------------+---------+----------+
| sale_id | product_id | user_id | quantity |
+---------+------------+---------+----------+
| 1 | 1 | 101 | 10 |
| 2 | 3 | 101 | 7 |
| 3 | 1 | 102 | 9 |
| 4 | 2 | 102 | 6 |
| 5 | 3 | 102 | 10 |
| 6 | 1 | 102 | 6 |
+---------+------------+---------+----------+
Product 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1 | 10 |
| 2 | 25 |
| 3 | 15 |
+------------+-------+
输出:
+---------+------------+
| user_id | product_id |
+---------+------------+
| 101 | 3 |
| 102 | 1 |
| 102 | 2 |
| 102 | 3 |
+---------+------------+
解释:
用户 101:- 在产品 1 上花费 10 * 10 = 100。- 在产品 3 上花费 7 * 15 = 105。
用户101在产品3上花的钱最多。
用户 102:- 在产品 1 上花费 (9 + 7)* 10 = 150- 在产品 2 上花费 6 * 25 = 150- 在产品 3 上花费 10 * 15 = 150。
用户 102 在产品 1、2、3 上花的钱最多。
– auto-generated definition
create table Sales
(
sale_id int null,
product_id int null,
user_id int null,
quantity int null
);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (1, 1, 101, 10);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (3, 1, 102, 9);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (6, 1, 102, 6);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (4, 2, 102, 6);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (2, 3, 101, 7);
INSERT INTO leetcode.Sales (sale_id, product_id, user_id, quantity) VALUES (5, 3, 102, 10);
– auto-generated definition
create table Product
(
product_id int null,
price int null
);
INSERT INTO leetcode.Product (product_id, price) VALUES (1, 10);
INSERT INTO leetcode.Product (product_id, price) VALUES (2, 25);
INSERT INTO leetcode.Product (product_id, price) VALUES (3, 15);
select user_id ,product_id
from (select user_id,product_id,sum(cut) sum_sut,rank() over (partition by user_id order by sum(cut) desc ) rw
from (select s.user_id,s.product_id,sum(quantity*p.price)over(partition by user_id,sale_id) cut
from Sales s left join Product p on s.product_id=p.product_id)a group by user_id, product_id) a where rw=1;
2308. 按性别排列表格
2308. 按性别排列表格
SQL架构
表: Genders+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| gender | varchar |
+-------------+---------+
user_id 是该表的主键。
gender 的值是 'female', 'male','other' 之一。
该表中的每一行都包含用户的 ID 及其性别。
表格中 'female', 'male','other' 数量相等。编写一个SQL查询以重新排列 Genders 表，使行按顺序在 'female', 'other' 和 'male' 之间交替。同时每种性别按照 user_id 升序进行排序。
按 上述顺序 返回结果表。
查询结果格式如以下示例所示。示例 1:输入:
Genders 表:
+---------+--------+
| user_id | gender |
+---------+--------+
| 4 | male |
| 7 | female |
| 2 | other |
| 5 | male |
| 3 | female |
| 8 | male |
| 6 | other |
| 1 | other |
| 9 | female |
+---------+--------+
输出:
+---------+--------+
| user_id | gender |
+---------+--------+
| 3 | female |
| 1 | other |
| 4 | male |
| 7 | female |
| 2 | other |
| 5 | male |
| 9 | female |
| 6 | other |
| 8 | male |
+---------+--------+
解释:
女性：ID 3、7、9。
其他性别：ID 1、2、6。
男性：ID 4、5、8。
我们在 'female', 'other','male' 之间交替排列表。
注意，每种性别都是按 user_id 升序排序的。
select user_id,gender
from (select user_id,gender,row_number() over (partition by gender order by user_id) rw
from Genders) a order by rw,gender ;
– auto-generated definition
create table Genders
(
user_id int null,
gender varchar(255) null
);
insert into Genders values
(‘15’, ‘other’), (‘12’, ‘female’), (‘1’, ‘other’), (‘2’, ‘female’), (‘21’, ‘other’), (‘5’, ‘male’), (‘20’, ‘male’), (‘14’, ‘other’), (‘11’, ‘male’), (‘4’, ‘male’), (‘19’, ‘male’), (‘18’, ‘other’), (‘6’, ‘other’), (‘9’, ‘female’), (‘3’, ‘female’), (‘8’, ‘female’), (‘10’, ‘male’), (‘7’, ‘other’), (‘16’, ‘male’), (‘13’, ‘female’), (‘17’, ‘female’)
2292. 连续两年有 3 个及以上订单的产品
2292. 连续两年有 3 个及以上订单的产品
SQL架构
表: Orders+---------------+------+
| Column Name | Type |
+---------------+------+
| order_id | int |
| product_id | int |
| quantity | int |
| purchase_date | date |
+---------------+------+
order_id 是该表的主键。
该表中的每一行都包含订单 ID、购买的产品 ID、数量和购买日期。编写一个 SQL 查询，获取连续两年订购三次或三次以上的所有产品的 id。以 任意顺序 返回结果表。查询结果格式示例如下。示例 1:输入:
Orders 表:
+----------+------------+----------+---------------+
| order_id | product_id | quantity | purchase_date |
+----------+------------+----------+---------------+
| 1 | 1 | 7 | 2020-03-16 |
| 2 | 1 | 4 | 2020-12-02 |
| 3 | 1 | 7 | 2020-05-10 |
| 4 | 1 | 6 | 2021-12-23 |
| 5 | 1 | 5 | 2021-05-21 |
| 6 | 1 | 6 | 2021-10-11 |
| 7 | 2 | 6 | 2022-10-11 |
+----------+------------+----------+---------------+
输出:
+------------+
| product_id |
+------------+
| 1 |
+------------+
解释:
产品 1 在 2020 年和 2021 年都分别订购了三次。由于连续两年订购了三次，所以我们将其包含在答案中。
产品 2 在 2022 年订购了一次。我们不把它包括在答案中。
select product_id
from (
select product_id,year(purchase_date) year_date,row_number() over (partition by product_id order by year(purchase_date)) rw
from Orders group by product_id,year(purchase_date) having count(1)>=3
) a group by product_id,year_date-rw having count(1)>=2
– auto-generated definition
create table Orders
(
order_id int null,
product_id int null,
quantity int null,
purchase_date date null
)
collate = utf8_bin;
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (1, 1, 7, ‘2020-03-16’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (2, 1, 4, ‘2020-12-02’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (3, 1, 7, ‘2020-05-10’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (4, 1, 6, ‘2021-12-23’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (5, 1, 5, ‘2021-05-21’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (6, 1, 6, ‘2021-10-11’);
INSERT INTO leetcode.Orders (order_id, product_id, quantity, purchase_date) VALUES (7, 2, 6, ‘2022-10-11’);
618. 学生地理信息报告
618. 学生地理信息报告
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表： student +-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
+-------------+---------+
该表可能包含重复的行。
该表的每一行表示学生的名字和他们来自的大陆。一所学校有来自亚洲、欧洲和美洲的学生。编写解决方案实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。测试用例的生成保证来自美国的学生人数不少于亚洲或欧洲的学生人数。返回结果格式如下所示。示例 1:输入:
Student table:
+--------+-----------+
| name | continent |
+--------+-----------+
| Jane | America |
| Pascal | Europe |
| Xi | Asia |
| Jack | America |
+--------+-----------+
输出:
+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack | Xi | Pascal |
| Jane | null | null |
+---------+------+--------+
select
max(if(continent=‘America’ ,name ,null )) America,
max(if(continent=‘Asia’ ,name ,null )) Asia,
max(if(continent=‘Europe’ ,name ,null )) Europe
from (
select
name ,
continent,
row_number() over (partition by continent order by continent desc ,name ) rk
from student
) a group by rk;
行转列问题：首先要确定哪些是同一行的数据，上个题中使用开窗函数将同一行的数据打出标记，在外层groupby将不同行数据分开，加上if 筛选出指定字段的数据。
601. 体育馆的人流量(连续天数问题)
601. 体育馆的人流量
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是该表中具有唯一值的列。
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。返回按 visit_date 升序排列 的结果表。查询结果格式如下所示。示例 1:输入：
Stadium 表:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
输出：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
解释：
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int)
Truncate table Stadium
insert into Stadium (id, visit_date, people) values (‘1’, ‘2017-01-01’, ‘10’)
insert into Stadium (id, visit_date, people) values (‘2’, ‘2017-01-02’, ‘109’)
insert into Stadium (id, visit_date, people) values (‘3’, ‘2017-01-03’, ‘150’)
insert into Stadium (id, visit_date, people) values (‘4’, ‘2017-01-04’, ‘99’)
insert into Stadium (id, visit_date, people) values (‘5’, ‘2017-01-05’, ‘145’)
insert into Stadium (id, visit_date, people) values (‘6’, ‘2017-01-06’, ‘1455’)
insert into Stadium (id, visit_date, people) values (‘7’, ‘2017-01-07’, ‘199’)
insert into Stadium (id, visit_date, people) values (‘8’, ‘2017-01-09’, ‘188’)
select id,visit_date,people
from (select id,
people,
visit_date,
count(1) over (partition by id - rw ) ct
from (SELECT id,
people,
visit_date,
ROW_NUMBER() OVER (ORDER BY visit_date) AS rw
FROM Stadium
WHERE people >= 100) a) b
where b.ct>=3;
连续天数问题，使用双层开窗。第一层需要满足条件的日期和连续自然日日期或者是上面两个值对应的id序号。
第二层是要满足条件的序号和满足条件的自然日序号相减获取对应的count，如果满足条件的日期是连续的那么他们进行相减是会获取到同样的值，所以到最后就使用where过滤成满足》=n的数据就可以了。
569. 员工薪水中位数
表: Employee+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| company | varchar |
| salary | int |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示公司和一名员工的工资。编写解决方案，找出每个公司的工资中位数。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Employee 表:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1 | A | 2341 |
| 2 | A | 341 |
| 3 | A | 15 |
| 4 | A | 15314 |
| 5 | A | 451 |
| 6 | A | 513 |
| 7 | B | 15 |
| 8 | B | 13 |
| 9 | B | 1154 |
| 10 | B | 1345 |
| 11 | B | 1221 |
| 12 | B | 234 |
| 13 | C | 2345 |
| 14 | C | 2645 |
| 15 | C | 2645 |
| 16 | C | 2652 |
| 17 | C | 65 |
+----+---------+--------+
输出:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5 | A | 451 |
| 6 | A | 513 |
| 12 | B | 234 |
| 9 | B | 1154 |
| 14 | C | 2645 |
+----+---------+--------+进阶: 你能在不使用任何内置函数或窗口函数的情况下解决它吗?
Create table If Not Exists Employee (id int, company varchar(255), salary int)
Truncate table Employee
insert into Employee (id, company, salary) values (‘1’, ‘A’, ‘2341’)
insert into Employee (id, company, salary) values (‘2’, ‘A’, ‘341’)
insert into Employee (id, company, salary) values (‘3’, ‘A’, ‘15’)
insert into Employee (id, company, salary) values (‘4’, ‘A’, ‘15314’)
insert into Employee (id, company, salary) values (‘5’, ‘A’, ‘451’)
insert into Employee (id, company, salary) values (‘6’, ‘A’, ‘513’)
insert into Employee (id, company, salary) values (‘7’, ‘B’, ‘15’)
insert into Employee (id, company, salary) values (‘8’, ‘B’, ‘13’)
insert into Employee (id, company, salary) values (‘9’, ‘B’, ‘1154’)
insert into Employee (id, company, salary) values (‘10’, ‘B’, ‘1345’)
insert into Employee (id, company, salary) values (‘11’, ‘B’, ‘1221’)
insert into Employee (id, company, salary) values (‘12’, ‘B’, ‘234’)
insert into Employee (id, company, salary) values (‘13’, ‘C’, ‘2345’)
insert into Employee (id, company, salary) values (‘14’, ‘C’, ‘2645’)
insert into Employee (id, company, salary) values (‘15’, ‘C’, ‘2645’)
insert into Employee (id, company, salary) values (‘16’, ‘C’, ‘2652’)
insert into Employee (id, company, salary) values (‘17’, ‘C’, ‘65’)
select * from (select id ,
company,
salary ,
row_number() over (partition by company order by salary desc,id desc ) rw,
count(1) over (partition by company ) ct
from Employee) a
where rw in (floor((ct+1) /2),floor((ct+2)/2))
571. 给定数字的频率查询中位数
Numbers 表：+-------------+------+
| Column Name | Type |
+-------------+------+
| num | int |
| frequency | int |
+-------------+------+
num 是这张表的主键(具有唯一值的列)。
这张表的每一行表示某个数字在该数据库中的出现频率。中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写解决方案，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。返回结果如下例所示。示例 1：输入：
Numbers 表：
+-----+-----------+
| num | frequency |
+-----+-----------+
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+-----+-----------+
输出：
+--------+
| median |
+--------+
| 0.0 |
+--------+
解释：
如果解压这个 Numbers 表，可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3] ，所以中位数是 (0 + 0) / 2 = 0
Create table If Not Exists Numbers (num int, frequency int)
Truncate table Numbers
insert into Numbers (num, frequency) values (‘0’, ‘7’)
insert into Numbers (num, frequency) values (‘1’, ‘1’)
insert into Numbers (num, frequency) values (‘2’, ‘3’)
insert into Numbers (num, frequency) values (‘3’, ‘1’)
with recursive num_recursive as (
select num ,frequency ,1 as partition_num from Numbers
union all
select num,frequency-1,1 as partition_num from num_recursive where frequency >1
)
select round(sum(num)/count(1) ,1) from (
select num, count(1) over (partition by partition_num) ct,
row_number() over (partition by partition_num order by num ) rw
from num_recursive
) a where rw in (floor((ct+1)/2),floor((ct+2)/2))
解压数据使用递归，解析完成后然后就是中位数的正常计算。
579. 查询员工的累计薪水(近n个月的问题)
表：Employee+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| month | int |
| salary | int |
+-------------+------+
(id, month) 是该表的主键(具有唯一值的列的组合)。
表中的每一行表示 2020 年期间员工一个月的工资。编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。员工的 累计工资汇总 可以计算如下:对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。结果格式如下所示。示例 1输入：
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
| 1 | 7 | 90 |
| 1 | 8 | 90 |
+----+-------+--------+
输出：
+----+-------+--------+
| id | month | Salary |
+----+-------+--------+
| 1 | 7 | 90 |
| 1 | 4 | 130 |
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
+----+-------+--------+
解释：
员工 “1” 有 5 条工资记录，不包括最近一个月的 “8”:
- 第 '7' 个月为 90。
- 第 '4' 个月为 60。
- 第 '3' 个月是 40。
- 第 '2' 个月为 30。
- 第 '1' 个月为 20。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 7 | 90 | (90 + 0 + 0)
| 1 | 4 | 130 | (60 + 40 + 30)
| 1 | 3 | 90 | (40 + 30 + 20)
| 1 | 2 | 50 | (30 + 20 + 0)
| 1 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+
请注意，'7' 月的 3 个月的总和是 90，因为他们没有在 '6' 月或 '5' 月工作。员工 '2' 只有一个工资记录('1' 月)，不包括最近的 '2' 月。
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 2 | 1 | 20 | (20 + 0 + 0)
+----+-------+--------+员工 '3' 有两个工资记录，不包括最近一个月的 '4' 月:
- 第 '3' 个月为 60 。
- 第 '2' 个月是 40。
因此，该员工的累计工资汇总为:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 3 | 3 | 100 | (60 + 40 + 0)
| 3 | 2 | 40 | (40 + 0 + 0)
+----+-------+--------+Create table If Not Exists Employee (id int, month int, salary int)
Truncate table Employee
insert into Employee (id, month, salary) values (‘1’, ‘1’, ‘20’)
insert into Employee (id, month, salary) values (‘2’, ‘1’, ‘20’)
insert into Employee (id, month, salary) values (‘1’, ‘2’, ‘30’)
insert into Employee (id, month, salary) values (‘2’, ‘2’, ‘30’)
insert into Employee (id, month, salary) values (‘3’, ‘2’, ‘40’)
insert into Employee (id, month, salary) values (‘1’, ‘3’, ‘40’)
insert into Employee (id, month, salary) values (‘3’, ‘3’, ‘60’)
insert into Employee (id, month, salary) values (‘1’, ‘4’, ‘60’)
insert into Employee (id, month, salary) values (‘3’, ‘4’, ‘70’)
insert into Employee (id, month, salary) values (‘1’, ‘7’, ‘90’)
insert into Employee (id, month, salary) values (‘1’, ‘8’, ‘90’)
select id,
month,
total salary
from (select id,
month,
salary,
sum(salary) over (partition by id order by month range 2 preceding) total,
row_number() over (partition by id order by month desc ) rk
from Employee_2) a
where rk > 1
order by id, month desc
jinn个月的问题，使用 开窗函数的range rows， rows是对于行来说，会获取近几行的数据，适合无视断月。
range适合时间和数值，是会计算值对应的前n条，处理连续月如果断月计算成0的问题。
1127. 用户购买平台
1127. 用户购买平台
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
支出表: Spending+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| spend_date | date |
| platform | enum |
| amount | int |
+-------------+---------+
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。
(user_id, spend_date, platform) 是这张表的主键(具有唯一值的列的组合)。
平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。以 任意顺序 返回结果表。返回结果格式如下例所示：示例 1：输入：
Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1 | 2019-07-01 | mobile | 100 |
| 1 | 2019-07-01 | desktop | 100 |
| 2 | 2019-07-01 | mobile | 100 |
| 2 | 2019-07-02 | mobile | 100 |
| 3 | 2019-07-01 | desktop | 100 |
| 3 | 2019-07-02 | desktop | 100 |
+---------+------------+----------+--------+
输出：
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop | 100 | 1 |
| 2019-07-01 | mobile | 100 | 1 |
| 2019-07-01 | both | 200 | 1 |
| 2019-07-02 | desktop | 100 | 1 |
| 2019-07-02 | mobile | 100 | 1 |
| 2019-07-02 | both | 0 | 0 |
+------------+----------+--------------+-------------+
解释：
在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买，而用户3 仅 使用了桌面端购买。
在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。
select spend_date,b.platform,
sum(if(a.platform=b.platform,sum_at,0)) total_amount ,
count(if(a.platform=b.platform,1,null)) total_users
from (select user_id,
spend_date,
if(count(distinct platform)>=2,‘both’,max(platform)) platform,
sum(amount) sum_at
from Spending group by user_id,spend_date) a,
(
select ‘both’ platform
union all
select ‘mobile’
union all
select ‘desktop’
) b
group by a.spend_date,b.platform
Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM(‘desktop’, ‘mobile’), amount int)
Truncate table Spending
insert into Spending (user_id, spend_date, platform, amount) values (‘1’, ‘2019-07-01’, ‘mobile’, ‘100’)
insert into Spending (user_id, spend_date, platform, amount) values (‘1’, ‘2019-07-01’, ‘desktop’, ‘100’)
insert into Spending (user_id, spend_date, platform, amount) values (‘2’, ‘2019-07-01’, ‘mobile’, ‘100’)
insert into Spending (user_id, spend_date, platform, amount) values (‘2’, ‘2019-07-02’, ‘mobile’, ‘100’)
insert into Spending (user_id, spend_date, platform, amount) values (‘3’, ‘2019-07-01’, ‘desktop’, ‘100’)
insert into Spending (user_id, spend_date, platform, amount) values (‘3’, ‘2019-07-02’, ‘desktop’, ‘100’)
这个问题涉及到了行炸裂，可以使用一个固定行表将实现行的数据sql，
同时使用sum（）聚合函数会将符合条件的数据进行汇总行
1225. 报告系统状态的连续日期
表：Failed+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
该表主键为 fail_date (具有唯一值的列)。
该表包含失败任务的天数.表： Succeeded+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
该表主键为 success_date (具有唯一值的列)。
该表包含成功任务的天数.系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。最后结果按照起始日期 start_date 排序返回结果样例如下所示:示例 1：输入：
Failed table:
+-------------------+
| fail_date |
+-------------------+
| 2018-12-28 |
| 2018-12-29 |
| 2019-01-04 |
| 2019-01-05 |
+-------------------+
Succeeded table:
+-------------------+
| success_date |
+-------------------+
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-06 |
+-------------------+
输出：
+--------------+--------------+--------------+
| period_state | start_date | end_date |
+--------------+--------------+--------------+
| succeeded | 2019-01-01 | 2019-01-03 |
| failed | 2019-01-04 | 2019-01-05 |
| succeeded | 2019-01-06 | 2019-01-06 |
+--------------+--------------+--------------+
解释：
结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。
Create table If Not Exists Failed (fail_date date)
Create table If Not Exists Succeeded (success_date date)
Truncate table Failed
insert into Failed (fail_date) values (‘2018-12-28’)
insert into Failed (fail_date) values (‘2018-12-29’)
insert into Failed (fail_date) values (‘2019-01-04’)
insert into Failed (fail_date) values (‘2019-01-05’)
Truncate table Succeeded
insert into Succeeded (success_date) values (‘2018-12-30’)
insert into Succeeded (success_date) values (‘2018-12-31’)
insert into Succeeded (success_date) values (‘2019-01-01’)
insert into Succeeded (success_date) values (‘2019-01-02’)
insert into Succeeded (success_date) values (‘2019-01-03’)
insert into Succeeded (success_date) values (‘2019-01-06’)
select type period_state ,min(data_date) start_date,max(data_date) end_date from (
select
type,
data_date,
row_number() over (partition by type order by data_date) rw
from (select ‘succeeded’ as type,success_date data_date from Succeeded
union all
select ‘failed’ as type ,fail_date data_date from Failed ) a
where data_date between ‘2019-01-01’ and ‘2019-12-31’
) a
group by type,date_add(data_date ,interval -rw day ) order by min(data_date)
连续问题，内层获取自然的排序，外层对日期和自然的排序group by 差
1336. 每次访问的交易次数
表: Visits+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| visit_date | date |
+---------------+---------+
(user_id, visit_date) 是该表的主键(具有唯一值的列的组合)
该表的每行表示 user_id 在 visit_date 访问了银行表: Transactions+------------------+---------+
| Column Name | Type |
+------------------+---------+
| user_id | int |
| transaction_date | date |
| amount | int |
+------------------+---------+
该表可能有重复行
该表的每一行表示 user_id 在 transaction_date 完成了一笔 amount 数额的交易
可以保证用户 (user) 在 transaction_date 访问了银行 (也就是说 Visits 表包含 (user_id, transaction_date) 行)银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表编写解决方案找出多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等结果包含两列：transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count) 结果按 transactions_count 排序下面是返回结果格式的例子：示例 1：输入：
Visits 表:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1 | 2020-01-01 |
| 2 | 2020-01-02 |
| 12 | 2020-01-01 |
| 19 | 2020-01-03 |
| 1 | 2020-01-02 |
| 2 | 2020-01-03 |
| 1 | 2020-01-04 |
| 7 | 2020-01-11 |
| 9 | 2020-01-25 |
| 8 | 2020-01-28 |
+---------+------------+
Transactions 表:
+---------+------------------+--------+
| user_id | transaction_date | amount |
+---------+------------------+--------+
| 1 | 2020-01-02 | 120 |
| 2 | 2020-01-03 | 22 |
| 7 | 2020-01-11 | 232 |
| 1 | 2020-01-04 | 7 |
| 9 | 2020-01-25 | 33 |
| 9 | 2020-01-25 | 66 |
| 8 | 2020-01-28 | 1 |
| 9 | 2020-01-25 | 99 |
+---------+------------------+--------+
输出：
+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0 | 4 |
| 1 | 5 |
| 2 | 0 |
| 3 | 1 |
+--------------------+--------------+
解释：为这个例子绘制的图表如上所示
* 对于 transactions_count = 0, visits 中 (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") 和 (19, "2020-01-03") 没有进行交易，所以 visits_count = 4 。
* 对于 transactions_count = 1, visits 中 (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") 和 (1, "2020-01-04") 进行了一次交易，所以 visits_count = 5 。
* 对于 transactions_count = 2, 没有客户访问银行进行了两次交易，所以 visits_count = 0 。
* 对于 transactions_count = 3, visits 中 (9, "2020-01-25") 进行了三次交易，所以 visits_count = 1 。
* 对于 transactions_count >= 4, 没有客户访问银行进行了超过3次交易，所以我们停止在 transactions_count = 3 。
Create table If Not Exists Visits (user_id int, visit_date date)
Create table If Not Exists Transactions (user_id int, transaction_date date, amount int)
Truncate table Visits
insert into Visits (user_id, visit_date) values (‘1’, ‘2020-01-01’)
insert into Visits (user_id, visit_date) values (‘2’, ‘2020-01-02’)
insert into Visits (user_id, visit_date) values (‘12’, ‘2020-01-01’)
insert into Visits (user_id, visit_date) values (‘19’, ‘2020-01-03’)
insert into Visits (user_id, visit_date) values (‘1’, ‘2020-01-02’)
insert into Visits (user_id, visit_date) values (‘2’, ‘2020-01-03’)
insert into Visits (user_id, visit_date) values (‘1’, ‘2020-01-04’)
insert into Visits (user_id, visit_date) values (‘7’, ‘2020-01-11’)
insert into Visits (user_id, visit_date) values (‘9’, ‘2020-01-25’)
insert into Visits (user_id, visit_date) values (‘8’, ‘2020-01-28’)
Truncate table Transactions
insert into Transactions (user_id, transaction_date, amount) values (‘1’, ‘2020-01-02’, ‘120’)
insert into Transactions (user_id, transaction_date, amount) values (‘2’, ‘2020-01-03’, ‘22’)
insert into Transactions (user_id, transaction_date, amount) values (‘7’, ‘2020-01-11’, ‘232’)
insert into Transactions (user_id, transaction_date, amount) values (‘1’, ‘2020-01-04’, ‘7’)
insert into Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25’, ‘33’)
insert into Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25’, ‘66’)
insert into Transactions (user_id, transaction_date, amount) values (‘8’, ‘2020-01-28’, ‘1’)
insert into Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25’, ‘99’)
with tmp as (
select count(transaction_date) cnt
from Visits a
left join Transactions b
on a.user_id = b.user_id and a.visit_date = b.transaction_date
group by a.user_id,visit_date
)
select trans_cnt transactions_count ,count(cnt) visits_count from ( with recursive trans_list as (
select 0 trans_cnt
union all
select trans_cnt+1 from trans_list where trans_cnt+1<=(select count(1) cnt from Transactions group by user_id,transaction_date order by count(1) desc limit 1 )
) select * from trans_list)a left join tmp b on a.trans_cnt=bt
group by trans_cnt
该题目是计算访问和交易都不确定的计数，要递归出所有的交易次数可能，然后计算出有交易的数据条数，两边进行关联，没有交易的自然就是0了
1384. 按年度列出销售总额
Product 表：+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
+---------------+---------+
product_id 是这张表的主键(具有唯一值的列)。
product_name 是产品的名称。Sales 表：+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| product_id | int |
| period_start | date |
| period_end | date |
| average_daily_sales | int |
+---------------------+---------+
product_id 是这张表的主键(具有唯一值的列)。
period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。
销售日期范围为2018年到2020年。编写解决方案，找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。返回结果并按 product_id 和 report_year 排序。返回结果格式如下例所示。示例 1：输入：
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1 | LC Phone |
| 2 | LC T-Shirt |
| 3 | LC Keychain |
+------------+--------------+
Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1 | 2019-01-25 | 2019-02-28 | 100 |
| 2 | 2018-12-01 | 2020-01-01 | 10 |
| 3 | 2019-12-01 | 2020-01-31 | 1 |
+------------+--------------+-------------+---------------------+
输出：
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1 | LC Phone | 2019 | 3500 |
| 2 | LC T-Shirt | 2018 | 310 |
| 2 | LC T-Shirt | 2019 | 3650 |
| 2 | LC T-Shirt | 2020 | 10 |
| 3 | LC Keychain | 2019 | 31 |
| 3 | LC Keychain | 2020 | 31 |
+------------+--------------+-------------+--------------+
解释：
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。
Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id int, period_start date, period_end date, average_daily_sales int)
Truncate table Product
insert into Product (product_id, product_name) values (‘1’, 'LC Phone ')
insert into Product (product_id, product_name) values (‘2’, ‘LC T-Shirt’)
insert into Product (product_id, product_name) values (‘3’, ‘LC Keychain’)
Truncate table Sales
insert into Sales (product_id, period_start, period_end, average_daily_sales) values (‘1’, ‘2019-01-25’, ‘2019-02-28’, ‘100’)
insert into Sales (product_id, period_start, period_end, average_daily_sales) values (‘2’, ‘2018-12-01’, ‘2020-01-01’, ‘10’)
insert into Sales (product_id, period_start, period_end, average_daily_sales) values (‘3’, ‘2019-12-01’, ‘2020-01-31’, ‘1’)
with recursive date_period as (select min(period_start) min_period, max(period_end) max_period
from Sales
union
select date_add(min_period, interval 1 day) min_period, max_period
from date_period
where min_period < max_period)
select a.product_id , product_name ,cast(year(b.min_period) as char ) report_year , count(1)*max(average_daily_sales) total_amount from Sales a left join date_period b on b.min_period between a.period_start and a.period_end
left join Product c on a.product_id=c.product_id
group by cast(year(b.min_period) as char ) ,a.product_id,product_name
order by product_id,cast(year(b.min_period) as char )
时间扩展可以借助日历表将数据展开后记性汇总，这里是将最小值到最大值做了一个递归。
1479. 周内每天的销售情况
1479. 周内每天的销售情况
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表：Orders+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| order_date | date |
| item_id | varchar |
| quantity | int |
+---------------+---------+
(order_id, item_id) 是该表主键(具有唯一值的列的组合)
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.
表：Items+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
+---------------------+---------+
item_id 是该表主键(具有唯一值的列)
item_name 是商品的名字
item_category 是商品的类别你是企业主，想要获得分类商品和周内每天的销售报告。编写解决方案，报告 周内每天 每个商品类别下订购了多少单位。返回结果表单 按商品类别排序 。结果格式如下例所示。示例 1：输入：
Orders 表：
+------------+--------------+-------------+--------------+-------------+
| order_id | customer_id | order_date | item_id | quantity |
+------------+--------------+-------------+--------------+-------------+
| 1 | 1 | 2020-06-01 | 1 | 10 |
| 2 | 1 | 2020-06-08 | 2 | 10 |
| 3 | 2 | 2020-06-02 | 1 | 5 |
| 4 | 3 | 2020-06-03 | 3 | 5 |
| 5 | 4 | 2020-06-04 | 4 | 1 |
| 6 | 4 | 2020-06-05 | 5 | 5 |
| 7 | 5 | 2020-06-05 | 1 | 10 |
| 8 | 5 | 2020-06-14 | 4 | 5 |
| 9 | 5 | 2020-06-21 | 3 | 5 |
+------------+--------------+-------------+--------------+-------------+Items 表：
+------------+----------------+---------------+
| item_id | item_name | item_category |
+------------+----------------+---------------+
| 1 | LC Alg. Book | Book |
| 2 | LC DB. Book | Book |
| 3 | LC SmarthPhone | Phone |
| 4 | LC Phone 2020 | Phone |
| 5 | LC SmartGlass | Glasses |
| 6 | LC T-Shirt XL | T-Shirt |
+------------+----------------+---------------+
输出：
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book | 20 | 5 | 0 | 0 | 10 | 0 | 0 |
| Glasses | 0 | 0 | 0 | 0 | 5 | 0 | 0 |
| Phone | 0 | 0 | 5 | 1 | 0 | 0 | 10 |
| T-Shirt | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
解释：
在周一(2020-06-01, 2020-06-08)，Book分类(ids: 1, 2)下，总共销售了20个单位(10 + 10)
在周二(2020-06-02)，Book分类(ids: 1, 2)下，总共销售了5个单位
在周三(2020-06-03)，Phone分类(ids: 3, 4)下，总共销售了5个单位
在周四(2020-06-04)，Phone分类(ids: 3, 4)下，总共销售了1个单位
在周五(2020-06-05)，Book分类(ids: 1, 2)下，总共销售了10个单位，Glasses分类(ids: 5)下，总共销售了5个单位
在周六, 没有商品销售
在周天(2020-06-14, 2020-06-21)，Phone分类(ids: 3, 4)下，总共销售了10个单位(5 + 5)
没有销售 T-Shirt 类别的商品
Create table If Not Exists Orders (order_id int, customer_id int, order_date date, item_id varchar(30), quantity int)
Create table If Not Exists Items (item_id varchar(30), item_name varchar(30), item_category varchar(30))
Truncate table Orders
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘1’, ‘1’, ‘2020-06-01’, ‘1’, ‘10’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘2’, ‘1’, ‘2020-06-08’, ‘2’, ‘10’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘3’, ‘2’, ‘2020-06-02’, ‘1’, ‘5’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘4’, ‘3’, ‘2020-06-03’, ‘3’, ‘5’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘5’, ‘4’, ‘2020-06-04’, ‘4’, ‘1’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘6’, ‘4’, ‘2020-06-05’, ‘5’, ‘5’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘7’, ‘5’, ‘2020-06-05’, ‘1’, ‘10’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘8’, ‘5’, ‘2020-06-14’, ‘4’, ‘5’)
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values (‘9’, ‘5’, ‘2020-06-21’, ‘3’, ‘5’)
Truncate table Items
insert into Items (item_id, item_name, item_category) values (‘1’, ‘LC Alg. Book’, ‘Book’)
insert into Items (item_id, item_name, item_category) values (‘2’, ‘LC DB. Book’, ‘Book’)
insert into Items (item_id, item_name, item_category) values (‘3’, ‘LC SmarthPhone’, ‘Phone’)
insert into Items (item_id, item_name, item_category) values (‘4’, ‘LC Phone 2020’, ‘Phone’)
insert into Items (item_id, item_name, item_category) values (‘5’, ‘LC SmartGlass’, ‘Glasses’)
insert into Items (item_id, item_name, item_category) values (‘6’, ‘LC T-Shirt XL’, ‘T-shirt’)
SELECT i.item_category AS Category,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Monday’, o.quantity, 0)) AS Monday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Tuesday’, o.quantity, 0)) AS Tuesday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Wednesday’, o.quantity, 0)) AS Wednesday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Thursday’, o.quantity, 0)) AS Thursday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Friday’, o.quantity, 0)) AS Friday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Saturday’, o.quantity, 0)) AS Saturday,
SUM(IF(date_format(o.order_date,‘%W’) = ‘Sunday’, o.quantity, 0)) AS Sunday
FROM Orders AS o RIGHT JOIN Items AS i
ON o.item_id = i.item_id
GROUP BY i.item_category
ORDER BY i.item_category
1635. Hopper 公司查询 I
1635. Hopper 公司查询 I
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表: Drivers+-------------+---------+
| Column Name | Type |
+-------------+---------+
| driver_id | int |
| join_date | date |
+-------------+---------+
driver_id 是该表的主键(具有唯一值的列)。
该表的每一行均包含驾驶员的ID以及他们加入Hopper公司的日期。表: Rides+--------------+---------+
| Column Name | Type |
+--------------+---------+
| ride_id | int |
| user_id | int |
| requested_at | date |
+--------------+---------+
ride_id 是该表的主键(具有唯一值的列)。
该表的每一行均包含行程ID(ride_id)，用户ID(user_id)以及该行程的日期(requested_at)。
该表中可能有一些不被接受的乘车请求。表: AcceptedRides+---------------+---------+
| Column Name | Type |
+---------------+---------+
| ride_id | int |
| driver_id | int |
| ride_distance | int |
| ride_duration | int |
+---------------+---------+
ride_id 是该表的主键(具有唯一值的列)。
该表的每一行都包含已接受的行程信息。
表中的行程信息都在“Rides”表中存在。编写解决方案以报告 2020 年每个月的以下统计信息：截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。
该月接受的乘车次数（accepted_rides）。
返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。返回结果格式如下例所示。示例 1：输入：
表 Drivers:
+-----------+------------+
| driver_id | join_date |
+-----------+------------+
| 10 | 2019-12-10 |
| 8 | 2020-1-13 |
| 5 | 2020-2-16 |
| 7 | 2020-3-8 |
| 4 | 2020-5-17 |
| 1 | 2020-10-24 |
| 6 | 2021-1-5 |
+-----------+------------+
表 Rides:
+---------+---------+--------------+
| ride_id | user_id | requested_at |
+---------+---------+--------------+
| 6 | 75 | 2019-12-9 |
| 1 | 54 | 2020-2-9 |
| 10 | 63 | 2020-3-4 |
| 19 | 39 | 2020-4-6 |
| 3 | 41 | 2020-6-3 |
| 13 | 52 | 2020-6-22 |
| 7 | 69 | 2020-7-16 |
| 17 | 70 | 2020-8-25 |
| 20 | 81 | 2020-11-2 |
| 5 | 57 | 2020-11-9 |
| 2 | 42 | 2020-12-9 |
| 11 | 68 | 2021-1-11 |
| 15 | 32 | 2021-1-17 |
| 12 | 11 | 2021-1-19 |
| 14 | 18 | 2021-1-27 |
+---------+---------+--------------+
表 AcceptedRides:
+---------+-----------+---------------+---------------+
| ride_id | driver_id | ride_distance | ride_duration |
+---------+-----------+---------------+---------------+
| 10 | 10 | 63 | 38 |
| 13 | 10 | 73 | 96 |
| 7 | 8 | 100 | 28 |
| 17 | 7 | 119 | 68 |
| 20 | 1 | 121 | 92 |
| 5 | 7 | 42 | 101 |
| 2 | 4 | 6 | 38 |
| 11 | 8 | 37 | 43 |
| 15 | 8 | 108 | 82 |
| 12 | 8 | 38 | 34 |
| 14 | 1 | 90 | 74 |
+---------+-----------+---------------+---------------+
输出：
+-------+----------------+----------------+
| month | active_drivers | accepted_rides |
+-------+----------------+----------------+
| 1 | 2 | 0 |
| 2 | 3 | 0 |
| 3 | 4 | 1 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 5 | 1 |
| 7 | 5 | 1 |
| 8 | 5 | 1 |
| 9 | 5 | 0 |
| 10 | 6 | 0 |
| 11 | 6 | 2 |
| 12 | 6 | 1 |
+-------+----------------+----------------+
解释：
截至1月底->两个活跃的驾驶员（10,8），没有被接受的行程。
截至2月底->三个活跃的驾驶员（10,8,5），没有被接受的行程。
截至3月底->四个活跃的驾驶员（10,8,5,7），一个被接受的行程（10）。
截至4月底->四个活跃的驾驶员（10,8,5,7），没有被接受的行程。
截至5月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至6月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（13）。
截至7月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（7）。
截至8月底->五个活跃的驾驶员（10,8,5,7,4），一位接受的行程（17）。
截至9月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至10月底->六个活跃的驾驶员（10,8,5,7,4,1），没有被接受的行程。
截至11月底->六个活跃的驾驶员（10,8,5,7,4,1），两个被接受的行程（20,5）。
截至12月底->六个活跃的驾驶员（10,8,5,7,4,1），一个被接受的行程（2）。
Create table If Not Exists Drivers (driver_id int, join_date date)
Create table If Not Exists Rides (ride_id int, user_id int, requested_at date)
Create table If Not Exists AcceptedRides (ride_id int, driver_id int, ride_distance int, ride_duration int)
Truncate table Drivers
insert into Drivers (driver_id, join_date) values (‘10’, ‘2019-12-10’)
insert into Drivers (driver_id, join_date) values (‘8’, ‘2020-1-13’)
insert into Drivers (driver_id, join_date) values (‘5’, ‘2020-2-16’)
insert into Drivers (driver_id, join_date) values (‘7’, ‘2020-3-8’)
insert into Drivers (driver_id, join_date) values (‘4’, ‘2020-5-17’)
insert into Drivers (driver_id, join_date) values (‘1’, ‘2020-10-24’)
insert into Drivers (driver_id, join_date) values (‘6’, ‘2021-1-5’)
Truncate table Rides
insert into Rides (ride_id, user_id, requested_at) values (‘6’, ‘75’, ‘2019-12-9’)
insert into Rides (ride_id, user_id, requested_at) values (‘1’, ‘54’, ‘2020-2-9’)
insert into Rides (ride_id, user_id, requested_at) values (‘10’, ‘63’, ‘2020-3-4’)
insert into Rides (ride_id, user_id, requested_at) values (‘19’, ‘39’, ‘2020-4-6’)
insert into Rides (ride_id, user_id, requested_at) values (‘3’, ‘41’, ‘2020-6-3’)
insert into Rides (ride_id, user_id, requested_at) values (‘13’, ‘52’, ‘2020-6-22’)
insert into Rides (ride_id, user_id, requested_at) values (‘7’, ‘69’, ‘2020-7-16’)
insert into Rides (ride_id, user_id, requested_at) values (‘17’, ‘70’, ‘2020-8-25’)
insert into Rides (ride_id, user_id, requested_at) values (‘20’, ‘81’, ‘2020-11-2’)
insert into Rides (ride_id, user_id, requested_at) values (‘5’, ‘57’, ‘2020-11-9’)
insert into Rides (ride_id, user_id, requested_at) values (‘2’, ‘42’, ‘2020-12-9’)
insert into Rides (ride_id, user_id, requested_at) values (‘11’, ‘68’, ‘2021-1-11’)
insert into Rides (ride_id, user_id, requested_at) values (‘15’, ‘32’, ‘2021-1-17’)
insert into Rides (ride_id, user_id, requested_at) values (‘12’, ‘11’, ‘2021-1-19’)
insert into Rides (ride_id, user_id, requested_at) values (‘14’, ‘18’, ‘2021-1-27’)
Truncate table AcceptedRides
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘10’, ‘10’, ‘63’, ‘38’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘13’, ‘10’, ‘73’, ‘96’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘7’, ‘8’, ‘100’, ‘28’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘17’, ‘7’, ‘119’, ‘68’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘20’, ‘1’, ‘121’, ‘92’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘5’, ‘7’, ‘42’, ‘101’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘2’, ‘4’, ‘6’, ‘38’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘11’, ‘8’, ‘37’, ‘43’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘15’, ‘8’, ‘108’, ‘82’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘12’, ‘8’, ‘38’, ‘34’)
insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values (‘14’, ‘1’, ‘90’, ‘74’)
with recursive tmp(n) as (
select 1 n
union all
select n+1 from tmp where n < 12
)
select
t.n month ,
coalesce(max(rw),0) active_drivers ,
coalesce(max(c.cut),0) accepted_rides
from (SELECT
a.join_date,
ROW_NUMBER() OVER ( ORDER BY a.join_date rows BETWEEN 0 PRECEDING AND CURRENT ROW) rw
FROM
Drivers AS a where year(a.join_date)<=2020 ) a right join tmp t
on month(a.join_date)<=t.n
left join (
select month(requested_at) month,count(1) cut from Rides a join AcceptedRides b on a.ride_id=b.ride_id
where year(requested_at)=2020
group by month(requested_at)) c
on t.n=c.month
group by n;
2752. 在连续天数上进行了最多交易次数的顾客
2752. 在连续天数上进行了最多交易次数的顾客
已解答
困难
相关标签
SQL Schema
Pandas Schema
表： Transactions+------------------+------+
| 列名 | 类型 |
+------------------+------+
| transaction_id | int |
| customer_id | int |
| transaction_date | date |
| amount | int |
+------------------+------+
transaction_id 是这个表的具有唯一值的列。
每行包含有关交易的信息，包括唯一的（customer_id，transaction_date）以及相应的 customer_id 和 amount。
编写一个解决方案，找到连续天数上进行了最多交易的所有 customer_id 。返回所有具有最大连续交易次数的 customer_id 。结果表按 customer_id 的 升序 排序。结果的格式如下所示。示例 1：输入：
Transactions 表:
+----------------+-------------+------------------+--------+
| transaction_id | customer_id | transaction_date | amount |
+----------------+-------------+------------------+--------+
| 1 | 101 | 2023-05-01 | 100 |
| 2 | 101 | 2023-05-02 | 150 |
| 3 | 101 | 2023-05-03 | 200 |
| 4 | 102 | 2023-05-01 | 50 |
| 5 | 102 | 2023-05-03 | 100 |
| 6 | 102 | 2023-05-04 | 200 |
| 7 | 105 | 2023-05-01 | 100 |
| 8 | 105 | 2023-05-02 | 150 |
| 9 | 105 | 2023-05-03 | 200 |
+----------------+-------------+------------------+--------+
输出：
+-------------+
| customer_id |
+-------------+
| 101 |
| 105 |
+-------------+
解释：
- customer_id 为 101 共有 3 次交易，且全部是连续的。
- customer_id 为 102 共有 3 次交易，但只有其中 2 次是连续的。
- customer_id 为 105 共有 3 次交易，且全部是连续的。
总的来说，最大连续交易次数为 3，由 customer_id 为 101 和 105 的完成。customer_id 按升序排序。
select customer_id from (
select customer_id,rank() over (order by cut desc) ra from (select customer_id, date_add(transaction_date,interval -rn day ),count(1) cut from (
select transaction_id, customer_id, transaction_date, amount,
row_number() over (partition by customer_id order by transaction_date) rn
from Transactions
)a
group by customer_id, date_add(transaction_date,interval -rn day )) a
)b where b.ra=1 order by customer_id;
Create table If Not Exists Transactions (transaction_id int, customer_id int, transaction_date date, amount int)
Truncate table Transactions
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘1’, ‘101’, ‘2023-05-01’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘2’, ‘101’, ‘2023-05-02’, ‘150’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘3’, ‘101’, ‘2023-05-03’, ‘200’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘4’, ‘102’, ‘2023-05-01’, ‘50’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘5’, ‘102’, ‘2023-05-03’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘6’, ‘102’, ‘2023-05-04’, ‘200’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘7’, ‘105’, ‘2023-05-01’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘8’, ‘105’, ‘2023-05-02’, ‘150’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘9’, ‘105’, ‘2023-05-03’, ‘200’)
2720. 受欢迎度百分比( count()over()计算总数的用法)
2720. 受欢迎度百分比
已解答
困难
相关标签
SQL Schema
表：Friends+-------------+------+
| 列名 | 类型 |
+-------------+------+
| user1 | int |
| user2 | int |
+-------------+------+
(user1, user2) 是该表的主键。
每一行包含关于用户1和用户2是朋友的信息。
编写一条 SQL 查询，找出 Meta/Facebook 平台上每个用户的受欢迎度的百分比。受欢迎度百分比定义为用户拥有的朋友总数除以平台上的总用户数，然后乘以 100，并 四舍五入保留 2 位小数 。返回按照 user1 升序 排序的结果表。查询结果的格式如下所示。示例 1：输入：
Friends 表:
+-------+-------+
| user1 | user2 |
+-------+-------+
| 2 | 1 |
| 1 | 3 |
| 4 | 1 |
| 1 | 5 |
| 1 | 6 |
| 2 | 6 |
| 7 | 2 |
| 8 | 3 |
| 3 | 9 |
+-------+-------+
输出：
+-------+-----------------------+
| user1 | percentage_popularity |
+-------+-----------------------+
| 1 | 55.56 |
| 2 | 33.33 |
| 3 | 33.33 |
| 4 | 11.11 |
| 5 | 11.11 |
| 6 | 22.22 |
| 7 | 11.11 |
| 8 | 11.11 |
| 9 | 11.11 |
+-------+-----------------------+
解释：
平台上总共有 9 个用户。
- 用户 "1" 与 2、3、4、5 和 6 是朋友。因此，用户 1 的受欢迎度百分比计算为（5/9）* 100 = 55.56。
- 用户 "2" 与 1、6 和 7 是朋友。因此，用户 2 的受欢迎度百分比计算为（3/9）* 100 = 33.33。
- 用户 "3" 与 1、8 和 9 是朋友。因此，用户 3 的受欢迎度百分比计算为（3/9）* 100 = 33.33。
- 用户 "4" 与 1 是朋友。因此，用户 4 的受欢迎度百分比计算为（1/9）* 100 = 11.11。
- 用户 "5" 与 1 是朋友。因此，用户 5 的受欢迎度百分比计算为（1/9）* 100 = 11.11。
- 用户 "6" 与 1 和 2 是朋友。因此，用户 6 的受欢迎度百分比计算为（2/9）* 100 = 22.22。
- 用户 "7" 与 2 是朋友。因此，用户 7 的受欢迎度百分比计算为（1/9）* 100 = 11.11。
- 用户 "8" 与 3 是朋友。因此，用户 8 的受欢迎度百分比计算为（1/9）* 100 = 11.11。
- 用户 "9" 与 3 是朋友。因此，用户 9 的受欢迎度百分比计算为（1/9）* 100 = 11.11。
user1 按升序排序。
Create table if not exists Friends (user1 int, user2 int)
Truncate table Friends
insert into Friends (user1, user2) values (‘2’, ‘1’)
insert into Friends (user1, user2) values (‘1’, ‘3’)
insert into Friends (user1, user2) values (‘4’, ‘1’)
insert into Friends (user1, user2) values (‘1’, ‘5’)
insert into Friends (user1, user2) values (‘1’, ‘6’)
insert into Friends (user1, user2) values (‘2’, ‘6’)
insert into Friends (user1, user2) values (‘7’, ‘2’)
insert into Friends (user1, user2) values (‘8’, ‘3’)
insert into Friends (user1, user2) values (‘3’, ‘9’)
select user1,round(cut *100/ total_count,2) percentage_popularity from (
select user1,count(user2) cut ,count(1) over ( ) total_count from (
select user1, user2
from Friends
union
select user2,user1 from Friends)a
group by user1
)a order by user1
互为好友的问题，要两边变换后union， 使用 count（） over() 不写的情况下可以计算出总条数
2701. 连续递增交易(连续且增长问题)
2701. 连续递增交易
已解答
困难
相关标签
SQL Schema
表: Transactions+------------------+------+
| 字段名 | 类型 |
+------------------+------+
| transaction_id | int |
| customer_id | int |
| transaction_date | date |
| amount | int |
+------------------+------+
transaction_id 是该表的主键。
每行包含有关交易的信息，包括唯一的 (customer_id, transaction_date)，以及相应的 customer_id 和 amount。
编写一个 SQL 查询，找出至少连续三天 amount 递增的客户。并包括 customer_id 、连续交易期的起始日期和结束日期。一个客户可以有多个连续的交易。返回结果并按照 customer_id 升序 排列。查询结果的格式如下所示。示例 1：输入：
Transactions 表:
+----------------+-------------+------------------+--------+
| transaction_id | customer_id | transaction_date | amount |
+----------------+-------------+------------------+--------+
| 1 | 101 | 2023-05-01 | 100 |
| 2 | 101 | 2023-05-02 | 150 |
| 3 | 101 | 2023-05-03 | 200 |
| 4 | 102 | 2023-05-01 | 50 |
| 5 | 102 | 2023-05-03 | 100 |
| 6 | 102 | 2023-05-04 | 200 |
| 7 | 105 | 2023-05-01 | 100 |
| 8 | 105 | 2023-05-02 | 150 |
| 9 | 105 | 2023-05-03 | 200 |
| 10 | 105 | 2023-05-04 | 300 |
| 11 | 105 | 2023-05-12 | 250 |
| 12 | 105 | 2023-05-13 | 260 |
| 13 | 105 | 2023-05-14 | 270 |
+----------------+-------------+------------------+--------+
输出：
+-------------+-------------------+-----------------+
| customer_id | consecutive_start | consecutive_end |
+-------------+-------------------+-----------------+
| 101 | 2023-05-01 | 2023-05-03 |
| 105 | 2023-05-01 | 2023-05-04 |
| 105 | 2023-05-12 | 2023-05-14 |
+-------------+-------------------+-----------------+
解释：
- customer_id 为 101 的客户在 2023年5月1日 至 2023年5月3日 期间进行了连续递增金额的交易。
- customer_id 为 102 的客户没有至少连续三天的交易。
- customer_id 为 105 的客户有两组连续交易：从 2023年5月1日 至 2023年5月4日，以及 2023年5月12日 至 2023年5月14日。结果按 customer_id 升序排序
Create table If Not Exists Transactions (transaction_id int, customer_id int, transaction_date date, amount int)
Truncate table Transactions
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘1’, ‘101’, ‘2023-05-01’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘2’, ‘101’, ‘2023-05-02’, ‘150’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘3’, ‘101’, ‘2023-05-03’, ‘200’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘4’, ‘102’, ‘2023-05-01’, ‘50’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘5’, ‘102’, ‘2023-05-03’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘6’, ‘102’, ‘2023-05-04’, ‘200’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘7’, ‘105’, ‘2023-05-01’, ‘100’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘8’, ‘105’, ‘2023-05-02’, ‘150’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘9’, ‘105’, ‘2023-05-03’, ‘200’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘10’, ‘105’, ‘2023-05-04’, ‘300’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘11’, ‘105’, ‘2023-05-12’, ‘250’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘12’, ‘105’, ‘2023-05-13’, ‘260’)
insert into Transactions (transaction_id, customer_id, transaction_date, amount) values (‘13’, ‘105’, ‘2023-05-14’, ‘270’)
select customer_id ,consecutive_start ,consecutive_end
from (select customer_id,min(transaction_date) consecutive_start ,max(transaction_date) consecutive_end ,count(1) cut
from (
select customer_id,
transaction_date,
rn,
sum(if(amount > lag_aut, 0, 1)) over (partition by customer_id order by transaction_date) lag_type
from (select transaction_id,
customer_id,
transaction_date,
amount,
row_number() over (partition by customer_id order by transaction_date ) rn,
lag(amount, 1, 0) over (partition by customer_id order by transaction_date ) lag_aut
from Transactions) a
)b group by customer_id,date_add(transaction_date ,interval -rn day ) ,lag_type) c
where cut>2 order by customer_id
连续问题借助自然数进行二次group by，并且增长问题需要再这个基础上增加一个标记
2494. 合并在同一个大厅重叠的活动
2494. 合并在同一个大厅重叠的活动
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表: HallEvents+-------------+------+
| Column Name | Type |
+-------------+------+
| hall_id | int |
| start_day | date |
| end_day | date |
+-------------+------+
该表可能包含重复字段。
该表的每一行表示活动的开始日期和结束日期，以及活动举行的大厅。编写解决方案，合并在 同一个大厅举行 的所有重叠活动。如果两个活动 至少有一天 相同，那么它们就是重叠的。以任意顺序返回结果表。结果格式如下所示。示例 1:输入:
HallEvents 表:
+---------+------------+------------+
| hall_id | start_day | end_day |
+---------+------------+------------+
| 1 | 2023-01-13 | 2023-01-14 |
| 1 | 2023-01-14 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 2 | 2022-12-13 | 2022-12-17 |
| 3 | 2022-12-01 | 2023-01-30 |
+---------+------------+------------+
输出:
+---------+------------+------------+
| hall_id | start_day | end_day |
+---------+------------+------------+
| 1 | 2023-01-13 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 3 | 2022-12-01 | 2023-01-30 |
+---------+------------+------------+
解释: 有三个大厅。
大厅 1:
- 两个活动 ["2023-01-13", "2023-01-14"] 和 ["2023-01-14", "2023-01-17"] 重叠。我们将它们合并到一个活动中 ["2023-01-13", "2023-01-17"]。
- 活动 ["2023-01-18", "2023-01-25"] 不与任何其他活动重叠，所以我们保持原样。
大厅 2:
- 两个活动 ["2022-12-09", "2022-12-23"] 和 ["2022-12-13", "2022-12-17"] 重叠。我们将它们合并到一个活动中 ["2022-12-09", "2022-12-23"]。
大厅 3:
- 大厅只有一个活动，所以我们返回它。请注意，我们只分别考虑每个大厅的活动。
Create table If Not Exists HallEvents (hall_id int, start_day date, end_day date)
Truncate table HallEvents
insert into HallEvents (hall_id, start_day, end_day) values (‘1’, ‘2023-01-13’, ‘2023-01-14’)
insert into HallEvents (hall_id, start_day, end_day) values (‘1’, ‘2023-01-14’, ‘2023-01-17’)
insert into HallEvents (hall_id, start_day, end_day) values (‘1’, ‘2023-01-18’, ‘2023-01-25’)
insert into HallEvents (hall_id, start_day, end_day) values (‘2’, ‘2022-12-09’, ‘2022-12-23’)
insert into HallEvents (hall_id, start_day, end_day) values (‘2’, ‘2022-12-13’, ‘2022-12-17’)
insert into HallEvents (hall_id, start_day, end_day) values (‘3’, ‘2022-12-01’, ‘2023-01-30’)
SELECT hall_id,
MIN(start_day) AS start_day,
MAX(end_day) AS end_day
FROM (
SELECT *,
SUM(range_start) OVER (PARTITION BY hall_id ORDER BY start_day) AS range_grp
FROM (
SELECT *,
CASE WHEN start_day <= LAG(max_end_day_so_far) OVER (PARTITION BY hall_id ORDER BY start_day) THEN 0
ELSE 1 END AS range_start
FROM (
SELECT hall_id,
start_day,
end_day,
MAX(end_day) OVER (PARTITION BY hall_id ORDER BY start_day) AS max_end_day_so_far
FROM HallEvents
) t
) t1
) t2
GROUP BY hall_id, range_grp;
对每个hall_id单独做计算，计算顺序依据start_day升序(体现在窗口函数OVER部分中)；
作辅助列，对每一行，计算截至该行的最大的end_day时间值，记作max_end_day_so_far；
检查当前行start_day是否小于上一行的max_end_day_so_far，（注意到我们的计算都基于按start_day作升序排序）如果是说明这一行的start_day被包含在上一行的时间区间中，那么设置值为0，反之，设置值为1，记作range_start；
窗口累加range_start，记作range_grp，用于作为分组的标志；
按hall_id和range_grp进行分组，对于每个分组，MIN(d1)即为最终结果区间的开始，MAX(d2)即为最终结果区间的结束。
2474. 购买量严格增加的客户
2474. 购买量严格增加的客户
已解答
困难
相关标签
相关企业
SQL Schema
表: Orders+--------------+------+
| Column Name | Type |
+--------------+------+
| order_id | int |
| customer_id | int |
| order_date | date |
| price | int |
+--------------+------+
order_id 是该表的主键。
每行包含订单的 id、订购该订单的客户 id、订单日期和价格。编写一个 SQL 查询，报告 总购买量 每年严格增加的客户 id。客户在一年内的 总购买量 是该年订单价格的总和。如果某一年客户没有下任何订单，我们认为总购买量为 0。
对于每个客户，要考虑的第一个年是他们 第一次下单 的年份。
对于每个客户，要考虑的最后一年是他们 最后一次下单 的年份。
以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Orders 表:
+----------+-------------+------------+-------+
| order_id | customer_id | order_date | price |
+----------+-------------+------------+-------+
| 1 | 1 | 2019-07-01 | 1100 |
| 2 | 1 | 2019-11-01 | 1200 |
| 3 | 1 | 2020-05-26 | 3000 |
| 4 | 1 | 2021-08-31 | 3100 |
| 5 | 1 | 2022-12-07 | 4700 |
| 6 | 2 | 2015-01-01 | 700 |
| 7 | 2 | 2017-11-07 | 1000 |
| 8 | 3 | 2017-01-01 | 900 |
| 9 | 3 | 2018-11-07 | 900 |
+----------+-------------+------------+-------+
输出:
+-------------+
| customer_id |
+-------------+
| 1 |
+-------------+
解释:
客户 1: 第一年是 2019 年，最后一年是 2022 年- 2019: 1100 + 1200 = 2300- 2020: 3000- 2021: 3100- 2022: 4700我们可以看到总购买量每年都在严格增加，所以我们在答案中包含了客户 1。客户 2: 第一年是2015年，最后一年是2017年- 2015: 700- 2016: 0- 2017: 1000我们没有把客户 2 包括在答案中，因为总的购买量并没有严格地增加。请注意，客户 2 在 2016 年没有购买任何物品。客户 3: 第一年是 2017 年，最后一年是 2018 年- 2017: 900- 2018: 900
Create table If Not Exists Orders (order_id int, customer_id int, order_date date, price int)
Truncate table Orders
insert into Orders (order_id, customer_id, order_date, price) values (‘1’, ‘1’, ‘2019-07-01’, ‘1100’)
insert into Orders (order_id, customer_id, order_date, price) values (‘2’, ‘1’, ‘2019-11-01’, ‘1200’)
insert into Orders (order_id, customer_id, order_date, price) values (‘3’, ‘1’, ‘2020-05-26’, ‘3000’)
insert into Orders (order_id, customer_id, order_date, price) values (‘4’, ‘1’, ‘2021-08-31’, ‘3100’)
insert into Orders (order_id, customer_id, order_date, price) values (‘5’, ‘1’, ‘2022-12-07’, ‘4700’)
insert into Orders (order_id, customer_id, order_date, price) values (‘6’, ‘2’, ‘2015-01-01’, ‘700’)
insert into Orders (order_id, customer_id, order_date, price) values (‘7’, ‘2’, ‘2017-11-07’, ‘1000’)
insert into Orders (order_id, customer_id, order_date, price) values (‘8’, ‘3’, ‘2017-01-01’, ‘900’)
insert into Orders (order_id, customer_id, order_date, price) values (‘9’, ‘3’, ‘2018-11-07’, ‘900’)
select customer_id from (
select customer_id,year-drn,range_group
from (select *,sum(if(sum_price>last_price,0,1)) over (partition by customer_id order by year ) range_group
from (select customer_id,year,sum(price) sum_price,max(drn) drn,lag(sum(price)) over (partition by customer_id order by year) last_price
from (
select order_id, customer_id, order_date, price,year(order_date) year,
dense_rank() over (partition by customer_id order by year(order_date) ) drn
from Orders
) a group by customer_id,year) b) c group by customer_id,year-drn,range_group
) d group by customer_id having count(1)=1
这个是变相的连续并且增长的问题，需要有个日然排序和分组隔断
2362. 生成发票
SQL Schema
Pandas Schema
表: Products+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id | int |
| price | int |
+-------------+------+
product_id 包含唯一值。
该表中的每一行显示了一个产品的 ID 和一个单位的价格。表: Purchases+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id | int |
| product_id | int |
| quantity | int |
+-------------+------+
(invoice_id, product_id) 是该表的主键（具有唯一值的列的组合）
该表中的每一行都显示了从发票中的一种产品订购的数量。编写解决方案，展示价格最高的发票的详细信息。如果两个或多个发票具有相同的价格，则返回 invoice_id 最小的发票的详细信息。以 任意顺序 返回结果表。结果格式示例如下。示例 1:输入:
Products 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1 | 100 |
| 2 | 200 |
+------------+-------+
Purchases 表:
+------------+------------+----------+
| invoice_id | product_id | quantity |
+------------+------------+----------+
| 1 | 1 | 2 |
| 3 | 2 | 1 |
| 2 | 2 | 3 |
| 2 | 1 | 4 |
| 4 | 1 | 10 |
+------------+------------+----------+
输出:
+------------+----------+-------+
| product_id | quantity | price |
+------------+----------+-------+
| 2 | 3 | 600 |
| 1 | 4 | 400 |
+------------+----------+-------+
解释:
发票 1: price = (2 * 100) = $200
发票 2: price = (4 * 100) + (3 * 200) = $1000
发票 3: price = (1 * 200) = $200
发票 4: price = (10 * 100) = $1000最高价格是 1000 美元，最高价格的发票是 2 和 4。我们返回 ID 最小的发票 2 的详细信息。
Create table If Not Exists Products (product_id int, price int)
Create table If Not Exists Purchases (invoice_id int, product_id int, quantity int)
Truncate table Products
insert into Products (product_id, price) values (‘1’, ‘100’)
insert into Products (product_id, price) values (‘2’, ‘200’)
Truncate table Purchases
insert into Purchases (invoice_id, product_id, quantity) values (‘1’, ‘1’, ‘2’)
insert into Purchases (invoice_id, product_id, quantity) values (‘3’, ‘2’, ‘1’)
insert into Purchases (invoice_id, product_id, quantity) values (‘2’, ‘2’, ‘3’)
insert into Purchases (invoice_id, product_id, quantity) values (‘2’, ‘1’, ‘4’)
insert into Purchases (invoice_id, product_id, quantity) values (‘4’, ‘1’, ‘10’)
select product_id,quantity,quantity*price price
from (select *, rank() over (order by sum_price desc ,invoice_id ) rw
from (select a.product_id,invoice_id,quantity,price, sum(price * quantity) over (partition by invoice_id ) sum_price
from Purchases a
left join Products b on a.product_id = b.product_id) b) c where rw=1 ;
2253. 动态取消表的旋转（动态列转行）
表: Products+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| store_name1 | int |
| store_name2 | int |
| : | int |
| : | int |
| : | int |
| store_namen | int |
+-------------+---------+
product_id 是该表的主键。
该表中的每一行都表示该商品在 n 个不同商店中的价格。
如果商店中没有该商品，则该商店列中的价格将为 null。
不同测试用例的商店名称可能会不同。至少有1家店，最多30家店。重要提示: 这个问题针对的是那些对 SQL 有丰富经验的人。如果你是初学者，我们建议你现在跳过它。实现 UnpivotProducts 过程来重新组织 Products 表，使每一行都有一个产品的 id、销售该商品的商店名称以及该商品在该商店中的价格。如果某个商品在某个商店中不可用，则不要在结果表中包含该 product_id 和 store 组合的行。结果应该有三列:product_id、store 和 price。过程应该在重新组织表之后返回它。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Products 表:
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1 | 100 | null | 110 | null |
| 2 | null | 200 | null | 190 |
| 3 | null | null | 1000 | 1900 |
+------------+----------+--------+------+------+
输出:
+------------+----------+-------+
| product_id | store | price |
+------------+----------+-------+
| 1 | LC_Store | 100 |
| 1 | Shop | 110 |
| 2 | Nozama | 200 |
| 2 | Souq | 190 |
| 3 | Shop | 1000 |
| 3 | Souq | 1900 |
+------------+----------+-------+
解释:
商品 1 在 LC_Store 和 Shop 销售，价格分别为 100 和 110。
商品 2 在 Nozama 和 Souq 销售，价格分别为 200 和 190。
商品 3 在 Shop 和 Souq 出售，价格分别为 1000 和 1900。
难点是需要动态创建表，表的列不固定
Truncate table Products
insert into Products (product_id, LC_Store, Nozama, Shop, Souq) values (‘1’, ‘100’, ‘None’, ‘110’, ‘None’)
insert into Products (product_id, LC_Store, Nozama, Shop, Souq) values (‘2’, ‘None’, ‘200’, ‘None’, ‘190’)
insert into Products (product_id, LC_Store, Nozama, Shop, Souq) values (‘3’, ‘None’, ‘None’, ‘1000’, ‘1900’)
(1)获取表格中每列（商店）的名字
这是本题难点之一，可以用以下语句查询除‘product_id’以外所有商店的名称：
SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = DATABASE() AND table_name = ‘Products’
AND column_name <> ‘product_id’
本查询语句具体解析可以搜索度娘。
（2）动态拼接所需要的SQL语句
由于查询所需SQL语句随着商店名称的变化而变化，故应用GROUP_CONCAT函数对其进行拼接，需要用到上一步所生成的表：
SELECT
GROUP_CONCAT(‘SELECT product_id, ‘’,
column_name,’’ AS store, ‘,
column_name,
’ AS price FROM Products WHERE ‘,
column_name,
’ IS NOT NULL’
SEPARATOR ’ UNION ’
) INTO @sql FROM (SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = DATABASE() AND table_name = ‘Products’
AND column_name <> ‘product_id’) t;
拼接出的语句大致如下：
SELECT
product_id,’…’ AS store,… AS price
FROM
Products
WHERE
… IS NOT NULL
UNION
SELECT
product_id,‘…’ AS store,… AS price
FROM
Products
WHERE
… IS NOT NULL
(3)修改GROUP_CONCAT函数的最大可识别长度
由于GROUP_CONCAT在默认情况下生成的字符串最长为1024，多余的部分会被自动截取。而题目中提到： at most 30 stores 故必须扩展GROUP_CONCAT函数的最大可识别长度。故在前一步的代码块前加入一句：
SET group_concat_max_len = 10240;
(4)准备与运行拼接好的SQL语句
PREPARE statement FROM @sql;
EXECUTE statement;
无需多言，有疑惑请自行询问度娘。
(5)完整的代码
CREATE PROCEDURE UnpivotProducts() #dynamic:动态 unpivoting:取消旋转
BEGIN
# Write your MySQL query statement below.
SET group_concat_max_len = 10240;
SELECT
GROUP_CONCAT(‘SELECT product_id, ‘’,
column_name,’’ AS store, ',
column_name,
’ AS price FROM Products WHERE ‘,
column_name,
’ IS NOT NULL’
SEPARATOR ’ UNION ’
) INTO @sql FROM (SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = DATABASE() AND table_name = ‘Products’
AND column_name <> ‘product_id’) t;
PREPARE statement FROM @sql;
EXECUTE statement; END
2252. 表的动态旋转(动态 行转列)
2252. 表的动态旋转
困难
相关标签
SQL Schema
Pandas Schema
表: Products+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| store | varchar |
| price | int |
+-------------+---------+
(product_id, store) 是该表的主键（具有唯一值的列的组合）。
该表的每一行都表示商店中的 product_id 的价格。
表中最多有 30 个不同的商店 store。
price 就是这家店商品的价格。重要提示：这个问题针对的是那些对 SQL 有丰富经验的人。如果你是初学者，我们建议你现在跳过它。实现 PivotProducts 过程来重新组织 Products 表，以便每行都有一个商品的 id 及其在每个商店中的价格。如果商品不在商店出售，价格应为 null。表的列应该包含每个商店，并且它们应该按 字典顺序排序。过程应该在重新组织表之后返回它。以 任意顺序 返回结果表。返回结果格式如下所示。示例 1:输入:
Products 表:
+------------+----------+-------+
| product_id | store | price |
+------------+----------+-------+
| 1 | Shop | 110 |
| 1 | LC_Store | 100 |
| 2 | Nozama | 200 |
| 2 | Souq | 190 |
| 3 | Shop | 1000 |
| 3 | Souq | 1900 |
+------------+----------+-------+
输出:
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1 | 100 | null | 110 | null |
| 2 | null | 200 | null | 190 |
| 3 | null | null | 1000 | 1900 |
+------------+----------+--------+------+------+
解释:
有 4 个商店: Shop, LC_Store, Nozama, Souq。 我们首先按字典顺序排列: LC_Store, Nozama, Shop, Souq.
现在, 对于商品 1, LC_Store 的价格是 100，Shop 的价格是 110。另外两个商店没有该商品销售，因此我们将价格设置为 null。
同样, 商品 2 在 Nozama 中的价格是 200，Souq 中的价格是 190。其他两家店都不卖。
商品 3, Shop 中的价格是 1000,Souq 中的价格是1900。其他两家店都不卖。
Create table If Not Exists Products (product_id int, store varchar(7), price int)
Truncate table Products
insert into Products (product_id, store, price) values (‘1’, ‘Shop’, ‘110’)
insert into Products (product_id, store, price) values (‘1’, ‘LC_Store’, ‘100’)
insert into Products (product_id, store, price) values (‘2’, ‘Nozama’, ‘200’)
insert into Products (product_id, store, price) values (‘2’, ‘Souq’, ‘190’)
insert into Products (product_id, store, price) values (‘3’, ‘Shop’, ‘1000’)
insert into Products (product_id, store, price) values (‘3’, ‘Souq’, ‘1900’)
分析题目我们可以发现，题目考察的是表格格式化中的行转列问题，对于这个问题可以使用诸如MAX(CASE WHEN ... THEN ... END) AS ...
GROUP BY
的语句进行转换。（更详细的大佬题解请关注618. 学生地理信息报告的题解） 由于题目中store字段是变化的，故我们必须使用动态生成的SQL语句。具体的方法如下：（1）生成一个包含所有store名称的基础表
WITH temp AS (SELECT DISTINCTstoreFROMProductsORDER BYstore
)
即获取Products表中所有出现过的store名称，并将其按字典序进行排序（2）拼接所要执行的SQL语句
这里我们使用CONCAT函数以及GROUP_CONCAT函数，将所需要的SQL语句拼接到变量@sql之中SELECT CONCAT('SELECT product_id, ',
GROUP_CONCAT('MAX(CASE WHEN store = \'',store,'\' THEN price END) AS ',store),
' FROM Products GROUP BY product_id'
) INTO @sql FROM temp;
拼接出来的SQL语句即为SELECTproduct_id,MAX(CASE WHEN store = ... THEN price END) AS ...,...
FROMProducts
GROUP BYproduct_id
划重点 这里有一点很容易被忽略，就是MySQL中GROUP_CONCAT函数有长度限制，默认为1024，而题目中提到 “There will be at most 30 different stores in the table” 故如果不修改默认长度的话所拼接的SQL语句会被自动截断，所以在WITH语句前需要加入SET group_concat_max_len = 10240;
进行默认长度修改（3）准备和执行生成的SQL语句
PREPARE statement FROM @sql;
EXECUTE statement;
这里无需多言，想进一步了解的可以去问问度娘。（4）完整的代码
CREATE PROCEDURE PivotProducts()
BEGIN# Write your MySQL query statement below.SET group_concat_max_len = 10240;WITH temp AS (SELECT DISTINCTstoreFROMProductsORDER BYstore)SELECT CONCAT('SELECT product_id, ',GROUP_CONCAT('MAX(CASE WHEN store = \'',store,'\' THEN price END) AS ',store),' FROM Products GROUP BY product_id') INTO @sql FROM temp;PREPARE statement FROM @sql;EXECUTE statement;
END
2199. 找到每篇文章的主题(数组炸裂，字符串切分)
2199. 找到每篇文章的主题
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表: Keywords+-------------+---------+
| Column Name | Type |
+-------------+---------+
| topic_id | int |
| word | varchar |
+-------------+---------+
(topic_id, word) 是该表的主键（具有唯一值的列的组合）。
该表的每一行都包含一个主题的 id 和一个用于表达该主题的词。
可以用多个词来表达同一个主题，也可以用一个词来表达多个主题。表: Posts+-------------+---------+
| Column Name | Type |
+-------------+---------+
| post_id | int |
| content | varchar |
+-------------+---------+
post_id 是该表的主键（具有唯一值的列）。
该表的每一行都包含一个帖子的 ID 及其内容。
内容仅由英文字母和空格组成。Leetcode 从其社交媒体网站上收集了一些帖子，并对每个帖子的主题感兴趣。每个主题可以由一个或多个关键字表示。如果某个主题的关键字存在于一个帖子的内容中 (不区分大小写)，那么这个帖子就有这个主题。编写解决方案，根据以下规则查找每篇文章的主题:如果帖子没有来自任何主题的关键词，那么它的主题应该是 "Ambiguous!"。
如果该帖子至少有一个主题的关键字，其主题应该是其主题的 id 按升序排列并以逗号 '，' 分隔的字符串。字符串不应该包含重复的 id。
以 任意顺序 返回结果表。结果格式如下所示。示例 1:输入:
Keywords 表:
+----------+----------+
| topic_id | word |
+----------+----------+
| 1 | handball |
| 1 | football |
| 3 | WAR |
| 2 | Vaccine |
+----------+----------+
Posts 表:
+---------+------------------------------------------------------------------------+
| post_id | content |
+---------+------------------------------------------------------------------------+
| 1 | We call it soccer They call it football hahaha |
| 2 | Americans prefer basketball while Europeans love handball and football |
| 3 | stop the war and play handball |
| 4 | warning I planted some flowers this morning and then got vaccinated |
+---------+------------------------------------------------------------------------+
输出:
+---------+------------+
| post_id | topic |
+---------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1,3 |
| 4 | Ambiguous! |
+---------+------------+
解释:
1: "We call it soccer They call it football hahaha"
"football" 表示主题 1。没有其他词能表示任何其他主题。2: "Americans prefer basketball while Europeans love handball and football"
"handball" 表示主题 1。"football" 表示主题 1。
没有其他词能表示任何其他主题。3: "stop the war and play handball"
"war" 表示主题 3。 "handball" 表示主题 1。
没有其他词能表示任何其他主题。4: "warning I planted some flowers this morning and then got vaccinated"
这个句子里没有一个词能表示任何主题。注意 “warning” 和 “war” 不同，尽管它们有一个共同的前缀。
所以这篇文章 “Ambiguous!”
请注意，可以使用一个词来表达多个主题。
Create table If Not Exists Keywords (topic_id int, word varchar(25))
Create table If Not Exists Posts (post_id int, content varchar(100))
Truncate table Keywords
insert into Keywords (topic_id, word) values (‘1’, ‘handball’)
insert into Keywords (topic_id, word) values (‘1’, ‘football’)
insert into Keywords (topic_id, word) values (‘3’, ‘WAR’)
insert into Keywords (topic_id, word) values (‘2’, ‘Vaccine’)
Truncate table Posts
insert into Posts (post_id, content) values (‘1’, ‘We call it soccer They call it football hahaha’)
insert into Posts (post_id, content) values (‘2’, ‘Americans prefer basketball while Europeans love handball and football’)
insert into Posts (post_id, content) values (‘3’, ‘stop the war and play handball’)
insert into Posts (post_id, content) values (‘4’, ‘warning I planted some flowers this morning and then got vaccinated’)
with recursive A as (
select post_id, content, 1 as n, substring_index(content, ’ ', 1) as content_split from Posts
union all
select post_id, content, n+1 as n, substring_index(substring_index(content, ’ ‘, n+1), ’ ‘, -1) as content_split
from A
where n <= char_length(content)-char_length(replace(content,’ ‘,’’))
)
select
C.post_id,
COALESCE(GROUP_CONCAT(distinct B.topic_id order by B.topic_id SEPARATOR ‘,’), ‘Ambiguous!’) as topic
from (select distinct post_id, lower(content_split) as content_split from A) C
left join (select topic_id, lower(word) as word from Keywords ) B on C.content_split = B.word
group by 1
2173. 最多连胜的次数(连续问题)
2173. 最多连胜的次数
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表: Matches+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id | int |
| match_day | date |
| result | enum |
+-------------+------+
(player_id, match_day) 是该表的主键（具有唯一值的列的组合）。
每一行包括了：参赛选手 id、 比赛时间、 比赛结果。
比赛结果（result）的枚举类型为 ('Win', 'Draw', 'Lose')。选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。编写解决方案来计算每个参赛选手最多的连胜数。结果可以以 任何顺序 返回。结果格式如下例所示：示例 1:输入:
Matches 表:
+-----------+------------+--------+
| player_id | match_day | result |
+-----------+------------+--------+
| 1 | 2022-01-17 | Win |
| 1 | 2022-01-18 | Win |
| 1 | 2022-01-25 | Win |
| 1 | 2022-01-31 | Draw |
| 1 | 2022-02-08 | Win |
| 2 | 2022-02-06 | Lose |
| 2 | 2022-02-08 | Lose |
| 3 | 2022-03-30 | Win |
+-----------+------------+--------+
输出:
+-----------+----------------+
| player_id | longest_streak |
+-----------+----------------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
+-----------+----------------+
解释:
Player 1:
从 2022-01-17 到 2022-01-25, player 1连续赢了三场比赛。
2022-01-31, player 1 平局.
2022-02-08, player 1 赢了一场比赛。
最多连胜了三场比赛。Player 2:
从 2022-02-06 到 2022-02-08, player 2 输了两场比赛。
最多连赢了0场比赛。Player 3:
2022-03-30, player 3 赢了一场比赛。
最多连赢了一场比赛。
Create table If Not Exists Matches (player_id int, match_day date, result ENUM(‘Win’, ‘Draw’, ‘Lose’))
Truncate table Matches
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-17’, ‘Win’)
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-18’, ‘Win’)
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-25’, ‘Win’)
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-01-31’, ‘Draw’)
insert into Matches (player_id, match_day, result) values (‘1’, ‘2022-02-08’, ‘Win’)
insert into Matches (player_id, match_day, result) values (‘2’, ‘2022-02-06’, ‘Lose’)
insert into Matches (player_id, match_day, result) values (‘2’, ‘2022-02-08’, ‘Lose’)
insert into Matches (player_id, match_day, result) values (‘3’, ‘2022-03-30’, ‘Win’)
select player_id, cut longest_streak
from (
select player_id, sum(if(result=‘Win’,1,0)) cut, row_number() over (partition by player_id order by sum(if(result=‘Win’,1,0)) desc ) rw
from (select *,
sum(if(lag_match_result = ‘Win’, 0, 1)) over (partition by player_id order by match_day) range_group
from (select *,
lag(result) over (partition by player_id order by match_day) lag_match_result
from Matches) a) b
group by player_id, range_group
) c
where rw = 1
2153. 每辆车的乘客人数 II
2153. 每辆车的乘客人数 II
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表: Buses+--------------+------+
| Column Name | Type |
+--------------+------+
| bus_id | int |
| arrival_time | int |
| capacity | int |
+--------------+------+
bus_id 包含唯一的值。
该表的每一行都包含关于公交车到达 LeetCode 站点的时间和它的容量 (空座位的数量) 的信息。
不会出现两辆公交车同时到达，所有公交车的容量都是正整数。表: Passengers+--------------+------+
| Column Name | Type |
+--------------+------+
| passenger_id | int |
| arrival_time | int |
+--------------+------+
passenger_id 包含唯一的值。
该表的每一行都包含乘客到达 LeetCode 站的时间信息。公交车和乘客到达 LeetCode 站。如果一辆公交车在 tbus 时间点到达车站，乘客在 tpassenger 到达车站，其中 tpassenger <= tbus，而该乘客没有赶上任何公交车，则该乘客将搭乘该公交车。此外，每辆公交车都有一个容量。如果在公交车到站的那一刻，等待的乘客超过了它的载客量 capacity，只有 capacity 个乘客才会搭乘该公交车。编写解决方案，报告使用每条总线的用户数量。返回按 bus_id 升序排序 的结果表。结果格式如下所示。示例 1:输入:
Buses 表:
+--------+--------------+----------+
| bus_id | arrival_time | capacity |
+--------+--------------+----------+
| 1 | 2 | 1 |
| 2 | 4 | 10 |
| 3 | 7 | 2 |
+--------+--------------+----------+
Passengers 表:
+--------------+--------------+
| passenger_id | arrival_time |
+--------------+--------------+
| 11 | 1 |
| 12 | 1 |
| 13 | 5 |
| 14 | 6 |
| 15 | 7 |
+--------------+--------------+
输出:
+--------+----------------+
| bus_id | passengers_cnt |
+--------+----------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+--------+----------------+
解释:
- 11 号乘客在时间 1 到达。
- 12 号乘客在时间 1 到达。
- 1 号公交车到达时间为 2，因为有一个空座位，所以搭载了 11 号乘客。- 2 号公交车在时间 4 到达，搭载了12 号乘客，因为它有 10 个空座位。- 13 号乘客在时间 5 到达。
- 14 号乘客在时间 6 到达。
- 15 号乘客在时间 7 到达。
- 3 号公交车在时间 7 到达，车上有两个空座位，搭载了 12 号和 13 号乘客。
Create table If Not Exists Buses (bus_id int, arrival_time int, capacity int)
Create table If Not Exists Passengers (passenger_id int, arrival_time int)
Truncate table Buses
insert into Buses (bus_id, arrival_time, capacity) values (‘1’, ‘2’, ‘1’)
insert into Buses (bus_id, arrival_time, capacity) values (‘2’, ‘4’, ‘10’)
insert into Buses (bus_id, arrival_time, capacity) values (‘3’, ‘7’, ‘2’)
Truncate table Passengers
insert into Passengers (passenger_id, arrival_time) values (‘11’, ‘1’)
insert into Passengers (passenger_id, arrival_time) values (‘12’, ‘1’)
insert into Passengers (passenger_id, arrival_time) values (‘13’, ‘5’)
insert into Passengers (passenger_id, arrival_time) values (‘14’, ‘6’)
insert into Passengers (passenger_id, arrival_time) values (‘15’, ‘7’)
WITH RECURSIVE bus_lag_info AS (
SELECT
*
,LAG(arrival_time, 1, 0) OVER(ORDER BY arrival_time) AS prev_arrival_time
FROM Buses
),
passenger_arrival_between_buses_info AS (
SELECT
b.bus_id
,b.arrival_time
,b.capacity
,COUNT(p.passenger_id) AS passenger_arrival_between_buses_cnt
,ROW_NUMBER() OVER(ORDER BY b.arrival_time) AS rnk
FROM bus_lag_info b
LEFT JOIN Passengers p
ON (p.arrival_time > b.prev_arrival_time AND p.arrival_time <= b.arrival_time)
GROUP BY 1, 2, 3
),
boarded_info AS (
SELECT
bus_id
,LEAST(capacity, passenger_arrival_between_buses_cnt) AS boarded_cnt
,GREATEST(0, passenger_arrival_between_buses_cnt - capacity) AS left_cnt
,rnk
FROM passenger_arrival_between_buses_info
WHERE rnk = 1
UNION ALL
SELECT
p.bus_id
,LEAST(capacity, left_cnt + passenger_arrival_between_buses_cnt) AS boarded_cnt
,GREATEST(0, left_cnt + passenger_arrival_between_buses_cnt - capacity) AS left_cnt
,p.rnk
FROM passenger_arrival_between_buses_info p, boarded_info b
WHERE p.rnk = b.rnk + 1
)
SELECT
bus_id
,boarded_cnt AS passengers_cnt
FROM boarded_info
ORDER BY bus_id;
先通过LAG()开窗获得上一班车的到达时间。
在步骤1的基础上，通过左连接Passengers表，统计出两辆bus间的到达的乘客。
递归主体，初始部分为第一辆bus到达。此时，上车乘客（boarded_cnt）为MIN{bus载客量，此前到达站点等待的乘客量}；注意，也会存在乘客剩下，剩余乘客（left_cnt）为MAX{0, 此前到达站点等待的乘客量 - bus载客量}。
递归部分类似，但需要考虑到上一辆bus开走后仍剩下的乘客（left_cnt）
递归条件则为在上一列bus排序上加+1。（所以在第二个cte中使用的row_number加以排序）
1767. 寻找没有被执行的任务对
1767. 寻找没有被执行的任务对
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表：Tasks+----------------+---------+
| Column Name | Type |
+----------------+---------+
| task_id | int |
| subtasks_count | int |
+----------------+---------+
task_id 具有唯一值的列。
task_id 表示的为主任务的id,每一个task_id被分为了多个子任务(subtasks)，subtasks_count表示为子任务的个数（n），它的值表示了子任务的索引从1到n。
本表保证2 <=subtasks_count<= 20。表： Executed+---------------+---------+
| Column Name | Type |
+---------------+---------+
| task_id | int |
| subtask_id | int |
+---------------+---------+
(task_id, subtask_id) 是该表中具有唯一值的列的组合。
每一行表示标记为task_id的主任务与标记为subtask_id的子任务被成功执行。
本表 保证 ，对于每一个task_id，subtask_id <= subtasks_count。编写解决方案报告没有被执行的（主任务，子任务）对，即没有被执行的（task_id, subtask_id）。以 任何顺序 返回即可。查询结果格式如下。示例 1：输入：
Tasks 表:
+---------+----------------+
| task_id | subtasks_count |
+---------+----------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 4 |
+---------+----------------+
Executed 表:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1 | 2 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
+---------+------------+
输出：
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
+---------+------------+
解释：
Task 1 被分成了 3 subtasks (1, 2, 3)。只有 subtask 2 被成功执行, 所以我们返回 (1, 1) 和 (1, 3) 这两个主任务子任务对。
Task 2 被分成了 2 subtasks (1, 2)。没有一个subtask被成功执行, 因此我们返回(2, 1)和(2, 2)。
Task 3 被分成了 4 subtasks (1, 2, 3, 4)。所有的subtask都被成功执行，因此对于Task 3,我们不返回任何值。
Create table If Not Exists Tasks (task_id int, subtasks_count int)
Create table If Not Exists Executed (task_id int, subtask_id int)
Truncate table Tasks
insert into Tasks (task_id, subtasks_count) values (‘1’, ‘3’)
insert into Tasks (task_id, subtasks_count) values (‘2’, ‘2’)
insert into Tasks (task_id, subtasks_count) values (‘3’, ‘4’)
Truncate table Executed
insert into Executed (task_id, subtask_id) values (‘1’, ‘2’)
insert into Executed (task_id, subtask_id) values (‘3’, ‘1’)
insert into Executed (task_id, subtask_id) values (‘3’, ‘2’)
insert into Executed (task_id, subtask_id) values (‘3’, ‘3’)
insert into Executed (task_id, subtask_id) values (‘3’, ‘4’)
with recursive t as
(
select task_id, subtasks_count from Tasks
union
select task_id, subtasks_count - 1 from t where subtasks_count > 1
)
select t.task_id,subtasks_count subtask_id
from t left join Executed e on t.task_id = e.task_id and t.subtasks_count = e.subtask_id
where e.subtask_id is null
1892. 页面推荐Ⅱ
1892. 页面推荐Ⅱ
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表： Friendship+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id,user2_id) 是 Friendship 表的主键(具有唯一值的列的组合)。
该表的每一行表示用户user1_id和user2_id是好友。表： Likes+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| page_id | int |
+-------------+---------+
(user_id,page_id) 是 Likes 表的主键(具有唯一值的列)。
该表的每一行表示user_id喜欢page_id。您正在为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id的 至少一个朋友喜欢 ，而 不被user_id喜欢 ，你的系统将 推荐 一个页面到user_id。编写一个解决方案来查找针对每个用户的所有可能的 页面建议 。每个建议应该在结果表中显示为一行，包含以下列:user_id: 系统向其提出建议的用户的ID。
page_id: 推荐为 user_id 的页面ID。.
friends_likes: user_id 对应 page_id 的好友数。
以 任意顺序 返回结果表。返回结果格式示例如下。示例 1:输入：
Friendship 表:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 6 | 1 |
+----------+----------+
Likes 表:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 88 |
| 2 | 23 |
| 3 | 24 |
| 4 | 56 |
| 5 | 11 |
| 6 | 33 |
| 2 | 77 |
| 3 | 77 |
| 6 | 88 |
+---------+---------+
输出：
+---------+---------+---------------+
| user_id | page_id | friends_likes |
+---------+---------+---------------+
| 1 | 77 | 2 |
| 1 | 23 | 1 |
| 1 | 24 | 1 |
| 1 | 56 | 1 |
| 1 | 33 | 1 |
| 2 | 24 | 1 |
| 2 | 56 | 1 |
| 2 | 11 | 1 |
| 2 | 88 | 1 |
| 3 | 88 | 1 |
| 3 | 23 | 1 |
| 4 | 88 | 1 |
| 4 | 77 | 1 |
| 4 | 23 | 1 |
| 5 | 77 | 1 |
| 5 | 23 | 1 |
+---------+---------+---------------+
解释：
以用户1为例:
—用户1是用户2、3、4、6的好友。
-推荐页面有23(用户2喜欢)，24(用户3喜欢)，56(用户3喜欢)，33(用户6喜欢)，77(用户2和用户3喜欢)。
-请注意，第88页不推荐，因为用户1已经喜欢它。另一个例子是用户6:
—用户6是用户1的好友。
-用户1只喜欢了88页，但用户6已经喜欢了。因此，用户6没有推荐。您可以使用类似的过程为用户2、3、4和5推荐页面。
Create table If Not Exists Friendship (user1_id int, user2_id int)
Create table If Not Exists Likes (user_id int, page_id int)
Truncate table Friendship
insert into Friendship (user1_id, user2_id) values (‘1’, ‘2’)
insert into Friendship (user1_id, user2_id) values (‘1’, ‘3’)
insert into Friendship (user1_id, user2_id) values (‘1’, ‘4’)
insert into Friendship (user1_id, user2_id) values (‘2’, ‘3’)
insert into Friendship (user1_id, user2_id) values (‘2’, ‘4’)
insert into Friendship (user1_id, user2_id) values (‘2’, ‘5’)
insert into Friendship (user1_id, user2_id) values (‘6’, ‘1’)
Truncate table Likes
insert into Likes (user_id, page_id) values (‘1’, ‘88’)
insert into Likes (user_id, page_id) values (‘2’, ‘23’)
insert into Likes (user_id, page_id) values (‘3’, ‘24’)
insert into Likes (user_id, page_id) values (‘4’, ‘56’)
insert into Likes (user_id, page_id) values (‘5’, ‘11’)
insert into Likes (user_id, page_id) values (‘6’, ‘33’)
insert into Likes (user_id, page_id) values (‘2’, ‘77’)
insert into Likes (user_id, page_id) values (‘3’, ‘77’)
insert into Likes (user_id, page_id) values (‘6’, ‘88’)
select user1_id user_id , b.page_id page_id,count(1) friends_likes
from (select user1_id,user2_id from Friendship a union
select user2_id,user1_id from Friendship a
)a left join Likes b on a.user2_id=b.user_id
where not exists (select 1 from Likes t where t.user_id=a.user1_id and t.page_id=b.page_id)
group by a.user1_id,b.page_id
互为好友的问题，第一步就是翻转union all
1917. Leetcodify 好友推荐
1917. Leetcodify 好友推荐
已解答
困难
相关标签
相关企业
SQL Schema
Pandas Schema
表： Listens+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
这个表没有主键，可能存在重复项。
表中的每一行表示用户 user_id 在 day 这一天收听的歌曲 song_id。表： Friendship+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id, user2_id) 是这个表的主键。
表中的每一行表示 user1_id 和 user2_id 是好友。
注意，user1_id < user2_id。写出 SQL 语句，为 Leetcodify 用户推荐好友。我们将符合下列条件的用户 x 推荐给用户 y ：用户 x 和 y 不是好友，且
用户 x 和 y 在同一天收听了相同的三首或更多不同歌曲。
注意，好友推荐是单向的，这意味着如果用户 x 和用户 y 需要互相推荐给对方，结果表需要将用户 x 推荐给用户 y 并将用户 y 推荐给用户 x。另外，结果表不得出现重复项（即，用户 y 不可多次推荐给用户 x ）。按任意顺序返回结果表。查询格式如下示例所示：示例 1:输入：
Listens 表：
+---------+---------+------------+
| user_id | song_id | day |
+---------+---------+------------+
| 1 | 10 | 2021-03-15 |
| 1 | 11 | 2021-03-15 |
| 1 | 12 | 2021-03-15 |
| 2 | 10 | 2021-03-15 |
| 2 | 11 | 2021-03-15 |
| 2 | 12 | 2021-03-15 |
| 3 | 10 | 2021-03-15 |
| 3 | 11 | 2021-03-15 |
| 3 | 12 | 2021-03-15 |
| 4 | 10 | 2021-03-15 |
| 4 | 11 | 2021-03-15 |
| 4 | 13 | 2021-03-15 |
| 5 | 10 | 2021-03-16 |
| 5 | 11 | 2021-03-16 |
| 5 | 12 | 2021-03-16 |
+---------+---------+------------+
Friendship 表：
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
输出：
+---------+----------------+
| user_id | recommended_id |
+---------+----------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
+---------+----------------+
解释
用户 1 和 2 在同一天收听了歌曲 10、11 和 12，但他们已经是好友了。
用户 1 和 3 在同一天收听了歌曲 10、11 和 12。由于他们不是好友，所以我们给他们互相推荐为好友。
用户 1 和 4 没有收听三首相同的歌曲。
用户 1 和 5 收听了歌曲 10、11 和 12，但不是在同一天收听的。类似地，我们可以发现用户 2 和 3 在同一天收听了歌曲 10、11 和 12，且他们不是好友，所以我们给他们互相推荐为好友。
Create table If Not Exists Listens (user_id int, song_id int, day date)
Create table If Not Exists Friendship (user1_id int, user2_id int)
Truncate table Listens
insert into Listens (user_id, song_id, day) values (‘1’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘1’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘1’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘13’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘10’, ‘2021-03-16’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘11’, ‘2021-03-16’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘12’, ‘2021-03-16’)
Truncate table Friendship
insert into Friendship (user1_id, user2_id) values (‘1’, ‘2’)
SELECT DISTINCT t.user1_id AS user_id,t.user2_id AS recommended_id
FROM
(SELECT a.user_id AS user1_id
,b.user_id AS user2_id
,a.song_id
,a.day
,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt
FROM (SELECT DISTINCT * FROM Listens) a
INNER JOIN (SELECT DISTINCT * FROM Listens) b
ON a.user_id <> b.user_id
AND a.song_id = b.song_id
AND a.day = b.day) t
LEFT JOIN Friendship t1
ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id
LEFT JOIN Friendship t2
ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id
WHERE tt >= 3 AND t1.user1_id IS NULL AND t2.user1_id IS NULL
1919. 兴趣相同的朋友
1919. 兴趣相同的朋友
已解答
困难
相关标签
相关企业
SQL Schema
表: Listens+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
该表没有主键，因此会存在重复的行。
该表的每一行所代表的含义是：用户（user_id）在某天（day）听了某首歌曲（song_id）。表: Friendship+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id, user2_id) 是该表的主键。
该表的每一行所代表的含义是，用户（user1_id, user2_id）是朋友。
注意：user1_id < user2_id。请写一段SQL查询获取到兴趣相同的朋友。用户 x 和 用户 y 是兴趣相同的朋友，需满足下述条件：用户 x 和 y 是朋友，并且
用户 x and y 在同一天内听过相同的歌曲，且数量大于等于三首.
结果表 无需排序 。注意：返回的结果需要和源数据表的呈现方式相同 （例如， 需满足 user1_id < user2_id）。结果表的格式如下例。示例 1：输入：
Listens table:
+---------+---------+------------+
| user_id | song_id | day |
+---------+---------+------------+
| 1 | 10 | 2021-03-15 |
| 1 | 11 | 2021-03-15 |
| 1 | 12 | 2021-03-15 |
| 2 | 10 | 2021-03-15 |
| 2 | 11 | 2021-03-15 |
| 2 | 12 | 2021-03-15 |
| 3 | 10 | 2021-03-15 |
| 3 | 11 | 2021-03-15 |
| 3 | 12 | 2021-03-15 |
| 4 | 10 | 2021-03-15 |
| 4 | 11 | 2021-03-15 |
| 4 | 13 | 2021-03-15 |
| 5 | 10 | 2021-03-16 |
| 5 | 11 | 2021-03-16 |
| 5 | 12 | 2021-03-16 |
+---------+---------+------------+
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 2 | 4 |
| 2 | 5 |
+----------+----------+
输出：
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
解释：
用户 1 和 2 是朋友, 并且他们在同一天内都听了10、11、12的歌曲。所以，他们是兴趣相同的朋友。
用户 1 和 3 在同一天内都听了10、11、12的歌曲，但他们不是朋友。
用户 2 和 4 是朋友，但他们同一天内听过相同的歌曲的数量小于3。
用户 2 和 5 是朋友，并且在都听了了10、11、12的歌曲，但不在同一天内。
Create table If Not Exists Listens (user_id int, song_id int, day date)
Create table If Not Exists Friendship (user1_id int, user2_id int)
Truncate table Listens
insert into Listens (user_id, song_id, day) values (‘1’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘1’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘1’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘2’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘3’, ‘12’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘10’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘11’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘4’, ‘13’, ‘2021-03-15’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘10’, ‘2021-03-16’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘11’, ‘2021-03-16’)
insert into Listens (user_id, song_id, day) values (‘5’, ‘12’, ‘2021-03-16’)
Truncate table Friendship
insert into Friendship (user1_id, user2_id) values (‘1’, ‘2’)
insert into Friendship (user1_id, user2_id) values (‘2’, ‘4’)
insert into Friendship (user1_id, user2_id) values (‘2’, ‘5’)
select distinct t.user1_id,t.user2_id from (
select a.user_id user1_id ,b.user_id user2_id,a.song_id,a.day from Listens a
left join Listens b on a.user_id<> b.user_id
inner join Friendship c on a.user_id=c.user1_id and b.user_id=c.user2_id
where a.song_id=b.song_id and a.day=b.day
) t group by t.user1_id,t.user2_id,day having count(distinct song_id)>=3
1972. 同一天的第一个电话和最后一个电话
1972. 同一天的第一个电话和最后一个电话
已解答
困难
相关标签
相关企业
SQL Schema
表: Calls+--------------+----------+
| Column Name | Type |
+--------------+----------+
| caller_id | int |
| recipient_id | int |
| call_time | datetime |
+--------------+----------+
(caller_id, recipient_id, call_time) 是这个表的主键。
每一行所含的时间信息都是关于caller_id 和recipient_id的。编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。结果请放在一个任意次序约束的表中。查询结果格式如下所示：输入：
Calls table:
+-----------+--------------+---------------------+
| caller_id | recipient_id | call_time |
+-----------+--------------+---------------------+
| 8 | 4 | 2021-08-24 17:46:07 |
| 4 | 8 | 2021-08-24 19:57:13 |
| 5 | 1 | 2021-08-11 05:28:44 |
| 8 | 3 | 2021-08-17 04:04:15 |
| 11 | 3 | 2021-08-17 13:07:00 |
| 8 | 11 | 2021-08-17 22:22:22 |
+-----------+--------------+---------------------+
输出：
+---------+
| user_id |
+---------+
| 1 |
| 4 |
| 5 |
| 8 |
+---------+
解释：
在 2021-08-24，这天的第一个电话和最后一个电话都是在user 8和user 4之间。user8应该被包含在答案中。
同样的，user 4在2 021-08-24 的第一个电话和最后一个电话都是和user 8的。user 4也应该被包含在答案中。
在 2021-08-11，user 1和5有一个电话。这个电话是他们彼此当天的唯一一个电话。因此这个电话是他们当天的第一个电话也是最后一个电话，他们都应该被包含在答案中。
{“headers”: {“Calls”: [“caller_id”, “recipient_id”, “call_time”]}, “rows”: {“Calls”: [[32, 144, “2021-09-09 12:44:09”], [11, 114, “2021-09-13 03:11:20”], [31, 32, “2021-09-12 01:22:07”], [34, 21, “2021-09-09 09:26:18”], [32, 144, “2021-09-11 20:54:34”], [3, 71, “2021-09-10 06:23:23”], [4, 86, “2021-09-09 08:55:36”], [35, 80, “2021-09-09 20:01:52”], [33, 5, “2021-09-13 02:56:57”], [18, 148, “2021-09-13 21:53:24”], [36, 70, “2021-09-11 13:02:13”], [21, 93, “2021-09-13 10:08:54”], [37, 149, “2021-09-11 02:35:34”], [33, 6, “2021-09-10 03:14:44”], [20, 159, “2021-09-09 01:32:15”], [28, 138, “2021-09-10 11:47:47”], [36, 16, “2021-09-10 21:13:48”], [31, 109, “2021-09-11 13:25:10”], [12, 59, “2021-09-10 19:45:29”], [32, 116, “2021-09-11 12:54:49”], [14, 96, “2021-09-11 08:50:32”], [35, 111, “2021-09-12 22:44:21”], [29, 76, “2021-09-09 08:28:45”], [16, 70, “2021-09-11 01:39:15”], [35, 58, “2021-09-10 00:59:53”], [29, 72, “2021-09-09 14:10:20”], [36, 104, “2021-09-11 04:58:43”], [33, 49, “2021-09-13 06:56:22”], [15, 45, “2021-09-09 22:13:12”], [20, 58, “2021-09-09 16:32:45”], [34, 148, “2021-09-12 23:31:47”], [27, 49, “2021-09-12 08:39:43”], [29, 56, “2021-09-10 01:54:44”], [30, 12, “2021-09-11 18:00:15”], [29, 152, “2021-09-11 13:28:16”], [5, 15, “2021-09-10 19:09:09”], [8, 136, “2021-09-10 08:43:14”], [25, 114, “2021-09-11 01:09:08”], [11, 134, “2021-09-11 20:44:04”], [1, 8, “2021-09-09 23:44:02”], [33, 44, “2021-09-10 17:55:32”], [27, 57, “2021-09-13 14:12:33”], [37, 146, “2021-09-10 22:23:38”], [6, 114, “2021-09-12 12:13:10”], [35, 4, “2021-09-12 03:29:26”], [18, 111, “2021-09-11 08:49:54”], [1, 156, “2021-09-10 13:49:59”], [1, 78, “2021-09-11 01:55:52”], [3, 154, “2021-09-10 05:01:27”], [2, 149, “2021-09-13 04:50:39”], [21, 86, “2021-09-10 02:24:13”], [21, 68, “2021-09-09 18:44:16”], [13, 128, “2021-09-13 16:43:23”], [5, 157, “2021-09-09 10:27:55”], [37, 30, “2021-09-13 16:46:27”], [5, 40, “2021-09-10 21:04:06”], [10, 156, “2021-09-10 11:37:30”], [23, 95, “2021-09-09 14:21:49”], [36, 84, “2021-09-09 00:17:18”], [13, 159, “2021-09-13 12:15:04”], [19, 3, “2021-09-13 12:17:57”], [25, 117, “2021-09-10 18:37:38”], [19, 30, “2021-09-10 19:08:33”], [31, 114, “2021-09-13 11:28:44”], [36, 71, “2021-09-11 14:25:36”], [13, 5, “2021-09-12 07:05:00”], [30, 25, “2021-09-11 15:28:27”], [3, 51, “2021-09-11 19:10:38”], [33, 127, “2021-09-13 02:31:56”], [25, 16, “2021-09-12 20:47:59”], [3, 6, “2021-09-13 03:20:07”], [19, 44, “2021-09-11 08:33:37”], [5, 148, “2021-09-10 08:42:41”], [26, 120, “2021-09-09 05:43:34”], [4, 122, “2021-09-09 03:39:40”], [14, 63, “2021-09-11 09:10:50”], [14, 29, “2021-09-10 03:33:48”], [22, 76, “2021-09-10 09:09:14”], [25, 20, “2021-09-13 00:33:25”], [7, 22, “2021-09-13 12:26:17”], [21, 45, “2021-09-11 20:10:24”], [3, 111, “2021-09-10 12:49:44”], [33, 46, “2021-09-12 04:54:45”]]}}
select distinct caller_id user_id from (
select caller_id,
recipient_id,
call_time,
date(call_time) dt,
max(call_time) over (partition by caller_id,date_format(call_time, ‘%Y-%m-%d’) ) max_time,
min(call_time) over (partition by caller_id,date_format(call_time, ‘%Y-%m-%d’) ) min_time
from (select a.caller_id, a.recipient_id, a.call_time
from Calls a
union all
select b.recipient_id, b.caller_id, b.call_time
from Calls b) t
) t2 group by caller_id,recipient_id,dt having min(call_time)=max(min_time) and max(call_time)=max(max_time)
2004. 职员招聘人数
2004. 职员招聘人数
已解答
困难
相关标签
相关企业
SQL Schema
表: Candidates+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int |
| experience | enum |
| salary | int |
+-------------+------+
employee_id是此表的主键列。
经验是包含一个值（“高级”、“初级”）的枚举类型。
此表的每一行都显示候选人的id、月薪和经验。一家公司想雇佣新员工。公司的工资预算是 70000 美元。公司的招聘标准是：雇佣最多的高级员工。
在雇佣最多的高级员工后，使用剩余预算雇佣最多的初级员工。
编写一个SQL查询，查找根据上述标准雇佣的高级员工和初级员工的数量。
按 任意顺序 返回结果表。
查询结果格式如下例所示。示例 1:输入:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 10000 |
| 9 | Junior | 10000 |
| 2 | Senior | 20000 |
| 11 | Senior | 20000 |
| 13 | Senior | 50000 |
| 4 | Junior | 40000 |
+-------------+------------+--------+
输出:
+------------+---------------------+
| experience | accepted_candidates |
+------------+---------------------+
| Senior | 2 |
| Junior | 2 |
+------------+---------------------+
说明：
我们可以雇佣2名ID为（2,11）的高级员工。由于预算是7万美元，他们的工资总额是4万美元，我们还有3万美元，但他们不足以雇佣ID为13的高级员工。
我们可以雇佣2名ID为（1,9）的初级员工。由于剩下的预算是3万美元，他们的工资总额是2万美元，我们还有1万美元，但他们不足以雇佣ID为4的初级员工。
示例 2：
输入:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 10000 |
| 9 | Junior | 10000 |
| 2 | Senior | 80000 |
| 11 | Senior | 80000 |
| 13 | Senior | 80000 |
| 4 | Junior | 40000 |
+-------------+------------+--------+
输出:
+------------+---------------------+
| experience | accepted_candidates |
+------------+---------------------+
| Senior | 0 |
| Junior | 3 |
+------------+---------------------+
解释：
我们不能用目前的预算雇佣任何高级员工，因为我们需要至少80000美元来雇佣一名高级员工。
我们可以用剩下的预算雇佣三名初级员工Create table If Not Exists Candidates (employee_id int, experience ENUM(‘Senior’, ‘Junior’), salary int)
Truncate table Candidates
insert into Candidates (employee_id, experience, salary) values (‘1’, ‘Junior’, ‘10000’)
insert into Candidates (employee_id, experience, salary) values (‘9’, ‘Junior’, ‘10000’)
insert into Candidates (employee_id, experience, salary) values (‘2’, ‘Senior’, ‘20000’)
insert into Candidates (employee_id, experience, salary) values (‘11’, ‘Senior’, ‘20000’)
insert into Candidates (employee_id, experience, salary) values (‘13’, ‘Senior’, ‘50000’)
insert into Candidates (employee_id, experience, salary) values (‘4’, ‘Junior’, ‘40000’)
with a as (select 70000 - sum(salary) over (order by salary rows unbounded preceding) diff_salary
from Candidates
where experience = ‘Senior’),
b as (
select ifnull((select min(diff_salary) from a where diff_salary>0),70000) -
sum(salary) over (order by salary rows unbounded preceding) diff_salary
from Candidates
where experience = ‘Junior’
)
select ‘Senior’ ,cast(ifnull(count(1),0) as signed ) accepted_candidates from a where diff_salary>=0
union all
select ‘Junior’ ,ifnull(count(1),0) accepted_candidates from b where diff_salary>=0
更多推荐
力扣数据库
发布评论