Candidate telemetry diagnostic, error autopsy, and step-by-step query construction walkthrough.
Live aggregated metrics across candidate sandbox attempts
28 solved
First attempt fail
Evaluated submissions
Median time to solve
Unlocked answer
Show quarter-by-quarter revenue per genre across calendar years 2012 and 2013 for the four largest genres.
| Column | Type |
|---|---|
| InvoiceLineId | INTEGER (PK) |
| InvoiceId | INTEGER (FK) |
| TrackId | INTEGER (FK) |
| UnitPrice | NUMERIC(10,2) NOT NULL |
| Quantity | INTEGER NOT NULL |
| Column | Type |
|---|---|
| TrackId | INTEGER (PK) |
| Name | TEXT NOT NULL |
| AlbumId | INTEGER (FK → Album) |
| MediaTypeId | INTEGER (FK → MediaType) |
| GenreId | INTEGER (FK → Genre) |
| Composer | TEXT |
| Milliseconds | INTEGER NOT NULL |
| Bytes | INTEGER |
| UnitPrice | NUMERIC(10,2) NOT NULL |
| Column | Type |
|---|---|
| GenreId | INTEGER (PK) |
| Name | TEXT |
| Column | Type |
|---|---|
| InvoiceId | INTEGER (PK) |
| CustomerId | INTEGER (FK) |
| InvoiceDate | TIMESTAMP NOT NULL |
| BillingCountry | TEXT |
| Total | NUMERIC(10,2) NOT NULL |
Genre, Year, Quarter (1-4), Revenue (rounded to 2 decimals)Your query should return 29 rows with 4 columns: | genre | year | quarter | revenue | |-------|------|---------|---------| | Jazz | 2012 | 2 | 2.97 | | Jazz | 2012 | 4 | 2.97 | | Jazz | 2013 | 1 | 14.85 | | Jazz | 2013 | 2 | 2.97 | | Jazz | 2013 | 3 | 1.98 | | ... | ... | ... | ... |
Putting aggregated filter conditions in the WHERE clause instead of HAVING, or including un-aggregated columns in SELECT without listing them in GROUP BY. Postgres strictly enforces that every non-aggregated projection column must appear in the GROUP BY expression.
Interviewers verify whether you understand the distinction between row-level filtering (WHERE) versus post-aggregation partition filtering (HAVING), as well as SQL standard group syntax.
Construct the solution logically from first principles to avoid typical edge case pitfalls.
Determine the attributes that define unique summary rows (e.g. Artist, Country, or Category).
GROUP BY entity_id, entity_name
Apply SUM, AVG, COUNT, or conditional aggregations over each bucket.
SELECT entity_name, COUNT(*) AS total_items, SUM(amount) AS total_revenue
Filter only the groups that satisfy minimum aggregate thresholds.
HAVING COUNT(*) >= 10 ORDER BY total_revenue DESC;
SELECT g.Name AS Genre,
EXTRACT(YEAR FROM i.InvoiceDate)::int AS Year,
EXTRACT(QUARTER FROM i.InvoiceDate)::int AS Quarter,
ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS Revenue
FROM InvoiceLine il
JOIN Track t ON il.TrackId = t.TrackId
JOIN Genre g ON t.GenreId = g.GenreId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
WHERE g.Name IN ('Rock', 'Latin', 'Metal', 'Jazz')
AND EXTRACT(YEAR FROM i.InvoiceDate)::int IN (2012, 2013)
GROUP BY g.GenreId, g.Name, EXTRACT(YEAR FROM i.InvoiceDate), EXTRACT(QUARTER FROM i.InvoiceDate)
ORDER BY Genre, Year, Quarter;Real code patterns candidates submit that fail the grading suite.
SELECT country, SUM(total) FROM Invoice WHERE COUNT(InvoiceId) > 10 GROUP BY country;
Three recurring syntax and semantic traps relevant to this problem domain.
WHERE operates on individual rows before grouping occurs. Aggregate functions like COUNT(), SUM(), AVG() can only be filtered in HAVING.
SELECT genre_id, COUNT(*) FROM tracks WHERE COUNT(*) > 50 GROUP BY genre_id; -- ❌ Syntax Error
SELECT genre_id, COUNT(*) FROM tracks GROUP BY genre_id HAVING COUNT(*) > 50; -- ✅ Correct
Every non-aggregated column in the SELECT list must appear in the GROUP BY clause.
SELECT artist_id, artist_name, COUNT(album_id) FROM albums GROUP BY artist_id; -- ❌ artist_name missing
SELECT artist_id, artist_name, COUNT(album_id) FROM albums GROUP BY artist_id, artist_name; -- ✅ Correct
COUNT(*) counts every row in the group including NULLs. COUNT(column) counts only non-null instances.
SELECT department, COUNT(commission_pct) FROM employees GROUP BY department; -- ❌ Ignores 0-commission staff
SELECT department, COUNT(*) FROM employees GROUP BY department; -- ✅ Accurate total count
Launch our in-browser coding environment. Run queries, view execution plans, and get instant comparative diff grading with no setup.