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 ;