w3resource

SQL Challenges-1: Highest Sale Amount

SQL Challenges-1: Exercise-9 with Solution

A salesperson is a person whose job is to sell products or services.
From the following tables, write a SQL query to find the top 10 salesperson that have made highest sale. Return their names and total sale amount.

Input:

Table: sales

Structure:

FieldTypeNullKeyDefaultExtra
TRANSACTION_IDint(5)NOPRI
SALESMAN_IDint(4)NO
SALE_AMOUNTdecimal(8,2)YES

Data:

TRANSACTION_IDSALESMAN_IDSALE_AMOUNT
501185200.00
502505566.00
503388400.00
504438400.00
505119000.00
506425900.00
507137000.00
508336000.00
509418200.00
510114500.00
5115110000.00
512299500.00
513596500.00
514387800.00
515589800.00
5166012000.00
5175813900.00
5182312200.00
519345480.00
520358129.00
521499323.00
522468200.00
523479990.00
5244214000.00
525447890.00
526475990.00
527217770.00
528576645.00
529565125.00
5302510990.00
5312112600.00
532415514.00
5331715600.00
5344415000.00
5351217550.00
5365513000.00
5375816800.00
5382519900.00
539579990.00
540288900.00
5414410200.00
5425718000.00
5433416200.00
5443619998.00
5453013500.00
5463715520.00
5473620000.00
5482019800.00
5492218530.00
5501912523.00
551469885.00
552227100.00
5535417500.00
5541919600.00
5552417500.00
556387926.00
557497548.00
558159778.00
5595619330.00
5602414400.00
5611816700.00
562546420.00
5633118720.00
5642117220.00
5654818880.00
566338882.00
5674419550.00
5682214440.00
5695319500.00
570305300.00
5713010823.00
5723513300.00
5733519100.00
5741817525.00
575608995.00
576539990.00
577217660.00
5782718990.00
5791118200.00
5803012338.00
5813711000.00
5822711980.00
5831812628.00
5845211265.00
5855319990.00
586278125.00
587257128.00
588576760.00
589195985.00
5905217641.00
5915311225.00
5922212200.00
5935916520.00
5943519990.00
5954219741.00
5961915000.00
5975719625.00
598539825.00
5992416745.00
6001214900.00

Table: salesman

structure:

FieldTypeNullKeyDefaultExtra
SALESMAN_IDint(4)NOPRI
SALESMAN_NAMEvarchar(30)YES

Data:

