How to extract top 10 billionaire individuals from every Country

Question

Suppose I have a table named Billionaires having attributes Country, Name, Net Worth, of billionaire individuals from every country.

I want the top 10 individuals from every country having the highest net worth with their net worth summed up.

The sample output is like

Country	TotalNetworth
US.	500000000000
India.	300000000000

And so on.

My code that I tried:

Select Country, Sum(NetWorth) over (partition by country) as TotalNetworth
From Billionaires

Answer 1

There are a few possible approaches. Here is an example.

SELECT Country, SUM(NetWorth) AS TotalNetWorth
FROM (
    SELECT Country, NetWorth, ROW_NUMBER() OVER(PARTITION BY Country ORDER BY NetWorth) AS NetWorthRank
    FROM Billionaires
    ) AS sub
WHERE NetWorthRank < 11
GROUP BY Country

Answer 2

DENSE_RANK() function is suitable here for retrieving country wise top 10 individual Billionaires. If two person have same net worth in a country then consider it 1 not 2. DENSE_RANK() provides the rank without no gaps.

-- SQL SERVER
SELECT t.Country
     , SUM(t.NetWorth) TotalNetworth
FROM (SELECT Country
           , NetWorth
           , DENSE_RANK() OVER (PARTITION BY Country ORDER BY NetWorth DESC) rank_num
      FROM Billionaires) t
WHERE t.rank_num <= 10
GROUP BY t.Country; 

But if need country wise top 10 Billionaires name who have highest worth then use below query where if two persons of a country have same networth then count is 2 not 1.

-- SQL SERVER
SELECT t.Country
     , SUM(t.NetWorth) TotalNetworth
FROM (SELECT Country
           , NetWorth
           , ROW_NUMBER() OVER (PARTITION BY Country ORDER BY NetWorth DESC) rank_num
      FROM Billionaires) t
WHERE t.rank_num <= 10
GROUP BY t.Country;