Write a SQL query that finds the top 5 customers by total order value in the last 12 months, and explain how it works.

Here is the SQL query (written for standard SQL/PostgreSQL/MySQL):

SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    SUM(o.total) AS total_spent
FROM customers c
JOIN orders o 
    ON c.id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '12 month'
GROUP BY 
    c.id, 
    c.name
ORDER BY 
    total_spent DESC
LIMIT 5;

How it works step-by-step:

1. JOIN orders o ON c.id = o.customer_id
   Combines the customers and orders tables by matching each customer to their respective orders. An INNER JOIN is used so that customers with no orders during the timeframe are excluded.

2. WHERE o.created_at >= CURRENT_DATE - INTERVAL '12 month'
   Filters out any orders placed older than 12 months from the current date. (Note: depending on the SQL dialect, date syntax can vary slightly—e.g., NOW() - INTERVAL 12 MONTH in MySQL, DATEADD(month, -12, GETDATE()) in SQL Server).

3. GROUP BY c.id, c.name
   Groups the records by customer so we can perform aggregation per individual. Grouping by both id and name ensures uniqueness even if two customers share the same name.

4. SUM(o.total) AS total_spent
   Calculates the sum of all order totals for each customer within that 12-month window.

5. ORDER BY total_spent DESC
   Sorts the grouped results from the highest spenders to the lowest.

6. LIMIT 5
   Restricts the final output to only the top 5 highest-spending customers. (In SQL Server or Oracle, you would use TOP 5 or FETCH FIRST 5 ROWS ONLY instead).