Unpopular Books - Practice Coding Problems

Unpopular Books - Problem

Database Unknown

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

Understanding the Visualization

Filter Books

Books available ≥1 month

LEFT JOIN

Match with last year's orders

Aggregate

Sum quantities < 10 copies

Key Takeaway

🎯 Key Insight: LEFT JOIN is essential when you need to include records with no matches (books with zero sales)

Asked in

A Amazon 12 M Microsoft 8 🍎 Apple 5

Use LEFT JOIN to combine Books with Orders, filtering by date ranges for both availability (≥1 month) and sales period (last year), then GROUP BY and HAVING to find books with less than 10 total copies sold.

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

Common Approaches

Approach	Time	Space	Notes
✓ Left Join with Date Filtering	O(n + m)	O(n)	Use LEFT JOIN to include books with no recent orders, then filter by sales count and availability date

Left Join with Date Filtering — Algorithm Steps

Step 1: Filter books available for at least one month (before 2019-05-23)
Step 2: LEFT JOIN with Orders to include books with no recent sales
Step 3: Filter orders to last year (2018-06-23 to 2019-06-23)
Step 4: GROUP BY book and sum quantities, filtering for less than 10 copies

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Books

Available before 2019-05-23

Left Join

Match with last year's orders

Aggregate

Sum quantities and filter < 10

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of books and orders tables

✓ Linear Growth

Space Complexity

O(n)

Space for grouping results by book

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Left Join with Date Filtering — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler