Level 3 - Question 10: Complex Real-World Business Scenario

Solution:

SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_spent,
    ROUND(AVG(o.amount), 2) AS avg_order_value,
    MAX(o.order_date) AS last_order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY c.customer_id, c.name, c.email
HAVING COUNT(o.order_id) > 2
ORDER BY total_spent DESC
FETCH FIRST 5 ROWS ONLY;

This advanced query demonstrates multiple concepts:

• INNER JOIN: Combines customer and order data
• WHERE with date function: Filters orders from the last year using ADD_MONTHS
• GROUP BY: Aggregates data per customer
• HAVING: Filters groups with more than 2 orders
• Aggregate functions: COUNT, SUM, AVG, MAX for comprehensive metrics
• FETCH FIRST: Limits results to top 5 (Oracle 12c+ syntax)

Alternative for older Oracle versions: Use ROWNUM or ROW_NUMBER() instead of FETCH FIRST.