SALESMAN_IDSALESMAN_NAME
11Jonathan Goodwin
12Adam Hughes
13Mark Davenport
14Jamie Shelley
15Ethan Birkenhead
16Liam Alton
17Josh Day
18Sean Mann
19Evan Blake
20Rhys Emsworth
21Kian Wordsworth
22Frederick Kelsey
23Noah Turner
24Callum Bing
25Harri Wilberforce
26Gabriel Gibson
27Richard York
28Tobias Stratford
29Will Kirby
30Bradley Wright
31Eli Willoughby
32Patrick Riley
33Kieran Freeman
34Toby Scott
35Elliot Clapham
36Lewis Moss
37Joshua Atterton
38Jonathan Reynolds
39David Hill
40Aidan Yeardley
41Dan Astley
42Finlay Dalton
43Toby Rodney
44Ollie Wheatley
45Sean Spalding
46Jason Wilson
47Christopher Wentworth
48Cameron Ansley
49Henry Porter
50Ezra Winterbourne
51Rufus Fleming
52Wallace Dempsey
53Dan McKee
54Marion Caldwell
55Morris Phillips
56Chester Chandler
57Cleveland Klein
58Hubert Bean
59Cleveland Hart
60Marion Gregory

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
TRANSACTION_ID INTEGER(5) NOT NULL,
SALESMAN_ID   INTEGER(4) NOT NULL,
SALE_AMOUNT  decimal(8,2),
PRIMARY KEY (TRANSACTION_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO sales VALUES(501,18,5200.00),(502,50,5566.00),(503,38,8400.00),(504,43,8400.00),(505,11,9000.00);
INSERT INTO sales VALUES (506,42,5900.00),(507,13,7000.00),(508,33,6000.00),(509,41,8200.00),(510,11,4500.00);
INSERT INTO sales VALUES (511,51,10000.00),(512,29,9500.00),(513,59,6500.00),(514,38,7800.00),(515,58,9800.00);
INSERT INTO sales VALUES (516,60,12000.00),(517,58,13900.00),(518,23,12200.00),(519,34,5480.00),(520,35,8129.00);
INSERT INTO sales VALUES (521,49,9323.00),(522,46,8200.00),(523,47,9990.00),(524,42,14000.00),(525,44,7890.00);
INSERT INTO sales VALUES (526,47,5990.00),(527,21,7770.00),(528,57,6645.00),(529,56,5125.00),(530,25,10990.00);
INSERT INTO sales VALUES (531,21,12600.00),(532,41,5514.00),(533,17,15600.00),(534,44,15000.00),(535,12,17550.00);
INSERT INTO sales VALUES (536,55,13000.00),(537,58,16800.00),(538,25,19900.00),(539,57,9990.00),(540,28,8900.00);
INSERT INTO sales VALUES (541,44,10200.00),(542,57,18000.00),(543,34,16200.00),(544,36,19998.00),(545,30,13500.00);
INSERT INTO sales VALUES (546,37,15520.00),(547,36,20000.00),(548,20,19800.00),(549,22,18530.00),(550,19,12523.00);
INSERT INTO sales VALUES (551,46,9885.00),(552,22,7100.00),(553,54,17500.00),(554,19,19600.00),(555,24,17500.00);
INSERT INTO sales VALUES (556,38,7926.00),(557,49,7548.00),(558,15,9778.00),(559,56,19330.00),(560,24,14400.00);
INSERT INTO sales VALUES (561,18,16700.00),(562,54,6420.00),(563,31,18720.00),(564,21,17220.00),(565,48,18880.00); 
INSERT INTO sales VALUES (566,33,8882.00),(567,44,19550.00),(568,22,14440.00),(569,53,19500.00),(570,30,5300.00);
INSERT INTO sales VALUES (571,30,10823.00),(572,35,13300.00),(573,35,19100.00),(574,18,17525.00),(575,60,8995.00);
INSERT INTO sales VALUES (576,53,9990.00),(577,21,7660.00),(578,27,18990.00),(579,11,18200.00),(580,30,12338.00);
INSERT INTO sales VALUES (581,37,11000.00),(582,27,11980.00),(583,18,12628.00),(584,52,11265.00),(585,53,19990.00);
INSERT INTO sales VALUES (586,27,8125.00),(587,25,7128.00),(588,57,6760.00),(589,19,5985.00),(590,52,17641.00);
INSERT INTO sales VALUES (591,53,11225.00),(592,22,12200.00),(593,59,16520.00),(594,35,19990.00),(595,42,19741.00);
INSERT INTO sales VALUES (596,19,15000.00),(597,57,19625.00),(598,53,9825.00),(599,24,16745.00),(600,12,14900.00);
DROP TABLE IF EXISTS salesman;
CREATE TABLE salesman (
SALESMAN_ID 	            INTEGER(4) NOT NULL,
SALESMAN_NAME               varchar(30),
PRIMARY KEY (SALESMAN_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO salesman VALUES(11	,'Jonathan Goodwin     ');
INSERT INTO salesman VALUES(12	,'Adam Hughes          ');
INSERT INTO salesman VALUES(13	,'Mark Davenport       ');
INSERT INTO salesman VALUES(14	,'Jamie Shelley        ');
INSERT INTO salesman VALUES(15	,'Ethan Birkenhead     ');
INSERT INTO salesman VALUES(16	,'Liam Alton           ');
INSERT INTO salesman VALUES(17	,'Josh Day             ');
INSERT INTO salesman VALUES(18	,'Sean Mann            ');
INSERT INTO salesman VALUES(19	,'Evan Blake           ');
INSERT INTO salesman VALUES(20	,'Rhys Emsworth        ');
INSERT INTO salesman VALUES(21	,'Kian Wordsworth      ');
INSERT INTO salesman VALUES(22	,'Frederick Kelsey     ');
INSERT INTO salesman VALUES(23	,'Noah Turner          ');
INSERT INTO salesman VALUES(24	,'Callum Bing          ');
INSERT INTO salesman VALUES(25	,'Harri Wilberforce    ');
INSERT INTO salesman VALUES(26	,'Gabriel Gibson       ');
INSERT INTO salesman VALUES(27	,'Richard York         ');
INSERT INTO salesman VALUES(28	,'Tobias Stratford     ');
INSERT INTO salesman VALUES(29	,'Will Kirby           ');
INSERT INTO salesman VALUES(30	,'Bradley Wright       ');
INSERT INTO salesman VALUES(31	,'Eli Willoughby       ');
INSERT INTO salesman VALUES(32	,'Patrick Riley        ');
INSERT INTO salesman VALUES(33	,'Kieran Freeman       ');
INSERT INTO salesman VALUES(34	,'Toby Scott           ');
INSERT INTO salesman VALUES(35	,'Elliot Clapham       ');
INSERT INTO salesman VALUES(36	,'Lewis Moss           ');
INSERT INTO salesman VALUES(37	,'Joshua Atterton      ');
INSERT INTO salesman VALUES(38	,'Jonathan Reynolds    ');
INSERT INTO salesman VALUES(39	,'David Hill           ');
INSERT INTO salesman VALUES(40	,'Aidan Yeardley       ');
INSERT INTO salesman VALUES(41	,'Dan Astley           ');
INSERT INTO salesman VALUES(42	,'Finlay Dalton        ');
INSERT INTO salesman VALUES(43	,'Toby Rodney          ');
INSERT INTO salesman VALUES(44	,'Ollie Wheatley       ');
INSERT INTO salesman VALUES(45	,'Sean Spalding        ');
INSERT INTO salesman VALUES(46	,'Jason Wilson         ');
INSERT INTO salesman VALUES(47	,'Christopher Wentworth');
INSERT INTO salesman VALUES(48	,'Cameron Ansley       ');
INSERT INTO salesman VALUES(49	,'Henry Porter         ');
INSERT INTO salesman VALUES(50	,'Ezra Winterbourne    ');
INSERT INTO salesman VALUES(51	,'Rufus Fleming        ');
INSERT INTO salesman VALUES(52	,'Wallace Dempsey      ');
INSERT INTO salesman VALUES(53	,'Dan McKee            ');
INSERT INTO salesman VALUES(54	,'Marion Caldwell      ');
INSERT INTO salesman VALUES(55	,'Morris Phillips      ');
INSERT INTO salesman VALUES(56	,'Chester Chandler     ');
INSERT INTO salesman VALUES(57	,'Cleveland Klein      ');
INSERT INTO salesman VALUES(58	,'Hubert Bean          ');
INSERT INTO salesman VALUES(59	,'Cleveland Hart       ');
INSERT INTO salesman VALUES(60	,'Marion Gregory       ');

SELECT salesman_name, SUM(sale_amount) as total_sale
FROM salesman  a JOIN sales b ON a.salesman_id = b.salesman_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Sample Output:

salesman_name        |total_sale|
---------------------|----------|
Dan McKee            |  70530.00|
Cleveland Klein      |  61020.00|
Elliot Clapham       |  60519.00|
Evan Blake           |  53108.00|
Ollie Wheatley       |  52640.00|
Frederick Kelsey     |  52270.00|
Sean Mann            |  52053.00|
Callum Bing          |  48645.00|
Kian Wordsworth      |  45250.00|
Bradley Wright       |  41961.00|

Solution-1:

SELECT salesman_name, SUM(sale_amount) as total_sale
FROM salesman a JOIN sales b ON a.salesman_id = b.salesman_id
GROUP BY salesman_name 
ORDER BY total_sale DESC
LIMIT 10;

Solution-2:

select name, total_sale
from
(select u.salesman_id as id, u.salesman_name as name, coalesce(sum(sale_amount),0) as total_sale,
rank() over(order by coalesce(sum(sale_amount),0) desc) as sale_rank
from salesman u
left join sales r
on u.salesman_id = r.salesman_id
group by u.salesman_id, u.salesman_name) x
where sale_rank <= 10;

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Rising Sulfur Dioxide.
Next: Find active customers.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8