Explain SQL code

What this SQL sample shows

joins

LEFT JOIN keeps every customer, including one with no matching orders. For that customer the order columns come back as NULL.

aggregation

GROUP BY folds each customer's orders into one row. COUNT(o.id) counts only real orders, so a customer with none gets 0, where COUNT(*) would give 1.

window functions

RANK() OVER (...) runs after the grouping and numbers the rows by spend without merging them. Ties share a rank and the next rank is skipped: two customers at 1 are followed by 3.

Good to know

• The date filter sits in the ON clause on purpose. Moved to WHERE, it would drop every customer without an order this year, and the LEFT JOIN would behave like an inner join.
• COALESCE(SUM(o.total), 0) turns the NULL sum of a customer with no orders into 0, both in the output and in the ranking.
• Use DENSE_RANK() to number ties 1, 1, 2 instead of 1, 1, 3.
• On a large orders table, an index on orders (customer_id, created_at) lets the database find each customer's recent orders without reading the whole table.

Watch out: Comparing created_at with '2026-01-01' works when dates are stored as a date type or as ISO text like 2026-03-01. Text in another format, such as 03/01/2026, compares character by character and gives wrong results.