Low-Demand Catalog Titles

Medium

A publishing analytics team monitors inventory velocity and wants to identify catalog titles that are currently underperforming. Table: books book_id: unique title identifier name: title name available_from: first date this title became available for sale Table: orders order_id: unique order-line identifier book_id: ordered title identifier quantity: shipped units in that order line dispatch_date: shipment dispatch date Reference date assumptions: today = 2019-06-23 maturity cutoff = 2019-05-23 lookback window = [2018-06-23, 2019-06-23] (inclusive) A title is considered low-demand if: It is mature: available_from <= 2019-05-23 Total shipped units in the lookback window is strictly less than 10 Important behavior: Mature titles with zero shipments in the lookback window must still be returned. Task: Return all qualifying titles as (book_id, name) Return one row per book_id Sort by book_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

books:
| book_id | name                 | available_from |
|---------|----------------------|----------------|
| 1       | Atlas of Rivers      | 2010-01-01     |
| 2       | Distributed Futures  | 2012-05-12     |
| 3       | Fast Graph Primer    | 2019-06-10     |
| 4       | Queueing Stories     | 2019-06-01     |
| 5       | Cloud Systems Digest | 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":1,"name":"Atlas of Rivers"},
  {"book_id":2,"name":"Distributed Futures"},
  {"book_id":5,"name":"Cloud Systems Digest"}
]

Explanation

Books 1, 2, and 5 are mature and each has fewer than 10 shipped units in the lookback window. Books 3 and 4 are too recent and therefore excluded.

Example 2

Input

books:
| book_id | name                  | available_from |
|---------|-----------------------|----------------|
| 10      | Legacy SQL Patterns   | 2018-05-10     |
| 11      | Batch Window Playbook | 2018-05-10     |
| 12      | New Launch Almanac    | 2019-05-24     |

orders:
| order_id | book_id | quantity | dispatch_date |
|----------|---------|----------|---------------|
| 10       | 10      | 9        | 2018-06-23    |
| 11       | 10      | 1        | 2019-06-23    |
| 12       | 11      | 4        | 2017-03-01    |
| 13       | 12      | 1        | 2019-06-01    |

Output

[
  {"book_id":11,"name":"Batch Window Playbook"}
]

Explanation

Book 10 ships exactly 10 units in-window and fails the strict < 10 rule. Book 11 has only out-of-window shipments, so in-window total is 0. Book 12 is not mature.

Example 3

Input

books:
| book_id | name               | available_from |
|---------|--------------------|----------------|
| 100     | Event Mesh Intro   | 2017-01-01     |
| 101     | Serverless Atlas   | 2017-01-01     |

orders:
| order_id | book_id | quantity | dispatch_date |
|----------|---------|----------|---------------|
| 1001     | 100     | 3        | 2019-01-05    |
| 1002     | 100     | 2        | 2019-02-05    |
| 1003     | 101     | 6        | 2019-05-01    |
| 1004     | 101     | 4        | 2019-06-02    |

Output

[
  {"book_id":100,"name":"Event Mesh Intro"}
]

Explanation

Book 100 totals 5 units and qualifies. Book 101 totals 10 units and does not qualify.

Constraints

1 <= number of rows in books <= 1,000
0 <= number of rows in orders <= 10,000
1 <= book_id <= 1,000,000,000
1 <= order_id <= 1,000,000,000
1 <= quantity <= 100,000
books.book_id is unique
orders.order_id is unique
orders.book_id references books.book_id
Dates use ISO format YYYY-MM-DD
Use fixed reference date 2019-06-23
Maturity condition: available_from <= 2019-05-23
Window condition: dispatch_date between 2018-06-23 and 2019-06-23 inclusive
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [book_id, name]

books: | book_id | name | available_from | |---------|----------------------|----------------| | 1 | Atlas of Rivers | 2010-01-01 | | 2 | Distributed Futures | 2012-05-12 | | 3 | Fast Graph Primer | 2019-06-10 | | 4 | Queueing Stories | 2019-06-01 | | 5 | Cloud Systems Digest | 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 |

books: | book_id | name | available_from | |---------|-----------------------|----------------| | 10 | Legacy SQL Patterns | 2018-05-10 | | 11 | Batch Window Playbook | 2018-05-10 | | 12 | New Launch Almanac | 2019-05-24 | orders: | order_id | book_id | quantity | dispatch_date | |----------|---------|----------|---------------| | 10 | 10 | 9 | 2018-06-23 | | 11 | 10 | 1 | 2019-06-23 | | 12 | 11 | 4 | 2017-03-01 | | 13 | 12 | 1 | 2019-06-01 |

books: | book_id | name | available_from | |---------|--------------------|----------------| | 100 | Event Mesh Intro | 2017-01-01 | | 101 | Serverless Atlas | 2017-01-01 | orders: | order_id | book_id | quantity | dispatch_date | |----------|---------|----------|---------------| | 1001 | 100 | 3 | 2019-01-05 | | 1002 | 100 | 2 | 2019-02-05 | | 1003 | 101 | 6 | 2019-05-01 | | 1004 | 101 | 4 | 2019-06-02 |