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.
Output details
- Return columns in this order:
marketing_channel, customer_id, customer_name, transaction_count_30d, and channel_percent_rank.
- Treat
DATE '2024-06-01' as today's date.
- Use an inclusive lower bound and exclusive upper bound for the 30-day window.
- Filter qualifying rows where
channel_percent_rank is at most 0.05.
- Order results by
marketing_channel ascending, transaction_count_30d descending, then customer_id ascending.
- Break percentile ties by
customer_id ascending, and do not round channel_percent_rank.
