Unpopular Books - Problem

You are given two tables: Books and Orders.

The Books table contains information about books with their availability dates. The Orders table contains order information including book quantities and dispatch dates.

Write a SQL query to find books that have sold less than 10 copies in the last year, excluding books that have been available for less than one month from today.

Assume today is 2019-06-23.

Return the result in any order.

Table Schema

Books
Column Name Type Description
book_id PK int Primary key, unique book identifier
name varchar Book name
available_from date Date when book became available
Primary Key: book_id
Orders
Column Name Type Description
order_id PK int Primary key, unique order identifier
book_id int Foreign key referencing Books table
quantity int Number of copies ordered
dispatch_date date Date when order was dispatched
Primary Key: order_id

Input & Output

Example 1 — Mixed Sales Performance
Input Tables:
Books
book_id name available_from
1 Kalila And Demna 2010-01-01
2 28 Letters 2019-06-01
3 The Hunger Games 2008-09-01
Orders
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 3 39 2019-05-07
Output:
book_id name
1 Kalila And Demna
💡 Note:

Book 1: Available since 2010 (>1 month), sold 3 copies total in last year (2+1) < 10 ✓

Book 2: Available since 2019-06-01, only 22 days before today (2019-06-23) < 1 month ✗

Book 3: Available since 2008 (>1 month), sold 47 copies total in last year (8+39) ≥ 10 ✗
Example 2 — Books with No Sales
Input Tables:
Books
book_id name available_from
4 Zero Sales Book 2018-01-01
5 New Release 2019-06-20
Orders
order_id book_id quantity dispatch_date
Output:
book_id name
4 Zero Sales Book
💡 Note:

Book 4: Available since 2018 (>1 month), no orders in last year (0 copies) < 10 ✓

Book 5: Available since 2019-06-20, only 3 days before today < 1 month ✗

Constraints

  • 1 ≤ book_id ≤ 1000
  • 1 ≤ order_id ≤ 1000
  • 1 ≤ quantity ≤ 100
  • Today's date is 2019-06-23
  • Last year is defined as 2018-06-23 to 2019-06-23

Visualization

Tap to expand
Unpopular Books - SQL Query Solution INPUT TABLES Books Table book_id | name 1 | "Kalila" 2 | "44 Letters" 3 | "Dreams" 4 | "New Book" available_from column Orders Table order_id | book_id | qty | date 1 | 1 | 2 | 2018-07-26 2 | 2 | 1 | 2018-08-14 3 | 3 | 8 | 2019-06-11 ... Reference: 2019-06-23 ALGORITHM STEPS 1 Filter Books available_from before 2019-05-23 (1 month ago) 2 Filter Orders dispatch_date in last year (after 2018-06-23) 3 LEFT JOIN + GROUP BY Join books with orders Group by book_id 4 HAVING Clause SUM(quantity) less than 10 or NULL (no orders) SELECT b.book_id, b.name FROM Books b LEFT JOIN Orders o ON ... WHERE available_from ... GROUP BY ... HAVING ... COALESCE(SUM(qty),0) < 10 FINAL RESULT Unpopular Books book_id | name 1 | "Kalila" 2 | "44 Letters" 5 | "Old Classic" (less than 10 copies sold) Excluded Books: Book 4 - Too new (less than 1 month available) Book 3 - Popular (sold 10+ copies in last year) Key Insight: Use LEFT JOIN to include books with zero orders. COALESCE handles NULL sums from books with no matching orders. Two date filters: one for book availability (1 month) and one for order dates (1 year) ensure accurate "unpopular" classification. TutorialsPoint - Unpopular Books | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 5
23.4K Views
Medium Frequency
~12 min Avg. Time
845 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen