LeetCode 1098. Unpopular Books SQL Solution
Problem
LeetCode SQL Problem
- Unpopular Books
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 |
Solution
- MySQL
- TSQL
Unpopular Books
SET @today = '2019-06-23';
SET @one_month_ago = DATE_ADD(@today, INTERVAL -30 DAY);
SET @one_year_ago = DATE_ADD(@today, INTERVAL -1 YEAR);
-- Report the books that have sold less than 10 copies in the last year
SELECT B.book_id
,B.name
FROM Books AS B
LEFT JOIN Orders AS O ON B.book_id = O.book_id
AND O.dispatch_date BETWEEN @one_year_ago
AND @today
-- Exclude books that have been available for less than 1 month from today.
-- Assume today is 2019-06-23.
WHERE B.available_from < @one_month_ago
GROUP BY B.book_id
,B.name
HAVING count(O.order_id) <= 10
Unpopular Books
DECLARE @today DATE = '2019-06-23';
DECLARE @one_month_ago DATE = dateadd(DAY, - 30, @today);
DECLARE @one_year_ago DATE = dateadd(YEAR, - 1, @today);
-- Report the books that have sold less than 10 copies in the last year
SELECT B.book_id
,B.name
FROM Books AS B
LEFT JOIN Orders AS O ON B.book_id = O.book_id
AND O.dispatch_date BETWEEN @one_year_ago
AND @today
-- Exclude books that have been available for less than 1 month from today.
-- Assume today is 2019-06-23.
WHERE B.available_from < @one_month_ago
GROUP BY B.book_id
,B.name
HAVING count(O.order_id) <= 10
Query Output
| book_id | name |
|---|---|
| 2 | 28 Letters |
| 1 | Kalila And Demna |
| 5 | The Hunger Games |