Coding questions · Reevo
Count sports by calendar quarter in SQL
This question evaluates a candidate's ability to perform date-based grouping and aggregation in SQL, including extracting year and calendar quarter and counting rows per sport.
You are given a table games(date DATE, team_name TEXT, sport_name TEXT). Write a SQL query that returns, for each year and calendar quarter (Q1–Q 4), each sport_name and the count of rows in that quarter. Output columns: year, quarter, sport_name, cnt. Order results by year, quarter, cnt DESC, sport_name ASC.
Count sports by calendar quarter
Table games
| column | type | constraints |
|---|---|---|
| game_date | DATE | NOT NULL |
| team_name | VARCHAR(50) | NOT NULL |
| sport_name | VARCHAR(50) | NOT NULL |
Sample data:
| game_date | sport_name | team_name |
|---|---|---|
| 2023-01-15 | Basketball | Lions |
| 2023-02-10 | Basketball | Tigers |
| 2023-03-20 | Soccer | Bears |
| 2023-04-05 | Basketball | Lions |
| 2023-04-18 | Soccer | Tigers |
| 2023-06-30 | Soccer | Bears |
| 2023-11-02 | Basketball | Lions |
| 2024-01-09 | Soccer | Tigers |
| 2024-03-11 | Soccer | Bears |
| 2024-05-27 | Basketball | Lions |
| 2024-07-04 | Basketball | Tigers |
| 2024-10-31 | Soccer | Bears |
Expected output
One row per (year, calendar quarter, sport_name) with the count of games in that year-quarter for that sport. Only quarters that have at least one game appear in the output.
| cnt | quarter | sport_name | year |
|---|---|---|---|
| 2 | Q1 | Basketball | 2023 |
| 1 | Q1 | Soccer | 2023 |
| 2 | Q2 | Soccer | 2023 |
| 1 | Q2 | Basketball | 2023 |
| 1 | Q4 | Basketball | 2023 |
| 2 | Q1 | Soccer | 2024 |
| 1 | Q2 | Basketball | 2024 |
| 1 | Q3 | Basketball | 2024 |
| 1 | Q4 | Soccer | 2024 |
Hints
- Use EXTRACT(YEAR FROM ...) and EXTRACT(QUARTER FROM ...) to derive grouping keys.
- Group by year, quarter, and sport_name, and then COUNT(*) the rows in each group.