SQL Schema

Table: Books

+----------------+---------+

| Column Name    | Type    |

+----------------+---------+

| book_id        | int     |

| name           | varchar |

| available_from | date    |

+----------------+---------+

book_id is the primary key 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 of this table.

book_id is a foreign key to the Books table.

 

Write an SQL query that reports 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 query result format is in the following example.

 

Example 1:

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

+-----------+--------------------+

 

 

SELECT DISTINCT b.book_id, b.name

FROM books b

LEFT JOIN Orders o on b.book_id = o.book_id

GROUP BY b.book_id, b.name,

DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date),  

DATEDIFF(day,  b.available_from, DATEADD(month, -1, '2019-06-23'))

HAVING SUM(o.quantity) IS NULL OR

DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date) < 0 OR

(DATEDIFF(day, DATEADD(year, -1, '2019-06-23'), o.dispatch_date) > 0 AND DATEDIFF(day,  b.available_from, DATEADD(month, -1, '2019-06-23')) > 0 AND SUM(o.quantity) < 10);

 

Case 1

Input

Books =

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

| 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 |
| ------- | ---------------- |
| 2 | 28 Letters |
| 1 | Kalila And Demna |
| 5 | The Hunger Games |

Expected

| book_id | name |
| ------- | ---------------- |
| 1 | Kalila And Demna |
| 2 | 28 Letters |
| 5 | The Hunger Games |