You are working as a data analyst for a national retail company that operates across multiple geographic regions. The sales team wants to identify their most loyal customers in each region — specifically, the top 3 customers who have placed the most orders. This insight helps regional managers reward high-frequency buyers and design targeted retention campaigns. The database contains two tables. The customers table holds customer profile data with columns: customer_id (INT, primary key), customer_name (VARCHAR), and region (VARCHAR). The orders table records each purchase with columns: order_id (INT, primary key), customer_id (INT, foreign key), order_date (DATE), and amount (DECIMAL). Your query must rank customers within each region by how many orders they have placed, then return only the top 3 per region. The final result should clearly show each customer's region, name, total order count, and their rank within that region, sorted by region and rank. Count the total number of orders placed by each customer. Rank customers within each region from highest to lowest order count. Return only the top 3 ranked customers per region (rank 1, 2, and 3). Output columns: region , customer_name , order_count , rank . Sort results by region ascending, then rank ascending. Hint: Use a CTE (Common Table Expression) to first aggregate order counts per customer, then apply a window function ( ROW_NUMBER() with PARTITION BY region ORDER BY order_count DESC ) in a second CTE to assign rankings. Finally, filter in the outer query where rank is less than or equal to 3. A common pitfall is trying to filter on a window function alias directly in a WHERE clause within the same SELECT — this will fail because window functions are evaluated after WHERE. Always wrap the ranking step in a subquery or CTE before filtering.

Table Setup (SQL Schema)

CREATE TABLE customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(100), region VARCHAR(50)); CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount DECIMAL(10, 2)); INSERT INTO customers VALUES (1, 'Alice Johnson', 'North'), (2, 'Bob Smith', 'North'), (3, 'Carol White', 'North'), (4, 'David Brown', 'North'), (5, 'Eve Davis', 'South'), (6, 'Frank Miller', 'South'), (7, 'Grace Wilson', 'South'), (8, 'Henry Moore', 'East'), (9, 'Isla Taylor', 'East'), (10, 'Jack Anderson', 'East'); INSERT INTO orders VALUES (1, 1, '2024-01-05', 150.00), (2, 1, '2024-01-15', 200.00), (3, 1, '2024-02-10', 180.00), (4, 2, '2024-01-08', 300.00), (5, 2, '2024-02-12', 250.00), (6, 3, '2024-01-20', 400.00), (7, 3, '2024-02-18', 350.00), (8, 3, '2024-03-05', 200.00), (9, 3, '2024-03-15', 100.00), (10, 4, '2024-01-22', 120.00), (11, 5, '2024-01-10', 500.00), (12, 5, '2024-02-20', 450.00), (13, 5, '2024-03-12', 300.00), (14, 5, '2024-03-25', 200.00), (15, 6, '2024-01-14', 600.00), (16, 6, '2024-02-08', 550.00), (17, 6, '2024-02-28', 400.00), (18, 7, '2024-01-18', 250.00), (19, 7, '2024-02-22', 300.00), (20, 8, '2024-01-05', 700.00), (21, 8, '2024-02-10', 650.00), (22, 8, '2024-03-15', 500.00), (23, 9, '2024-01-12', 400.00), (24, 9, '2024-02-18', 350.00), (25, 9, '2024-03-20', 300.00), (26, 9, '2024-04-05', 200.00), (27, 10, '2024-01-25', 150.00), (28, 10, '2024-02-28', 100.00);