Quarter-Only Product Sales Report

Easy

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 1

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 2

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 3

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.

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

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 |

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 |

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 |