SQL for Repeat Customers | Dataford Interview Questions

Your question is SQL for Repeat 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

Affirm wants to identify customers who completed multiple purchases through Affirm Checkout during the 12 months ending 2025-01-15. Write a PostgreSQL query that returns one row per qualifying customer.

Requirements

Include only purchases with status = 'completed' from 2024-01-15 through 2025-01-15, inclusive.
Return the customer ID, customer name, number of completed purchases, total purchase amount, and most recent purchase date.
Include only customers with at least two qualifying purchases.
Sort by purchase count descending, total purchase amount descending, and customer ID ascending.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's display name
email	VARCHAR(255)	Customer email address

purchases

Column	Type	Description
purchase_idPK	INTEGER	Unique purchase identifier
customer_id	INTEGER	Customer associated with the purchase
purchase_date	DATE	Date the purchase was submitted
amount	NUMERIC(10,2)	Purchase amount
status	VARCHAR(20)	Purchase processing status