Skip to content

LeetCode 1098: Unpopular Books

A clear explanation of finding books with fewer than 10 sales in the last year that were not sold in the last year using LEFT JOIN and GROUP BY.

leetcode database

Problem Restatement

We have Books(book_id, name, available_from) and Orders(order_id, book_id, quantity, dispatch_date) tables.

Report books that have been available for more than 1 month (available before 2019-01-01) and sold fewer than 10 copies in the last year (between 2018-06-23 and 2019-06-23).

Edge Cases

  • Match the aggregate to the question: use raw COUNT or SUM for rows/amounts, and COUNT(DISTINCT ...) only for unique entities.
  • Tie cases should return every qualifying row unless the statement asks for a single row.
  • For outer joins, keep filters on the joined table in the ON clause when unmatched rows must remain visible.

Common Pitfalls

  • Do not put aggregate filters in WHERE; use HAVING after GROUP BY.
  • When the answer needs all tied winners, avoid LIMIT 1 unless the statement explicitly asks for one row.

Implementation 

SELECT b.book_id, b.name
FROM Books b
LEFT JOIN Orders o
    ON b.book_id = o.book_id
    AND o.dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'
WHERE b.available_from < '2019-01-01'
GROUP BY b.book_id, b.name
HAVING COALESCE(SUM(o.quantity), 0) < 10;

Code Explanation

LEFT JOIN ensures books with no orders in the last year are included (with NULL quantities).

WHERE b.available_from < '2019-01-01' filters out recently published books.

HAVING COALESCE(SUM(o.quantity), 0) < 10 keeps only books with fewer than 10 total copies sold in the date range.

COALESCE handles NULL sums (books with no orders) by treating them as 0.