Count sports by calendar quarter in SQL
Company: Reevo
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: 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(game_date DATE, team_name VARCHAR, sport_name VARCHAR). Write a SQL query that returns, for each year and calendar quarter (Q1–Q4), each sport_name and the count of rows in that quarter. Output columns: year, quarter (as 'Q1', 'Q2', 'Q3', or 'Q4'), sport_name, cnt. Order results by year ASC, quarter ASC, cnt DESC, sport_name ASC.
Tables
games(game_date DATE, team_name VARCHAR(50), sport_name VARCHAR(50))
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.