Lab: design & performance
Rank rows with a window function and count across a left join — checked instantly.
- Use a window function to rank rows
- Count across a LEFT JOIN, including the zeros
Optional lab. Two queries on the shop. Hit Check to compare your result against the correct one; Run to see your output.
Checkpoint 1 — rank with a window
Return each product's name, price, and its rank by price (most expensive = rank 1), ordered from most to least expensive. Use a window function for the rank.
Checkpoint 2 — count across a LEFT JOIN
The tricky part: customers with no orders must show a count of 0, which
means a LEFT JOIN and counting the order column (not *).
Return every customer name and how many orders they have placed, including customers with zero orders. Two columns.
COUNT(*) would count the row the LEFT JOIN keeps even when there's no order,
giving 1 instead of 0. COUNT(o.id) counts only non-NULL order ids — so
customers with no orders correctly count 0.
Done?
That's the SQL core complete — querying, combining, and designing for correctness and speed. The dialect appendices cover where PostgreSQL, SQLite, and SQL Server diverge from the standard you've learned.