TSQL calculates different% based on different fields

Question

I work on a 2012 MS-SQL server and have a USER table with Age, Gender among other fields and a SALES table with sales records.

I am currently calculating a Sales Leaderboard showing a list of sellers ordered by their TOP Sales, so, to give an example, this list returns various sales details based on their Top Sales. Somewhere in the middle of the list, we have Mr. Thomas, who says that this is No. 4.

My current task is to show how Thomas compares with sales representatives who are the same age as him, as well as how he compares with a seller who has the same gender as him. The calculation will lead to a different result than the general list described above.

My ideal stored procedure will receive 1 parameter (UserId) and return the following single record of values: OverallPosition, TotalPositionTotalCount, AgePosition, AgeTotalCount, GenderPosition, GenderTotalCount

SAMPLE DATA:

CREATE TABLE dbo.User ( UserId int NOT NULL IDENTITY (1, 1), Name nvarchar(50) NOT NULL, Age int NULL, Gender nvarchar(10) NULL ) 1, James, 30, 'male' 2, Monica, 27, 'female' 3, Paul, 30, 'male' 4, Thomas, 30, 'male' 5, Mike, 22, 'male' 6, Sabrina, 30, 'female' CREATE TABLE dbo.Sales ( SalesId int NOT NULL IDENTITY (1, 1), UserId int NOT NULL, TotalSale int NOT NULL ) ON [PRIMARY] 1, 1, $900,000 2, 1, $1,000,000 3, 2, $900,000 4, 2, $400,000 5, 3, $750,000 6, 3, $300,000 7, 4, $875,000 8, 5, $700,000 9, 5, $1,200,000 10, 6, $850,000 

List of sales leaders

 SELECT u.UserId, u.Name, MAX(s.TotalSale) as TopSale, Count(*) OVER () AS TotalCount FROM User u INNER JOIN Sales s on s.UserId = u.UserId GROUP BY u.UserID, u.Name ORDER BY TopSale DESC OFFSET (@PageIndexSelected) * @PageCountSelected ROWS FETCH NEXT @PageCountSelected ROWS ONLY 

Ideal calculation results
Since Thomas (userId 4) is 30 years old and “male,” his statistics should look like this:

 OverallPosition = 4; OverallPositionTotalCount = 6 (ie 4 out of 6) $1,200,000 Mike $1,000,000 James $900,000 Monica $875,000 Thomas $850,000 Sabrina $750,000 Paul AgePosition = 2; AgeTotalCount = 4 (ie 2 out of 4) $1,000,000 James $875,000 Thomas $850,000 Sabrina $750,000 Paul GenderPosition = 3; GenderTotalCount = 4 (ie 3 out of 4) $1,200,000 Mike $1,000,000 James $875,000 Thomas $750,000 Paul 

Note
The expected result is ONLY the values ​​for TotalPosition, TotalPositionTotalCount, AgePosition, AgeTotalCount, GenderPosition, GenderTotalCount for one user (the stored procedure will receive UserId as a parameter), and NOT the actual list.

EXPECTED RETURN
General position = 4,
OverallPositionTotalCount = 6,
AgePosition = 2,
AgeTotalCount = 4,
GenderPosition = 3,
GenderTotalCount = 4

As I said in my comments, I really don’t know how to approach this problem. I hope someone wants to help!

Answer 1

The first CTE receives the maximum sales for each person. The second uses the window functions rank() and count() with the corresponding over() clause to calculate position and totals.

 with C1 as ( select U.UserId, U.Gender, U.Age, max(S.TotalSale) as TotalSale from dbo.[User] as U inner join dbo.Sales as S on U.UserId = S.UserId group by U.UserId, U.Gender, U.Age ), C2 as ( select C1.UserId, C1.TotalSale, rank() over(order by C1.TotalSale desc) as OverallPosition, rank() over(partition by C1.Age order by C1.TotalSale desc) as AgePosition, rank() over(partition by C1.Gender order by C1.TotalSale desc) as GenderPosition, count(*) over() as OverallPositionTotalCount, count(*) over(partition by C1.Age) as AgeTotalCount, count(*) over(partition by C1.Gender) as GenderTotalCount from C1 ) select C2.OverallPosition, C2.OverallPositionTotalCount, C2.AgePosition, C2.AgeTotalCount, C2.GenderPosition, C2.GenderTotalCount from C2 where C2.UserId = 4; 

SQL Fiddle

