SQL for Top Customer Spend | Dataford Interview Questions

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.

Schema

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	Identifier of the customer who placed the order
customer_name	VARCHAR(100)	Customer display name captured on the order
order_date	DATE	Date on which the order was placed

order_items

Column	Type	Description
order_item_idPK	INTEGER	Unique order item identifier
order_id	INTEGER	Identifier of the associated order
quantity	INTEGER	Number of units in the item row
unit_price	NUMERIC(10,2)	Price per unit for the item row

Your question is SQL for Top Customer Spend. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem