00:00:00

Description

Editorial

Quarter-Only Product Sales Report

EASY10 pts

Your analytics team stores catalog metadata in Product and transaction events in Sales.

Table: product

product_id: unique identifier for a catalog item
product_name: display name for the item
unit_price: list price in the catalog

Table: sales

seller_id: account that sold the item
product_id: sold product identifier
buyer_id: account that purchased the item
sale_date: transaction date
quantity: units in the row
price: total amount for the row

Task:

Find products that were sold only during the first quarter of 2019.
The valid date window is inclusive: 2019-01-01 through 2019-03-31.
A product qualifies only if it has at least one sales row.
If a product has even one sale outside this window, it must be excluded.

Output requirements:

Return exactly two columns: product_id, product_name
One row per qualifying product
Sort by product_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 1          | Atlas Phone  | 1000       |
| 2          | Boreal Pad   | 800        |
| 3          | Comet Watch  | 1400       |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 1         | 1          | 1        | 2019-01-21  | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17  | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02  | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13  | 2        | 2800  |

Output

[
  {"product_id":1,"product_name":"Atlas Phone"}
]

Explanation

Product 1 has sales only within Q1 2019. Product 2 has a Q2 sale, and product 3 has only post-Q1 sales.

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 10         | North Hub    | 350        |
| 11         | South Hub    | 450        |
| 12         | East Hub     | 500        |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 7         | 10         | 101      | 2019-01-01  | 1        | 350   |
| 8         | 10         | 102      | 2019-03-31  | 1        | 350   |
| 8         | 11         | 103      | 2019-03-31  | 1        | 450   |
| 9         | 11         | 104      | 2018-12-31  | 1        | 450   |
| 9         | 12         | 105      | 2019-04-01  | 1        | 500   |

Output

[
  {"product_id":10,"product_name":"North Hub"}
]

Explanation

Date boundaries are inclusive. Product 11 is disqualified by a pre-window sale, and product 12 is disqualified by an out-of-window April sale.

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 21         | Vertex Cam   | 600        |
| 22         | Pulse Dock   | 200        |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 5         | 21         | 9        | 2019-04-15  | 1        | 600   |
| 6         | 22         | 10       | 2018-11-02  | 2        | 400   |

Output

[]

Explanation

No product is sold exclusively within Q1 2019.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in product <= 1,000
0 <= number of rows in sales <= 10,000
1 <= product_id, seller_id, buyer_id <= 1,000,000,000
1 <= unit_price, price <= 1,000,000,000
1 <= quantity <= 100,000
product.product_id is unique
sales.product_id references product.product_id
sales may contain duplicate rows
sale_date uses ISO format YYYY-MM-DD
A qualifying product must have at least one sales row
Return one row per qualifying product
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_id, product_name

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

[{"seller_id":1,"product_id":1,"buyer_id":1,"sale_date":"2019-01-21","quantity":2,"price":2000},{"seller_id":1,"product_id":2,"buyer_id":2,"sale_date":"2019-02-17","quantity":1,"price":800},{"seller_id":2,"product_id":2,"buyer_id":3,"sale_date":"2019-06-02","quantity":1,"price":800},{"seller_id":3,"product_id":3,"buyer_id":4,"sale_date":"2019-05-13","quantity":2,"price":2800}]

product =

[{"product_id":1,"product_name":"Atlas Phone","unit_price":1000},{"product_id":2,"product_name":"Boreal Pad","unit_price":800},{"product_id":3,"product_name":"Comet Watch","unit_price":1400}]

Loading problem...

101

00:00:00

Description

Editorial

Quarter-Only Product Sales Report

EASY10 pts

Your analytics team stores catalog metadata in Product and transaction events in Sales.

Table: product

product_id: unique identifier for a catalog item
product_name: display name for the item
unit_price: list price in the catalog

Table: sales

seller_id: account that sold the item
product_id: sold product identifier
buyer_id: account that purchased the item
sale_date: transaction date
quantity: units in the row
price: total amount for the row

Task:

Find products that were sold only during the first quarter of 2019.
The valid date window is inclusive: 2019-01-01 through 2019-03-31.
A product qualifies only if it has at least one sales row.
If a product has even one sale outside this window, it must be excluded.

Output requirements:

Return exactly two columns: product_id, product_name
One row per qualifying product
Sort by product_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 1          | Atlas Phone  | 1000       |
| 2          | Boreal Pad   | 800        |
| 3          | Comet Watch  | 1400       |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 1         | 1          | 1        | 2019-01-21  | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17  | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02  | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13  | 2        | 2800  |

Output

[
  {"product_id":1,"product_name":"Atlas Phone"}
]

Explanation

Product 1 has sales only within Q1 2019. Product 2 has a Q2 sale, and product 3 has only post-Q1 sales.

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 10         | North Hub    | 350        |
| 11         | South Hub    | 450        |
| 12         | East Hub     | 500        |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 7         | 10         | 101      | 2019-01-01  | 1        | 350   |
| 8         | 10         | 102      | 2019-03-31  | 1        | 350   |
| 8         | 11         | 103      | 2019-03-31  | 1        | 450   |
| 9         | 11         | 104      | 2018-12-31  | 1        | 450   |
| 9         | 12         | 105      | 2019-04-01  | 1        | 500   |

Output

[
  {"product_id":10,"product_name":"North Hub"}
]

Explanation

Date boundaries are inclusive. Product 11 is disqualified by a pre-window sale, and product 12 is disqualified by an out-of-window April sale.

Example

Input

product:
| product_id | product_name | unit_price |
|------------|--------------|------------|
| 21         | Vertex Cam   | 600        |
| 22         | Pulse Dock   | 200        |

sales:
| seller_id | product_id | buyer_id | sale_date   | quantity | price |
|-----------|------------|----------|-------------|----------|-------|
| 5         | 21         | 9        | 2019-04-15  | 1        | 600   |
| 6         | 22         | 10       | 2018-11-02  | 2        | 400   |

Output

[]

Explanation

No product is sold exclusively within Q1 2019.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in product <= 1,000
0 <= number of rows in sales <= 10,000
1 <= product_id, seller_id, buyer_id <= 1,000,000,000
1 <= unit_price, price <= 1,000,000,000
1 <= quantity <= 100,000
product.product_id is unique
sales.product_id references product.product_id
sales may contain duplicate rows
sale_date uses ISO format YYYY-MM-DD
A qualifying product must have at least one sales row
Return one row per qualifying product
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_id, product_name

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

product =

[{"product_id":1,"product_name":"Atlas Phone","unit_price":1000},{"product_id":2,"product_name":"Boreal Pad","unit_price":800},{"product_id":3,"product_name":"Comet Watch","unit_price":1400}]

Quarter-Only Product Sales Report

Hints

Quarter-Only Product Sales Report

Hints