I have customers and orders tables. I want the top three customers based on order amount per year. How would you write the SQL query?

Question

Assisting AI · Accepted Answer

To retrieve the top three customers by yearly order amount, you can aggregate the orders per customer per year, then rank them and pick the top three. A straightforward approach uses GROUP BY and ORDER BY:

```sql
SELECT
  c.customer_id,
  c.customer_name,
  YEAR(o.order_date) AS order_year,
  SUM(o.order_amount) AS total_amount
FROM
  customers c
JOIN
  orders o ON c.customer_id = o.customer_id
GROUP BY
  c.customer_id, c.customer_name, YEAR(o.order_date)
ORDER BY
  order_year,
  total_amount DESC
LIMIT 3;
```

This returns the three customers with the highest total order amount for the most recent year in the dataset. If you need the top three for *each* year, use a window function:

```sql
WITH yearly_totals AS (
  SELECT
    c.customer_id,
    c.customer_name,
    YEAR(o.order_date) AS order_year,
    SUM(o.order_amount) AS total_amount
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.customer_name, YEAR(o.order_date)
), ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY order_year ORDER BY total_amount DESC) AS rn
  FROM yearly_totals
)
SELECT customer_id, customer_name, order_year, total_amount
FROM ranked
WHERE rn <= 3;
```

Complexity: O(n) for scanning orders, plus O(k log k) for sorting each year’s totals, where k is the number of customers in that year. The window approach is efficient and scales well with large datasets.

I have customers and orders tables. I want the top three customers based on order amount per year. How would you write the SQL query?

💡 Model Answer

🎤 Get questions like this answered in real-time