Students Report By Geography - Leetcode Solution

Problem Statement:

This problem is taken from Leetcode. We are given a table called "student" with columns id and name and continent.

We need to write a SQL query to report the number of students in each continent, along with their country names sorted by the number of students.

Solution:

To solve this problem, we need to group the records by the continent and country names. We can use the GROUP BY clause of the SQL query for this purpose. We also need to count the number of students in each group, which can be done using the COUNT function.

The SQL query to solve this problem is as follows:

SELECT continent, name, COUNT(*) AS total_students
FROM student
GROUP BY continent, name
ORDER BY total_students DESC

Explanation:

• The SELECT statement selects the columns continent, name and the count of the number of students in each group, aliased as total_students.
• The FROM clause specifies the student table.
• The GROUP BY clause groups the records by continent and name.
• The ORDER BY clause sorts the results by the count of the number of students in each group in descending order.

Output:

The above query will return the number of students in each continent, along with their country names sorted by the number of students.

For example, if the student table looks like this:

+----+-------+-----------+
| id | name  | continent |
+----+-------+-----------+
| 1  | John  | Europe    |
| 2  | David | Asia      |
| 3  | Mary  | Asia      |
| 4  | Lucy  | Europe    |
| 5  | Anna  | Africa    |
| 6  | Jack  | Asia      |
| 7  | Tom   | Africa    |
+----+-------+-----------+

The above query will return the following output:

+-----------+-------+----------------+
| continent | name  | total_students |
+-----------+-------+----------------+
| Asia      | David | 1              |
| Asia      | Jack  | 1              |
| Asia      | Mary  | 1              |
| Africa    | Anna  | 1              |
| Africa    | Tom   | 1              |
| Europe    | John  | 1              |
| Europe    | Lucy  | 1              |
+-----------+-------+----------------+

The output shows that there is 1 student from each country in the above table.

1