Merchant Volume Trends | Dataford Interview Questions

Mastercard analytics teams monitor transaction activity by Merchant Category Code, or MCC. Treat transaction volume as the number of settled transactions. Write a PostgreSQL query that summarizes daily activity for each matched merchant category.

Requirements

Join settled transactions to merchant_categories using mcc_code.
Return the daily transaction count for each category and date.
Calculate a cumulative running total within each category, ordered by transaction date.
Calculate a three-row moving average of daily transaction volume, including the current day and up to the two preceding category rows. Round it to two decimal places.

Schema

payment_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
mcc_code	INT	Mastercard Merchant Category Code
transaction_date	DATE	Settlement date
status	VARCHAR(20)	Transaction processing status

merchant_categories

Column	Type	Description
mcc_codePK	INT	Mastercard Merchant Category Code
category_name	VARCHAR(100)	Human-readable merchant category

Your question is Merchant Volume Trends. 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