Your question is Top 5% Customers by Channel. 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.
You are given customer transaction data and a Capital One-style marketing attribution table that maps each customer to an acquisition channel such as Email, Paid Search, or CreditWise. Write a PostgreSQL query to return the customers who fall in the top 5% by transaction volume in the last 30 days, calculated separately within each marketing channel.
Treat transaction volume as the count of transactions in the last 30 days. Return each qualifying customer's channel, customer ID, customer name, 30-day transaction count, and their percentile rank within the channel. Assume the top 5% should be determined using a window-function-based percentile approach, and exclude transactions outside the 30-day window or rows with missing channel attribution.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| signup_date | DATE | Date the customer signed up |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Customer identifier |
| marketing_channel | VARCHAR(50) | Attributed acquisition or engagement channel |
| attribution_date | DATE | Date the marketing channel attribution was recorded |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| customer_id | INT | Customer who made the transaction |
| transaction_date | DATE | Date of the transaction |
| amount | DECIMAL(10,2) | Transaction amount |