Question

Consider the table containing the list of individuals for every country along with their net worth. The table structure is like: Country | Name | Net Worth India | Mukesh Ambani | 40000000000 US | Bill Gates | 90000000000 US | Jeff Bezos | 95000000000 ….. Question: Write a SQL query in order to find the cumulative net worth of top 10 individuals for every country. Expected output: Country | Total Net worth India | 100000000000 US | 200000000000

Answer 1

Answer:

with cte as(

SELECT

 Name,

 Country,

 Net_Worth,

 RANK() OVER(PARTITION BY Country ORDER BY Net_Worth DESC) as ranks

FROM richest_persons)

SELECT

Country,

SUM(Net_Worth)as Total_Net_Worth

FROM cte

WHERE ranks<=10

GROUP BY Country;

Explanation:

Answer 2

cumulative net worth of top 10 individuals for every country

Explanation:

Lets say we have info table like this:

select * from info;

+---------+---------------+-----------+

| country | name          | net_worth |

+---------+---------------+-----------+

| India   | Mukesh Ambani |   1000000 |

| India   | Priya         |   1000000 |

| India   | Jk            |     10000 |

| India   | Rk            |     50000 |

| US      | Bill Gates    |   9000000 |

| US      | Jeff Bezos    |   9500000 |

| US      | sk            |   9800000 |

+---------+---------------+-----------+

• To Write a SQL query in order to find the cumulative net worth of top 10 individuals for every country:

• we need to create rank column that contain rank of each individual for each group

• query 1 = SELECT name, Country, Net_Worth, RANK() OVER(PARTITION BY Country ORDER BY Net_Worth DESC) as rank from info;

+---------------+---------+-----------+------+

| name          | Country | Net_Worth | rank |

+---------------+---------+-----------+------+

| Mukesh Ambani | India   |   1000000 |    1 |

| Priya         | India   |   1000000 |    1 |

| Rk            | India   |     50000 |    3 |

| Jk            | India   |     10000 |    4 |

| sk            | US      |   9800000 |    1 |

| Jeff Bezos    | US      |   9500000 |    2 |

| Bill Gates    | US      |   9000000 |    3 |

+---------------+---------+-----------+------+

• Now to use the temporary table that is generated using query 1 is done by using WITH clause.

• The WITH clause in MySQL is used to specify a Common Table Expression, a with clause can have one or more comms-separated subclauses.

syntax:

WITH

 table_name AS (query 1)

 query 2

So, the solution of the question is:

WITH

data1 AS (

   SELECT Name, Country, Net_Worth,

      RANK() OVER(PARTITION BY Country ORDER BY Net_Worth DESC) as ranks

   FROM info)

 SELECT Country, SUM(Net_Worth) AS Total_Net_Worth

 FROM

   data1  

 WHERE

   ranks<=10  

 GROUP BY

   Country;

Output of above query will be:

+---------+-----------------+

| Country | Total_Net_Worth |

+---------+-----------------+

| India   |         2060000 |

| US      |        28300000 |

+---------+-----------------+

hence, this is  required SQL query in order to find the cumulative net worth of top 10 individuals for every country.