Candidate telemetry diagnostic, error autopsy, and step-by-step query construction walkthrough.
Live aggregated metrics across candidate sandbox attempts
20 solved
First attempt fail
Evaluated submissions
Median time to solve
Unlocked answer
Produce a monthly report combining MoM revenue growth with the top-spending customer in that month.
| Column | Type |
|---|---|
| CustomerId | INTEGER (PK) |
| FirstName | TEXT NOT NULL |
| LastName | TEXT NOT NULL |
| Company | TEXT |
| Country | TEXT |
| TEXT NOT NULL | |
| SupportRepId | INTEGER (FK → Employee) |
| Column | Type |
|---|---|
| InvoiceId | INTEGER (PK) |
| CustomerId | INTEGER (FK) |
| InvoiceDate | TIMESTAMP NOT NULL |
| BillingCountry | TEXT |
| Total | NUMERIC(10,2) NOT NULL |
YYYY-MMPreviousRevenue via LAG(), and MoM_Growth_Pct (rounded to 2 decimals, NULL for the first month)ROW_NUMBER() OVER (PARTITION BY month ORDER BY customer_spend DESC)Month, MonthlyRevenue, PreviousRevenue, MoM_Growth_Pct, TopCustomer (FirstName + space + LastName), TopCustomerSpendYour query should return 60 rows with 6 columns: | month | monthlyrevenue | previousrevenue | mom_growth_pct | topcustomer | topcustomerspend | |---------|----------------|-----------------|----------------|--------------------|------------------| | 2009-01 | 35.64 | NULL | NULL | John Gordon | 13.86 | | 2009-02 | 37.62 | 35.64 | 5.56 | Leonie Köhler | 13.86 | | 2009-03 | 37.62 | 37.62 | 0.0 | Dominique Lefebvre | 13.86 | | 2009-04 | 37.62 | 37.62 | 0.0 | Tim Goyer | 13.86 | | 2009-05 | 37.62 | 37.62 | 0.0 | Luis Rojas | 13.86 | | ... | ... | ... | ... | ... | ... |
Attempting to filter window function output directly inside the WHERE clause (e.g., WHERE ROW_NUMBER() OVER (...) <= 3). Because SQL executes WHERE before evaluating window functions, this raises a syntax error or produces invalid groupings. The calculation must be staged in a CTE or subquery first.
Interviewers use this question to verify whether you understand the exact SQL execution order (FROM -> WHERE -> GROUP BY -> HAVING -> WINDOW -> SELECT -> ORDER BY), how to choose correctly between ROW_NUMBER, RANK, and DENSE_RANK when handling ties, and how to partition datasets without collapsing rows.
Construct the solution logically from first principles to avoid typical edge case pitfalls.
Determine whether the ranking or running total resets per customer, department, or genre (PARTITION BY), or spans the entire table.
OVER (PARTITION BY <group_col> ORDER BY <order_col> DESC)
Write a WITH clause to calculate the window metric alongside the base columns, ensuring all join and filter conditions are applied.
WITH RankedData AS (
WITH monthly_revenue AS (
SELECT TO_CHAR(InvoiceDate, 'YYYY-MM') AS Month,
ROUND(SUM(Total), 2) AS MonthlyRev...
)Select from the CTE and apply the outer predicate (e.g., WHERE rnk = 1 or WHERE rnk <= N) to extract the final result set.
SELECT <columns> FROM RankedData WHERE rnk = 1 ORDER BY <columns>;
WITH monthly_revenue AS (
SELECT TO_CHAR(InvoiceDate, 'YYYY-MM') AS Month,
ROUND(SUM(Total), 2) AS MonthlyRevenue
FROM Invoice
GROUP BY TO_CHAR(InvoiceDate, 'YYYY-MM')
),
monthly_with_lag AS (
SELECT Month, MonthlyRevenue,
LAG(MonthlyRevenue) OVER (ORDER BY Month) AS PreviousRevenue
FROM monthly_revenue
),
customer_month AS (
SELECT TO_CHAR(i.InvoiceDate, 'YYYY-MM') AS Month,
i.CustomerId,
c.FirstName || ' ' || c.LastName AS CustomerName,
SUM(i.Total) AS Spend
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY TO_CHAR(i.InvoiceDate, 'YYYY-MM'), i.CustomerId, c.FirstName, c.LastName
),
top_per_month AS (
SELECT Month, CustomerName, Spend,
ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Spend DESC, CustomerId) AS rn
FROM customer_month
)
SELECT m.Month,
m.MonthlyRevenue,
m.PreviousRevenue,
CASE WHEN m.PreviousRevenue IS NULL OR m.PreviousRevenue = 0 THEN NULL
ELSE ROUND((m.MonthlyRevenue - m.PreviousRevenue) * 100.0 / m.PreviousRevenue, 2)
END AS MoM_Growth_Pct,
t.CustomerName AS TopCustomer,
ROUND(t.Spend, 2) AS TopCustomerSpend
FROM monthly_with_lag m
LEFT JOIN top_per_month t ON m.Month = t.Month AND t.rn = 1
ORDER BY m.Month ASC;Real code patterns candidates submit that fail the grading suite.
SELECT * FROM table_name WHERE ROW_NUMBER() OVER (ORDER BY amount DESC) <= 5;
SELECT department_id, employee_id, salary,
RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;Three recurring syntax and semantic traps relevant to this problem domain.
Window functions cannot appear in WHERE or HAVING clauses. Filtering on a rank or running total requires wrapping the query in a CTE or subquery.
SELECT *, RANK() OVER (ORDER BY points DESC) as rnk FROM candidates WHERE RANK() OVER (ORDER BY points DESC) <= 5; -- ❌ Syntax Error
WITH Ranked AS ( SELECT *, RANK() OVER (ORDER BY points DESC) as rnk FROM candidates ) SELECT * FROM Ranked WHERE rnk <= 5; -- ✅ Correct
Using RANK() skips rank positions on ties (1, 2, 2, 4), whereas DENSE_RANK() retains consecutive integers (1, 2, 2, 3). Using ROW_NUMBER() arbitrarily breaks ties.
SELECT name, RANK() OVER (ORDER BY score DESC) as rnk ... -- ❌ Might miss 3rd rank if 2nd ties
SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) as rnk ... -- ✅ Guaranteed continuous ranks
Forgetting the PARTITION BY clause causes ranking or rolling metrics to compute across the entire dataset rather than resetting per group/customer.
ROW_NUMBER() OVER (ORDER BY sale_date DESC) -- ❌ Global row number
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sale_date DESC) -- ✅ Per-customer rank
Launch our in-browser coding environment. Run queries, view execution plans, and get instant comparative diff grading with no setup.