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]

Loading problem...

Preparing your editor. You can read the problem while it loads.

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]