Optimize Q1 SQL Join and Monthly Settlement Aggregation

MediumSQL · PostgreSQL

Problem

How do you optimize a slow-running SQL join on tables containing millions of enterprise transaction records?

Use the supplied transaction, account, and event tables. Return only positive transactions from the first quarter of 2025 whose latest event before April 1, 2025 is SETTLED.

Output

One row per account and transaction month
Columns: account_id, account_name, transaction_month, settled_transaction_count, and total_settled_amount
Sort by account_id, then transaction_month ascending

Output details

Represent transaction_month as the first calendar day of each month.
When events share a transaction and timestamp, select the greatest event_id as the latest event.
Group results by account_name in addition to account_id and transaction_month.

Schema

enterprise_accounts

Column	Type	Description
account_idPK	INT	Unique enterprise account identifier
account_name	VARCHAR(120)	Enterprise account name
region	VARCHAR(40)	Account operating region

enterprise_transactions

Column	Type	Description
transaction_idPK	INT	Unique enterprise transaction identifier
account_id	INT	Owning enterprise account
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(18,2)	Transaction amount in the transaction currency
currency	VARCHAR(3)	ISO currency code

transaction_events

Column	Type	Description
event_idPK	INT	Unique transaction event identifier
transaction_id	INT	Transaction associated with the event
event_time	TIMESTAMP	Time when the event was recorded
event_status	VARCHAR(20)	Status recorded by the event

Tablesenterprise_accountsenterprise_transactionstransaction_events