Problem

Table: Books

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id is the primary key (column with unique values) of this table.

Table: Orders

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id is the primary key (column with unique values) of this table.
book_id is a foreign key (reference column) to the Books table.

Write a solution to report the books that have sold less than10 copies in the last year, excluding books that have been available for less than one month from today. Assume today is2019-06-23.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Input: 
Books table:
+---------+--------------------+----------------+
| book_id | name               | available_from |
+---------+--------------------+----------------+
| 1       | "Kalila And Demna" | 2010-01-01     |
| 2       | "28 Letters"       | 2012-05-12     |
| 3       | "The Hobbit"       | 2019-06-10     |
| 4       | "13 Reasons Why"   | 2019-06-01     |
| 5       | "The Hunger Games" | 2008-09-21     |
+---------+--------------------+----------------+
Orders table:
+----------+---------+----------+---------------+
| 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        | 4       | 6        | 2019-06-05    |
| 5        | 4       | 5        | 2019-06-20    |
| 6        | 5       | 9        | 2009-02-02    |
| 7        | 5       | 8        | 2010-04-13    |
+----------+---------+----------+---------------+
Output: 
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

Solution

Method 1 – Aggregation and Filtering

Intuition

We need to find books that have sold less than 10 copies in the last year, but only if they have been available for at least one month as of 2019-06-23. We aggregate order quantities and filter by book availability.

Approach

  1. Exclude books available after 2019-05-23 (less than one month before 2019-06-23).
  2. For each book, sum the quantity of orders with dispatch_date between 2018-06-23 and 2019-06-23.
  3. Select books with total quantity < 10, or with no orders in the last year.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT b.book_id, b.name
FROM Books b
LEFT JOIN (
  SELECT book_id, SUM(quantity) AS total_sold
  FROM Orders
  WHERE dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'
  GROUP BY book_id
) o ON b.book_id = o.book_id
WHERE b.available_from <= '2019-05-23'
  AND (o.total_sold IS NULL OR o.total_sold < 10);
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT b.book_id, b.name
FROM Books b
LEFT JOIN (
  SELECT book_id, SUM(quantity) AS total_sold
  FROM Orders
  WHERE dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'
  GROUP BY book_id
) o ON b.book_id = o.book_id
WHERE b.available_from <= '2019-05-23'
  AND (o.total_sold IS NULL OR o.total_sold < 10);

Complexity

  • ⏰ Time complexity: O(n) — Each row is processed a constant number of times.
  • 🧺 Space complexity: O(n) — For intermediate groupings.