To solve this problem, we need to identify the top customer (based on the highest number of orders) for each of the last 5 years present in the orders data.

 To solve this problem, we need to identify the top customer (based on the highest number of orders) for each of the last 5 years present in the orders data. The solution involves grouping orders by year and customer, counting the orders per customer per year, and then selecting the customer with the highest count for each year. In case of ties, the customer with the smallest customer_id is chosen.

Solution Code

sql

WITH last_five_years AS (
    SELECT DISTINCT EXTRACT(YEAR FROM order_date)::integer AS year
    FROM orders
    ORDER BY year DESC
    LIMIT 5
), 
yearly_orders AS (
    SELECT 
        EXTRACT(YEAR FROM o.order_date)::integer AS year,
        o.customer_id,
        COUNT(*) AS total_orders
    FROM orders o
    WHERE EXTRACT(YEAR FROM o.order_date)::integer IN (SELECT year FROM last_five_years)
    GROUP BY EXTRACT(YEAR FROM o.order_date), o.customer_id
), 
ranked_customers AS (
    SELECT 
        year,
        customer_id,
        total_orders,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_orders DESC, customer_id ASC) AS rn
    FROM yearly_orders
)
SELECT 
    rc.year,
    rc.customer_id,
    c.first_name,
    c.last_name,
    rc.total_orders
FROM ranked_customers rc
JOIN customers c ON rc.customer_id = c.customer_id
WHERE rc.rn = 1
ORDER BY rc.year DESC;

Explanation

1. Identify Last 5 Years: The last_five_years CTE retrieves the 5 most recent distinct years from the orders table, ordered descendingly.

2. Count Orders per Customer per Year: The yearly_orders CTE counts the number of orders for each customer in each of the last 5 years.

3. Rank Customers by Order Count: The ranked_customers CTE assigns a rank to each customer within each year based on:

   • total_orders in descending order (highest first).

   • customer_id in ascending order (to break ties by selecting the smallest ID).

4. Select Top Customers: The final query joins the top-ranked customers (where rank is 1) with the customers table to include their names and orders the result by year in descending order.