Your question is SQL Join and Grouping Query. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Xero's product discovery team is testing a catalog reporting pattern for grouped product data. Using the game catalog below, write a PostgreSQL query that counts short games for every console.
Use a threshold of 10 hours, and treat a game as short only when playtime_hours < 10. Consoles with no qualifying games must still appear with a count of zero.
| Column | Type | Description |
|---|---|---|
| console_idPK | INTEGER | Unique console identifier |
| console_name | VARCHAR(100) | Display name of the console |
| Column | Type | Description |
|---|---|---|
| game_idPK | INTEGER | Unique game identifier |
| game_title | VARCHAR(150) | Game title |
| playtime_hours | NUMERIC(6,2) | Estimated hours needed to complete the game |
| Column | Type | Description |
|---|---|---|
| game_id | INTEGER | Reference to games.game_id |
| console_id | INTEGER | Reference to consoles.console_id |