CODEWITHSUNDEEP
sqlsqlite
Carlos Mendez
Carlos Mendez

Reputation: 1

SQL- How to find best performing employee, from certain year across three different tables

I'm having trouble solving this problem. I'm fairly new to SQL and this is for an assignment. I'm allowed to create a new table or do anything to get the answer to the question below.

"Who was the best performing employee in 2012? Provide the name of the employee associated with most sales and her (or his) sales amount in 2012."

Three Tables I have to use

In the Employee Table, we're supposed to use the EmployeeID as a reference to SupportRepID in the Customer Table, Which is supposed to link with the CustomerID so we can pull the totals in the Invoice Table.

I know very little, but what I tried and failed to do was create a table where EmployeeID would equal SupportRepID to identify the Employee. SupportRepID would match with Customer ID so in the Invoice Table as we're pulling the totals for each CustomerID, it would, in the table give each amount to the employee that helped that customer in order to solve this problem. However, I'm very stuck as to how to execute this so I would really appreciate if anyone could help me! Also, It's only for 2012, which I do not know how to specific that either.

Upvotes: 0

Views: 230

Answers (1)

Graeme LM
Graeme LM

Reputation: 143

This query uses a CTE to:

  1. Sum the 2012 Sales per customer and tag the relevant SalesRepId.
  2. Sum the customer sales totals, per SalesRep Employee.
  3. Present the results with the Employee Name, ordered from highest sales to least.
WITH CustomerSales AS (
SELECT       I.CustomerId
            ,C.SalesRepID
            ,SUM(I.Total) AS CustomerSales
FROM         Invoice AS I
INNER JOIN   Customer AS C ON C.CustomerId=I.CustomerId
WHERE        YEAR(I.InvoiceDate)='2012'
GROUP BY     I.CustomerId
            ,C.SalesRepID
)

SELECT       E.EmployeeId
            ,E.FirstName
            ,E.LastName
            ,SUM(CS.CustomerSales) AS EmployeeSales
FROM         Employee AS E
INNER JOIN   CustomerSales AS CS ON CS.SalesRepID=E.EmployeeId
GROUP BY     E.EmployeeId
            ,E.FirstName
            ,E.LastName
ORDER BY     SUM(CS.CustomerSales) DESC

Upvotes: 0

Related Questions