CTE Purchases Query | Dataford Interview Questions

Capgemini Engineering wants to identify customers who are engaging frequently with its digital commerce services. Write a PostgreSQL query using CTEs to find customers with more than three purchases during the last 30 days.

For reproducible testing, interpret the period relative to PostgreSQL's CURRENT_DATE. The sample data is positioned around the current date used for evaluation.

Requirements

Use at least one CTE to isolate purchases from the last 30 days.
Join the filtered purchases to customer records.
Count purchases for each customer and return only customers with more than three purchases.
Return results ordered by purchase count descending, then customer ID ascending.

Schema

customers

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

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
customer_id	INT	Customer who made the purchase
purchase_timestamp	TIMESTAMPTZ	Date and time of the purchase
amount	NUMERIC(10,2)	Purchase amount

Your question is CTE Purchases Query. 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