Problem

Given a table with millions of rows, write a SQL query to find the top 10 most common values in a given column.
Example input:
| id | value |
|----|-------|
| 1  | 100   |
| 2  | 200   |
| 3  | 100   |
| 4  | 300   |
| 5  | 100   |
| 6  | 200   |
| 7  | 400   |
Example output:
| value | count |
|-------|-------|
| 100   | 3     |
| 200   | 2     |
| 300   | 1     |
| 400   | 1     |

Solution

SELECT value, COUNT(*) AS count
FROM table
GROUP BY value
ORDER BY count DESC
LIMIT 10;

/*

The solution above is optimal because it uses the GROUP BY and ORDER BY clauses to group the values and order them by count.
The LIMIT clause is used to limit the number of rows returned.

*/