Tables – product and sales are provided.
Write SQL query to identify products which were sold only in first quarter of year 2019. If a product is sold in any other quarter then that is not supposed to be present in final output.
Output should be product_id and product_name.
Input Table: sales
seller_id | product_id | buyer_id | sale_date | quantity | price |
11 | 10 | 71 | 2019-02-22 | 3 | 6000 |
11 | 20 | 72 | 2019-03-27 | 1 | 600 |
22 | 20 | 73 | 2019-07-04 | 1 | 900 |
33 | 30 | 74 | 2019-06-21 | 2 | 6600 |
Input Table: product
product_id | product_name | unit_price |
10 | S10 | 2000 |
20 | 7Pro | 600 |
30 | iPhone | 3300 |
Output Table:
product_id | product_name |
10 | S10 |
Explanation:
- product_id = 10 is the only product which was sold in first quarter of 2019.
- product_id = 20 was sold in first quarter as well as in third quarter, thats why it was excluded from final result.
DDLs:
CREATE TABLE IF NOT EXISTS product ( product_id INT, product_name VARCHAR(10), unit_price INT ); INSERT INTO product VALUES (10, 'S10', 2000); INSERT INTO product VALUES (20, '7Pro', 600); INSERT INTO product VALUES (30, 'iPhone', 3300); CREATE TABLE IF NOT EXISTS sales ( seller_id INT, product_id INT, buyer_id INT, sale_date DATE, quantity INT, price INT ); INSERT INTO sales VALUES (11, 10, 71, '2019-02-22', 3, 6000); INSERT INTO sales VALUES (11, 20, 72, '2019-03-27', 1, 600); INSERT INTO sales VALUES (22, 20, 73, '2019-07-04', 1, 900); INSERT INTO sales VALUES (33, 30, 74, '2019-06-21', 2, 6600);
Solution:
SELECT C.product_id, C.product_name FROM product C WHERE product_id NOT IN ( SELECT B.product_id FROM product A JOIN sales B ON A.product_id = B.product_id WHERE B.sale_date < '2019-01-01' OR B.sale_date > '2019-03-31' ) ;