Window Functions for Purchase Rankings | Dataford Interview Questions

Amazon’s analytics team wants to monitor how frequently customers purchase over time. Write a PostgreSQL query that summarizes completed purchases by user and calendar month, then uses window functions to calculate running averages and rank users by purchase frequency.

Requirements

Consider only completed transactions from January 1 through March 31, 2024.
Calculate each user’s monthly completed purchase count.
Calculate the running average of monthly purchase counts for each user, ordered chronologically.
Rank users by their total completed purchase count in descending order, using standard competition ranking.

Return one row per user-month with user_name, purchase_month, monthly_purchase_count, running_avg_purchase_frequency, total_purchases, and frequency_rank.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Amazon customer identifier
user_name	VARCHAR(100)	Customer display name

transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
user_id	INTEGER	Customer who made the transaction
purchased_at	TIMESTAMP	Transaction timestamp
status	VARCHAR(20)	Transaction state

Your question is Window Functions for Purchase Rankings. 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