How would you design a scalable database schema to handle high-volume transaction logging and reporting requirements? Use the provided schema to produce a monthly report for active Mastercard merchants from January through March 2025, considering only transactions whose latest event is SETTLED.
Output
- One row per active merchant and reporting month, including months with no settled transactions
- Columns:
merchant_id, merchant_name, report_month, transaction_count, total_amount, prior_month_amount, month_over_month_pct, and cumulative_amount
- Order by
merchant_id, then report_month ascending
Output details
- Use
2025-01-01 through 2025-03-31 inclusively, with timestamps on or after 2025-01-01 00:00:00 and before 2025-04-01 00:00:00.
- Represent each
report_month as the first day of its month: 2025-01-01, 2025-02-01, and 2025-03-01.
- For each transaction, use the event with the greatest
event_id as its latest event, and include it only when that event is SETTLED.
- Format
total_amount and cumulative_amount as numeric values with exactly two decimal places, replacing missing totals with 0.00.
- Set
prior_month_amount to NULL for the first month, and calculate it as the preceding reporting month's total_amount.
- Return
month_over_month_pct rounded to two decimals, but use NULL when prior_month_amount is NULL or zero.
