SQL Challenges-1: Sales Analysis

Last update on December 20 2024 11:58:52 (UTC/GMT +8 hours)

From the following tables write an SQL query to find the best seller by total sales price. Return distributor ID , If there is a tie, report them all.

Input:

Table: item

Structure:

Field	Type	Null	Key
item_code	int(11)	NO	PRI
item_desc	varchar(255)	YES
cost	int(11)	YES

Data:

item_code	item_desc	cost
101	mother board	2700
102	RAM	800
103	key board	300
104	mouse	300

Table: sales_info

Structure:

Field	Type	Null
distributor_id	int(11)	YES
item_code	int(11)	YES
retailer_id	int(11)	YES
date_of_sell	date	YES
quantity	int(11)	YES
total_cost	int(11)	YES

Data:

distributor_id	item_code	retailer_id	date_of_sell	quantity	total_cost
5001	101	1001	2020-02-12	3	8100
5001	103	1002	2020-03-15	15	4500
5002	101	1001	2019-06-24	2	5400
5001	104	1003	2019-09-11	8	2400
5003	101	1003	2020-10-21	5	13500
5003	104	1002	2020-12-27	10	3000
5002	102	1001	2019-05-18	12	9600
5002	103	1004	2020-06-17	8	2400
5003	103	1001	2020-04-12	3	900

Sample Solution-1:

SQL Code(MySQL):

CREATE TABLE item (item_code int not null unique, item_desc varchar(255), cost int);
INSERT INTO item VALUES(101,'mother board',	2700);
INSERT INTO item VALUES(102,'RAM',	800);
INSERT INTO item VALUES(103,'key board',300);
INSERT INTO item VALUES(104,'mouse',300);


CREATE TABLE sales_info (distributor_id int, item_code int, retailer_id int, date_of_sell date, quantity int, total_cost int);
INSERT INTO sales_info VALUES(5001,101,1001,'2020-02-12',3,8100);
INSERT INTO sales_info VALUES(5001,103,1002,'2020-03-15',15,4500);
INSERT INTO sales_info VALUES(5002,101,1001,'2019-06-24',2,5400);
INSERT INTO sales_info VALUES(5001,104,1003,'2019-09-11',8,2400);
INSERT INTO sales_info VALUES(5003,101,1003,'2020-10-21',5,13500);
INSERT INTO sales_info VALUES(5003,104,1002,'2020-12-27',10,3000);
INSERT INTO sales_info VALUES(5002,102,1001,'2019-05-18',12,9600);
INSERT INTO sales_info VALUES(5002,103,1004,'2020-06-17',8,2400);
INSERT INTO sales_info VALUES(5003,103,1001,'2020-04-12',3,900);

SELECT DISTINCT distributor_id
FROM sales_info
GROUP BY distributor_id
HAVING SUM(total_cost) =
(
SELECT SUM(total_cost) AS total_price
FROM sales_info
GROUP BY distributor_id
ORDER BY total_price DESC
LIMIT 1
);

Sample Output:

distributor_id|
--------------|
          5002|
          5003|

Sample Solution-2:

SQL Code(MySQL):

CREATE TABLE item (item_code int not null unique, item_desc varchar(255), cost int);
INSERT INTO item VALUES(101,'mother board',	2700);
INSERT INTO item VALUES(102,'RAM',	800);
INSERT INTO item VALUES(103,'key board',300);
INSERT INTO item VALUES(104,'mouse',300);


CREATE TABLE sales_info (distributor_id int, item_code int, retailer_id int, date_of_sell date, quantity int, total_cost int);
INSERT INTO sales_info VALUES(5001,101,1001,'2020-02-12',3,8100);
INSERT INTO sales_info VALUES(5001,103,1002,'2020-03-15',15,4500);
INSERT INTO sales_info VALUES(5002,101,1001,'2019-06-24',2,5400);
INSERT INTO sales_info VALUES(5001,104,1003,'2019-09-11',8,2400);
INSERT INTO sales_info VALUES(5003,101,1003,'2020-10-21',5,13500);
INSERT INTO sales_info VALUES(5003,104,1002,'2020-12-27',10,3000);
INSERT INTO sales_info VALUES(5002,102,1001,'2019-05-18',12,9600);
INSERT INTO sales_info VALUES(5002,103,1004,'2020-06-17',8,2400);
INSERT INTO sales_info VALUES(5003,103,1001,'2020-04-12',3,900);

SELECT distributor_id
FROM sales_info
GROUP BY distributor_id
HAVING SUM(total_cost) >= 
ALL(SELECT SUM(total_cost) FROM sales_info GROUP BY distributor_id);

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Most experienced manager to execute the schemes.
Next: Retailers buying history.