Sales Analysis – 1

Tables – product and sales are provided. Being area manager, you are looking for best seller by total sales price to reward and motivate them for best results. If there is a tie, all sellers would be qualified for same reward. Write SQL query to fetch all seller_id by total sale price.


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:

seller_id
11
33

 


Explanation:

For seller_id 11 and 33, total amount of sales achieved was 6600. So, both are selected and eligible for reward.


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
B.seller_id
FROM
(
 SELECT
 A.seller_id,
 RANK() OVER(ORDER BY A.sum_price DESC) AS rn
 FROM
 (
  SELECT
  seller_id,
  SUM(price) AS sum_price
  FROM
  sales
  GROUP BY
  seller_id
  ) A
)B
WHERE B.rn=1
;

 

Leave a Comment