EPAM India's order analytics dashboard needs to identify its highest-value customers. As of 2024-04-01, write a PostgreSQL query that finds the three customers with the greatest spending during the preceding three calendar months, from 2024-01-01 inclusive through 2024-04-01 exclusive.
Requirements
- Join
orders to order_items using order_id.
- Calculate spending as
quantity * unit_price for each item.
- Aggregate spending by customer, exclude orders outside the date range and customers with no customer ID, then return the top three customers in descending spending order.
Output details
- Return
customer_id, customer_name, and total_spent in that order.
- Sort equal spending by
customer_id ascending.
- Treat distinct (
customer_id, customer_name) pairs as separate customer records.
- Preserve full numeric precision without rounding
total_spent.
