Top Products and Repeat Purchases | Dataford Interview Questions

Your question is Top Products and Repeat Purchases. Start with the requirements and the three 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

Business context

Vanguard's digital commerce analytics team wants a recent sales snapshot for products and customer retention signals.

Task

As of 2025-02-01, write one PostgreSQL query that returns the top three products by sales during the inclusive 30-day period ending on that date, and identifies customers who made another purchase within seven days of an earlier purchase.

Requirements

Join purchases to products and calculate product sales as quantity * unit_price.
Filter product sales to the inclusive 30-day period and rank products by total sales, returning only the top three.
Use purchase dates to identify customers with a later purchase no more than seven days after an earlier purchase.
Return both result types in one result set, with result_type set to TOP_PRODUCT or REPEAT_CUSTOMER.

Schema

customers

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

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer who made the purchase
product_id	INT	Product purchased
purchase_date	DATE	Date of purchase
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at purchase time