Back to questions
SQL GROUP BY Practice Exercise: Candidate Skills LinkedIn SQL Interview Question
SQL GROUP BY Practice Exercise: Candidate Skills
LinkedIn SQL Interview Question
This SQL GROUP BY exercise uses real data from a LinkedIn SQL Interview question which is a bit too hard to tackle right now, so we'll solve an easier variant of the interview question.
Suppose you are given a table of candidates and their skills. How many candidates possess each of the different skills? Sort your answers based on the count of candidates, from highest to lowest.
Assumption:
- There are no duplicates in the table.
Table:
| Column Name | Type |
|---|---|
| candidate_id | integer |
| skill | varchar |
Example Input:
| candidate_id | skill |
|---|---|
| 123 | Python |
| 123 | Tableau |
| 123 | PostgreSQL |
| 234 | R |
| 234 | PowerBI |
| 234 | SQL Server |
| 345 | Python |
| 345 | Tableau |
Example Output:
| skill | count |
|---|---|
| Python | 4 |
| Tableau | 4 |
| PostgreSQL | 2 |
| PowerBI | 1 |
| R | 1 |
| SQL Server | 1 |
| Java | 1 |
Solution
Here's the solution:
Since we have to count candidates, that's a hint to use the aggregate function!
We also want to report our count FOR EACH SKILL... which means in your head, know that we'll !
Finally, because we want to sort answers based on the count of candidates, from highest to lowest, that's a job for . We need that DESC keyword, because the default is to sort ASC (ascending), which for numerical data means lowest to highest (which is NOT what the problem wants!).