Finding average highest salary

Write a query to find the highest average sales among all the salespersons using the given table.

Table: Sales

Field       Type
InvoiceNo   Integer
SalesPerson Text
TotalSale   Integer

Sample

InvoiceNo   SalesPerson  TotalSale
1           Acheson      50
2           Bryant       25
3           Bennett      250
4           Acheson      50
5           Bryant       100
6           Bennett      250

Sample output

max(totalsale)
250.0000

I tried using: SELECT MAX(TotalSale) FROM Sales. My output for sample test case was 250 but it was given wrong answer verdict in an online judge. Also, when I changed the query to : SELECT MAX(TotalSale*1.0000) FROM Sales,I got correct answer for sample test case but still wrong answer for other test cases.

What is meant by average here? What should be the query?

Also, this isn’t from any on-going competition. It is from a practice test which you can attempt here: https://www.hackerearth.com/challenge/test/kredx-data-analyst-test/

Answer

1.First you have to calculate the average TotalSale for each SalesPerson using the AVG function.

SELECT SalesPerson, AVG(TotalSale) AS 'TotalSale' 
FROM Sales 
GROUP BY SalesPerson

2.Find out the max value from the table generated by the above query.

SELECT MAX(avgSales.TotalSale)
FROM (SELECT AVG(TotalSale) AS 'TotalSale' FROM Sales GROUP BY SalesPerson) AS avgSales

Advertisement

Answer