TSQL calculating various % based on different fields

Question

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

I'm currently calculating the Sales Leaderboard showing an list of Sales People ordered by their TOP Sales so to give an example this list returns various sales rep based on their Top Sales. Somewhere in the middle of the list we have Mr. Thomas which let's say is #4th.

My current task is to display how Thomas compares to sales reps that have the same Age as him and also how he compares with sales rep that have the same gender as him. The calculation will return a different result than the overall list described above.

My ideal stored procedure would receive 1 param (UserId) and return the following single record values: OverallPosition, OverallPositionTotalCount, AgePosition, AgeTotalCount, GenderPosition, GenderTotalCount

DATA SAMPLE:

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

Sales Leaderboard list

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 of Age and 'male', his Stats should look like this

OverallPosition = 4; OverallPositionTotalCount = 6    (i.e 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   (i.e. 2 out of 4)  
$1,000,000   James
$875,000     Thomas
$850,000     Sabrina
$750,000     Paul

GenderPosition = 3; GenderTotalCount = 4 (i.e 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 OverallPosition, OverallPositionTotalCount, AgePosition, AgeTotalCount, GenderPosition, GenderTotalCount for a single user (the stored procedure will receive the UserId as param) and NOT the actual list.

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

As I stated on my comments, I really don't know how to approach this problem. I hope that somebody will be willing to help !!

I don't have any code, cause I don't know how to approach this. That's why I've posted the question. BTW are you the person that clicked on the -1? — SF Developer, Feb 17 '14 at 18:22
Yes. Once you've updated your question with an attempt or research effort I'll remove it. — Kermit, Feb 17 '14 at 18:22
Please take a moment to read [on-topic questions](http://stackoverflow.com/help/on-topic); particularly #3. — Kermit, Feb 17 '14 at 18:28
My question is not that far from this one http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement ...and somehow I don't see -1 !!! — SF Developer, Feb 17 '14 at 18:33
The standards were not yet established. Are you going to continue arguing over whether you should show the work you've done so far? — Kermit, Feb 17 '14 at 18:36
@FreshPrinceOfSO I tend to agree with Eager that people tend to click on -1 too quickly instead of maybe reading the actual question. Anyhow, I would say to run separate queries but I'm not an SQL expert. — Johnny, Feb 17 '14 at 18:38
Please feel free to ring me once you've improved your question so I can remove my downvote. — Kermit, Feb 17 '14 at 18:39
Could you, at least, add a complete example of what data you have and what do you expect to get? — thepirat000, Feb 17 '14 at 18:55
Yes, it will take some time as this question is an over-simplification of the real database. Please give me 10 min... — SF Developer, Feb 17 '14 at 18:57
@thepirat000 ...post updated. Let me know if is not too clear. Thanks in advanced. — SF Developer, Feb 17 '14 at 19:35
I would look at creating axillary tables to provide those ranges values, map your user's metrics into those aux tables and then deliver it to your users as a pivot table. Let them figure out all the slicing and dicing — billinkc, Feb 17 '14 at 19:49
@billinkc Unfortunately, the stored procedure needs to return those 5 values, I cannot change that to a pivotal table.... — SF Developer, Feb 17 '14 at 19:51

Mikael Eriksson · Accepted Answer · 2014-02-17T20:41:12.423

The first CTE gets the max sales for each person. The second uses the windowing functions rank() and count() with an appropriate over() clause to calculate the 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

Which one do you think is better performing ? – SF Developer Feb 17 '14 at 21:09 — SF Developer, Feb 17 '14 at 21:09
My guess would be the second query. @Eager. – Mikael Eriksson Feb 17 '14 at 21:16 — Mikael Eriksson, Feb 17 '14 at 21:16

Tom · Answer 2 · 2014-02-17T21:05:51.197

jsFiddle -- edit: it's a 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

Thanks Tom, even if I ended up using the shorter version, I appreciate your reply. — SF Developer, Feb 18 '14 at 00:13

Joe · Answer 3 · 2014-02-19T16:10:44.260

Here is the full SQL Proc to do it... basically you have to manually do it. (NOTE: I changed the table names to TestUser and TestSales to not collide with 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

Joe, I ended up using the shorter version but still appreciate your reply. Thanks again. — SF Developer, Feb 18 '14 at 00:12
Thanks for the upvote. The other answer is better performance too. I learned something here as well -- didn't know about the rank() order(...) abilities with 'cross apply'. Good question +1. — Joe, Feb 19 '14 at 16:15

TSQL calculating various % based on different fields

3 Answers3