SQL for Top LTV Holiday Customers | Dataford Interview Questions

Your question is SQL for Top LTV Holiday Customers. Start with the requirements and the two tables on the right.

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

Problem

Shutterfly wants to target its highest-value customers who were active during the most recent holiday season. Write a PostgreSQL query using completed orders to find customers in the top 10% by lifetime value who made at least one completed purchase from November 1 through December 31, 2025.

Requirements

Calculate lifetime value as the sum of completed order amounts across all available order history.
Rank all customers into ten lifetime-value deciles, including customers with no completed orders.
Keep only customers in the top decile who completed at least one purchase during the holiday period.
Return the customer ID, name, lifetime value, and holiday order count, sorted by lifetime value descending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Revenue associated with the order
status	VARCHAR(20)	Order processing status