SQL for Corporate Action Payments | Dataford Interview Questions

How would you write a SQL query to find all accounts that have processed a corporate action payment of over $100,000 in the last 30 days?

Use the provided PostgreSQL tables and consider only payments marked as processed. The result should summarize qualifying payments at the account level.

Output

One row per qualifying account.
Return account_id, account_name, qualifying_payment_count, and maximum_payment_amount.
Include payments processed from 30 days ago through today, order by maximum payment amount descending, then account ID ascending.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account display name
account_type	VARCHAR(30)	Account classification

corporate_action_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
account_id	INT	Account receiving the payment
payment_amount	NUMERIC(15,2)	Corporate action payment amount
processed_at	TIMESTAMP	Timestamp when the payment was processed
status	VARCHAR(20)	Payment processing status
corporate_action_type	VARCHAR(30)	Type of corporate action

Your question is SQL for Corporate Action Payments. 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