# LeetCode 1098: Unpopular Books

## 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

```sql
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.