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

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

Leave a Comment