Calculate 2025 Customer Revenue and Regional Rank

Focus on coding with syntax not just logic; write the code in both SQL and Spark, and use a basic understanding of AWS services.

Use the supplied customer and order data to calculate each customer's completed 2025 revenue and position within their region. Provide the SQL solution and describe the equivalent Spark approach and an appropriate AWS execution option.

Output

1. One row per customer, including customers without qualifying orders
2. Columns: region, customer_id, customer_name, total_revenue, and revenue_rank
3. Rank ties equally, sort by region, revenue descending, and customer name

Output details

• Count only orders whose status equals completed.
• Use an inclusive lower bound of 2025-01-01 and an exclusive upper bound of 2026-01-01.
• Return total_revenue as 0 when a customer has no qualifying revenue or the sum is NULL.
• Use competition ranking with gaps, ranking customers only by total_revenue descending within each region.
• Sort NULL regions last.