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

 

---

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
;