Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1098.html
1098. Unpopular Books
Level
Medium
Description
Table: Books
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| book_id | int |
| name | varchar |
| available_from | date |
+----------------+---------+
book_id is the primary key of this table.
Table: Orders
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| order_id | int |
| book_id | int |
| quantity | int |
| dispatch_date | date |
+----------------+---------+
order_id is the primary key of this table.
book_id is a foreign key to the Books table.
Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than 1 month from today. Assume today is 2019-06-23.
The query result format is in the following example:
Books table:
+---------+--------------------+----------------+
| book_id | name | available_from |
+---------+--------------------+----------------+
| 1 | "Kalila And Demna" | 2010-01-01 |
| 2 | "28 Letters" | 2012-05-12 |
| 3 | "The Hobbit" | 2019-06-10 |
| 4 | "13 Reasons Why" | 2019-06-01 |
| 5 | "The Hunger Games" | 2008-09-21 |
+---------+--------------------+----------------+
Orders table:
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1 | 1 | 2 | 2018-07-26 |
| 2 | 1 | 1 | 2018-11-05 |
| 3 | 3 | 8 | 2019-06-11 |
| 4 | 4 | 6 | 2019-06-05 |
| 5 | 4 | 5 | 2019-06-20 |
| 6 | 5 | 9 | 2009-02-02 |
| 7 | 5 | 8 | 2010-04-13 |
+----------+---------+----------+---------------+
Result table:
+-----------+--------------------+
| book_id | name |
+-----------+--------------------+
| 1 | "Kalila And Demna" |
| 2 | "28 Letters" |
| 5 | "The Hunger Games" |
+-----------+--------------------+
Solution
If a book has been sold less than 10 copies, then there are two cases. The first case is that the book has been sold at least 1 copy but less than 10 copies. The second case is that the book has never been sold.
For the first case, join Books and Orders and select the books with sum(quantity) < 10.
For the second case, select the books such that the book_id values are not in Orders.
The selection criteria also include that available_from is more than 1 month from the given date, and dispatch_date is in the last year.
# Write your MySQL query statement below
select Books.book_id, name from Books join Orders
on Books.book_id = Orders.book_id
where available_from < '2019-05-23'
and dispatch_date between '2018-06-23' and '2019-06-23'
group by Books.book_id
having sum(quantity) < 10
union
select book_id, name from Books
where available_from < '2019-05-23'
and book_id not in (
select distinct book_id from Orders where dispatch_date between '2018-06-23' and '2019-06-23'
);