00:00:00

Description

Editorial

Low-Demand Catalog Titles

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

books =

[{"book_id":1,"name":"Atlas of Rivers","available_from":"2010-01-01"},{"book_id":2,"name":"Distributed Futures","available_from":"2012-05-12"},{"book_id":3,"name":"Fast Graph Primer","available_from":"2019-06-10"},{"book_id":4,"name":"Queueing Stories","available_from":"2019-06-01"},{"book_id":5,"name":"Cloud Systems Digest","available_from":"2008-09-21"}]

orders =

[{"order_id":1,"book_id":1,"quantity":2,"dispatch_date":"2018-07-26"},{"order_id":2,"book_id":1,"quantity":1,"dispatch_date":"2018-11-05"},{"order_id":3,"book_id":3,"quantity":8,"dispatch_date":"2019-06-11"},{"order_id":4,"book_id":4,"quantity":6,"dispatch_date":"2019-06-05"},{"order_id":5,"book_id":4,"quantity":5,"dispatch_date":"2019-06-20"},{"order_id":6,"book_id":5,"quantity":9,"dispatch_date":"2009-02-02"},{"order_id":7,"book_id":5,"quantity":8,"dispatch_date":"2010-04-13"}]

Loading problem...

101

00:00:00

Description

Editorial

Low-Demand Catalog Titles

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

books =

orders =

Low-Demand Catalog Titles

Hints

Low-Demand Catalog Titles

Hints