Sales Analysis – 3

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

 

---

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'
  )
;