Unpopular Books
Amazon
Problem Statement
Amazon wants to clear out stock of unpopular books. Write an SQL query to find books that have sold less than 10 copies in the last year (from 2025-02-09 to 2026-02-09).
Rules:
- Exclude books that were released in the last month (on or after 2026-01-09).
- Include books that had zero sales in the last year.
- Output columns: book_id, name.
- Results must be sorted by book_id in ascending order.
Tests your understanding of
Date Manipulation, Joins and Aggregation
Input Tables
Books
| book_id(INTEGER) | name(VARCHAR) | available_from(DATE) |
|---|---|---|
| 1 | SQL Basics | 2020-01-01 |
| 2 | Advanced Python | 2021-05-10 |
| 3 | Data Science Pro | 2026-01-20 |
| 4 | Legacy Code | 2015-12-01 |
Orders
| order_id(INTEGER) | book_id(INTEGER) | quantity(INTEGER) | dispatch_date(DATE) |
|---|---|---|---|
| 101 | 1 | 5 | 2025-12-01 |
| 102 | 1 | 6 | 2025-06-01 |
Inventory_Status
| book_id(INTEGER) | warehouse_id(INTEGER) |
|---|---|
| 1 | 501 |
Expected Output
| book_id(INTEGER) | name(VARCHAR) |
|---|---|
| 2 | Advanced Python |
| 4 | Legacy Code |
Tags
MediumDate ManipulationJoinsAggregation
20-25 min
48%