1098 Unpopular Books

Description

Table: Books

• book_id is the primary key (column with unique values) of this table.

Table: Orders

• 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.

Problem Statement

Write a solution to report the 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 table in any order.

The result format is in the following example.

Example 1:

Input:

• Books table:

• Orders table:

Output:

Solution

First thing, you need to retrieve results which are not available from just last one month from 2019-06-23. This means, you need books which are available from earlier than last one month. This you can get using .available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH).

1WITH old_books AS (
2    SELECT b.book_id, b.name, b.available_from, o.dispatch_date, o.quantity
3        FROM Books b
4        LEFT JOIN Orders o
5        ON b.book_id = o.book_id
6        WHERE b.available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH)
7)

Next, you need only those books which have sold less than 10 copies in the last year. For this, you can use SUM(quantity) to calculate copies sold, but you need to consider only those that have dispatch_date in the last one year. You can get that using CASE WHEN statement. You take quantity if the order was in the last one year else 0 as quantity.

1CASE WHEN dispatch_date >= DATE_SUB('2019-06-23', INTERVAL 1 YEAR) THEN quantity ELSE 0 END

The SQL would look like this.

1SELECT book_id, name
2    FROM old_books
3    GROUP BY book_id, name
4    HAVING SUM(CASE WHEN dispatch_date >= DATE_SUB('2019-06-23', INTERVAL 1 YEAR) THEN quantity ELSE 0 END) < 10;

This will output book_id and name of the book as asked in the question. The complete solution is as follows.

 1WITH old_books AS (
 2    SELECT b.book_id, b.name, b.available_from, o.dispatch_date, o.quantity
 3        FROM Books b
 4        LEFT JOIN Orders o
 5        ON b.book_id = o.book_id
 6        WHERE b.available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH)
 7) SELECT book_id, name
 8    FROM old_books
 9    GROUP BY book_id, name
10    HAVING SUM(CASE WHEN dispatch_date >= DATE_SUB('2019-06-23', INTERVAL 1 YEAR) THEN quantity ELSE 0 END) < 10;

Notice that in the second view, you’re not doing anything extra. So, you could include all these logic in a single query.

1SELECT DISTINCT b.book_id, b.name FROM Books b
2    LEFT JOIN Orders o ON b.book_id = o.book_id
3    WHERE b.available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH)
4    GROUP BY b.book_id
5    HAVING SUM(CASE WHEN dispatch_date BETWEEN DATE_SUB('2019-06-23', INTERVAL 1 YEAR) AND '2019-06-23' THEN quantity 
6    ELSE 0 END) < 10;