1084. Sales Analysis III
Problem Statement
Report the products that were only sold in the spring of 2019 (2019-01-01 to 2019-03-31 inclusive, and no other dates). Return the result table in any order.
Table Schema
Table: Product
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id is the primary key.
Table: Sales
+-------------+------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+-------------+------+
No primary key; may have duplicate rows.
Examples
Example 1
Input Table:
Sales table:
+-----------+------------+----------+------------+----------+-------+
| 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 |
+-----------+------------+----------+------------+----------+-------+
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
+------------+--------------+------------+
Expected Output:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1 | S8 |
+------------+--------------+
Explanation: Product 2 was also sold on 2019-06-02 so it's excluded.
SQL Solution
SELECT p.product_id, p.product_name
FROM Product p
JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name
HAVING MIN(s.sale_date) >= '2019-01-01' AND MAX(s.sale_date) <= '2019-03-31';
Problem Info
DifficultyEASY
Topics
group-byhavingjoin
Reference Links