「SQL面试题库」 No_123 The Most Recent Orders for Each Product

2023-10-16 10:51:07 浏览数 (1)

今日真题

题目介绍： The Most Recent Orders for Each Product the-most-recent-orders-for-each-product

难度中等

SQL架构

Table:

代码语言：javascript复制
Customers
代码语言：javascript复制
 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| customer_id   | int     |
| name          | varchar |
 --------------- --------- 
customer_id is the primary key for this table.
This table contains information about the customers.

Table:

代码语言：javascript复制
Orders
代码语言：javascript复制
 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| product_id    | int     |
 --------------- --------- 
order_id is the primary key for this table.
This table contains information about the orders made by customer_id.
There will be no product ordered by the same user more than once in one day.

Table:

代码语言：javascript复制
Products
代码语言：javascript复制
 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| product_id    | int     |
| product_name  | varchar |
| price         | int     |
 --------------- --------- 
product_id is the primary key for this table.
This table contains information about the Products.

Write an SQL query to find the most recent order(s) of each product.

Return the result table sorted by

代码语言：javascript复制
product_name

in ascending order and in case of a tie by the

代码语言：javascript复制
product_id

in ascending order. If there still a tie, order them by the

代码语言：javascript复制
order_id

in ascending order.

The query result format is in the following example:

``` Customers ------------- ----------- | customer_id | name | ------------- ----------- | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | ------------- -----------

Orders ---------- ------------ ------------- ------------ | order_id | order_date | customer_id | product_id | ---------- ------------ ------------- ------------ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 1 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | ---------- ------------ ------------- ------------

Products ------------ -------------- ------- | product_id | product_name | price | ------------ -------------- ------- | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | ------------ -------------- -------

Result table: -------------- ------------ ---------- ------------ | product_name | product_id | order_id | order_date | -------------- ------------ ---------- ------------ | keyboard | 1 | 6 | 2020-08-01 | | keyboard | 1 | 7 | 2020-08-01 | | mouse | 2 | 8 | 2020-08-03 | | screen | 3 | 3 | 2020-08-29 | -------------- ------------ ---------- ------------ keyboard's most recent order is in 2020-08-01, it was ordered two times this day. mouse's most recent order is in 2020-08-03, it was ordered only once this day. screen's most recent order is in 2020-08-29, it was ordered only once this day. The hard disk was never ordered and we don't include it in the result table. ```

代码语言：javascript复制
sql
select product_name,product_id,order_id,order_date
from
(
select product_name ,o.product_id ,order_id,order_date ,
    rank() over(partition by o.product_id order by order_date desc) rk
from Orders o left join Products p
on o.product_id =p.product_id 
)t1
where rk =1
order by product_name,product_id,order_id
  • 已经有灵感了？在评论区写下你的思路吧！
sql each orders product table

0 人点赞