Interview Guides
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.
