Candidate telemetry diagnostic, error autopsy, and step-by-step query construction walkthrough.
Live aggregated metrics across candidate sandbox attempts
19 solved
First attempt fail
Evaluated submissions
Median time to solve
Unlocked answer
The data team discovered that some artists have tracks in multiple genres. Find these cross-genre artists and show which genres they span.
| Column | Type |
|---|---|
| ArtistId | INTEGER (Primary Key) |
| Name | TEXT |
| Column | Type |
|---|---|
| AlbumId | INTEGER (Primary Key) |
| Title | TEXT |
| ArtistId | INTEGER (Foreign Key → Artist.ArtistId) |
| Column | Type |
|---|---|
| TrackId | INTEGER (Primary Key) |
| Name | TEXT |
| AlbumId | INTEGER (Foreign Key → Album.AlbumId) |
| MediaTypeId | INTEGER (Foreign Key) |
| GenreId | INTEGER (Foreign Key → Genre.GenreId) |
| Composer | TEXT |
| Milliseconds | INTEGER |
| Bytes | INTEGER |
| UnitPrice | NUMERIC(10,2) |
| Column | Type |
|---|---|
| GenreId | INTEGER (Primary Key) |
| Name | TEXT |
STRING_AGG(DISTINCT g.Name, ',') to list genresArtistName, GenreCount, Genres, TrackCountYour result should have 15 rows with 4 columns: | artistname | genrecount | genres | trackcount | |-----------------|------------|-------------------------------------|------------| | Iron Maiden | 4 | Blues,Heavy Metal,Metal,Rock | 213 | | Lenny Kravitz | 3 | Metal,Reggae,Rock | 57 | | Various Artists | 3 | Latin,Pop,Soundtrack | 56 | | Audioslave | 3 | Alternative,Alternative & Punk,Rock | 40 | | Gilberto Gil | 3 | Jazz,Latin,Soundtrack | 32 | | ... | ... | ... | ... |
Double-counting metrics by using COUNT(*) after joining parent and child tables. When joining an invoice table with an invoice lines table, a single invoice multiplies across all its line items, causing COUNT(invoice_id) to return line counts instead of unique invoice counts.
Interviewers check whether you notice 1-to-many cardinality multiplication and use COUNT(DISTINCT col) or pre-aggregate child records before joining.
Construct the solution logically from first principles to avoid typical edge case pitfalls.
Identify the base table and apply preliminary WHERE filters.
FROM TableName WHERE is_active = true
Group by primary business keys and compute aggregate expressions.
SELECT category, COUNT(DISTINCT item_id) AS total_items, SUM(amount) AS revenue GROUP BY category
Order by specified metrics descending and apply limit clauses.
ORDER BY revenue DESC LIMIT 10;
WITH ArtistGenres AS (
SELECT ar.ArtistId,
ar.Name AS ArtistName,
COUNT(DISTINCT g.GenreId) AS GenreCount,
STRING_AGG(DISTINCT g.Name, ',') AS Genres,
COUNT(DISTINCT t.TrackId) AS TrackCount
FROM Artist ar
JOIN Album al ON ar.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN Genre g ON t.GenreId = g.GenreId
GROUP BY ar.ArtistId, ar.Name
HAVING COUNT(DISTINCT g.GenreId) > 1
)
SELECT ArtistName, GenreCount, Genres, TrackCount
FROM ArtistGenres
ORDER BY GenreCount DESC, TrackCount DESC
LIMIT 15;Real code patterns candidates submit that fail the grading suite.
SELECT a.Name, COUNT(t.TrackId) FROM Artist a JOIN Album al ON a.ArtistId = al.ArtistId JOIN Track t ON al.AlbumId = t.AlbumId GROUP BY a.Name;
Three recurring syntax and semantic traps relevant to this problem domain.
Joining a fact table with child lines multiplies fact table rows, distorting sums and counts.
SELECT c.id, SUM(i.total) FROM customer c JOIN invoice i ON c.id = i.customer_id JOIN invoice_line il ON i.id = il.invoice_id -- ❌ Inflated SUM
SELECT c.id, SUM(i.total) FROM customer c JOIN invoice i ON c.id = i.customer_id GROUP BY c.id; -- ✅ Avoids line multiplication
Using COUNT(*) when duplicate rows exist due to joins counts duplicate records.
SELECT artist_id, COUNT(album_id) ... -- ❌ Counts duplicate occurrences
SELECT artist_id, COUNT(DISTINCT album_id) ... -- ✅ Distinct unique entities
In SQL, dividing integers like 5 / 10 results in 0. Cast at least one operand to FLOAT or NUMERIC.
SELECT solved_count / total_count AS rate ... -- ❌ Returns 0
SELECT CAST(solved_count AS FLOAT) / total_count AS rate ... -- ✅ Returns 0.5
Launch our in-browser coding environment. Run queries, view execution plans, and get instant comparative diff grading with no setup.