Alternative:

 select C.OverallPosition, C.OverallPositionTotalCount, C.AgePosition, C.AgeTotalCount, C.GenderPosition, C.GenderTotalCount from ( select U.UserId, S.TotalSale, rank() over(order by S.TotalSale desc) as OverallPosition, rank() over(partition by U.Age order by S.TotalSale desc) as AgePosition, rank() over(partition by U.Gender order by S.TotalSale desc) as GenderPosition, count(*) over() as OverallPositionTotalCount, count(*) over(partition by U.Age) as AgeTotalCount, count(*) over(partition by U.Gender) as GenderTotalCount from dbo.[User] as U cross apply ( select max(S.TotalSale) as TotalSale from dbo.Sales as S where U.UserId = S.UserId ) as S ) as C where C.UserId = 4; 

SQL Fiddle

Answer 2

jsFiddle - edit: this is sqlFiddle, not jsFiddle :)

 DECLARE @UserId INT = 4 ;with overall as ( SELECT u.Name, u.UserId, RANK() OVER (ORDER BY max(s.TotalSale) DESC) OverallRank FROM User u JOIN Sales s on u.UserId = s.UserId group by u.Name, u.UserId ), age as ( SELECT u.Name, u.UserId, RANK() OVER (ORDER BY max(s.TotalSale) DESC) AgeRank FROM User u JOIN Sales s on u.UserId = s.UserId where u.age = (select age from @User where UserId = @UserId) group by u.Name, u.UserId ), gender as ( SELECT u.Name, u.UserId, RANK() OVER (ORDER BY max(s.TotalSale) DESC) GenderRank FROM User u JOIN Sales s on u.UserId = s.UserId where u.Gender = (select gender from @User where UserId = @UserId) group by u.Name, u.UserId ) SELECT o.OverallRank as OverallPosition, (select count(*) from overall) as OverallTotalCount, a.AgeRank as AgePosition, (select count(*) from age) as AgeTotalCount, g.GenderRank GenderPosition, (select count(*) from gender) as GenderTotalCount FROM overall o JOIN age a on o.UserId = a.UserId JOIN gender g on o.UserId = g.UserId WHERE o.UserId = @UserId 

Answer 3

Here is the complete SQL Proc for this ... basically you have to do it manually. (NOTE: I changed the table names to TestUser and TestSales so as not to interfere with the built-in names.)

 CREATE PROCEDURE [dbo].[GetUserSales] @paramUserId int AS BEGIN DECLARE @OverallPosition int DECLARE @OverallCount int DECLARE @AgePosition int DECLARE @AgeTotalCount int DECLARE @GenderPosition int DECLARE @GenderTotalCount int ---------- -- OVERALL ---------- SELECT @OverallCount = COUNT(UserId) FROM dbo.TestUser -- Add an extra 1 here for the user himself. SELECT @OverallPosition = COUNT(us.UserId) + 1 FROM ( SELECT tu.UserId, MAX(ts.TotalSale) as TopSale FROM TestUser as tu JOIN TestSales as ts ON tu.UserId = ts.UserId GROUP BY (tu.UserId) ) as us WHERE us.TopSale > (SELECT MAX(TotalSale) FROM TestSales WHERE UserId = @paramUserId) ---------- -- AGE ---------- SELECT @AgeTotalCount = COUNT(UserId) FROM TestUser WHERE Age = (SELECT Age FROM TestUser WHERE UserId = @paramUserId) -- Add an extra 1 here for hte user himself. SELECT @AgePosition = COUNT(usa.UserId) + 1 FROM ( SELECT tu.UserId, MAX(ts.TotalSale) as TopSale FROM TestUser as tu JOIN TestSales as ts ON tu.UserId = ts.UserId WHERE tu.Age = (SELECT Age FROM TestUser WHERE UserId = @paramUserId) GROUP BY (tu.UserId) ) as usa WHERE usa.TopSale > (SELECT MAX(TotalSale) FROM TestSales WHERE UserId = @paramUserId) ---------- -- GENDER ---------- SELECT @GenderTotalCount = COUNT(UserId) FROM TestUser WHERE Gender = (SELECT Gender FROM TestUser WHERE UserId = @paramUserId) -- Add an extra 1 here for hte user himself. SELECT @GenderPosition = COUNT(usg.UserId) + 1 FROM ( SELECT tu.UserId, MAX(ts.TotalSale) as TopSale FROM TestUser as tu JOIN TestSales as ts ON tu.UserId = ts.UserId WHERE tu.Gender = (SELECT Gender FROM TestUser WHERE UserId = @paramUserId) GROUP BY (tu.UserId) ) as usg WHERE usg.TopSale > (SELECT MAX(TotalSale) FROM TestSales WHERE UserId = @paramUserId) ---------- -- RESULTSET ---------- SELECT tu.UserId, tu.Name, @OverallPosition as 'OverallPosition', @OverallCount as 'OverallCount', @AgePosition as 'AgePosition', @AgeTotalCount as 'AgeTotalCount', @GenderPosition as 'GenderPosition', @GenderTotalCount as 'GenderTotalCount' FROM TestUser as tu WHERE tu.UserId = @paramUserId END