Second-Highest Transaction for Advisor | Dataford Interview Questions

Raymond James Financial Services needs to review transaction activity for a specific advisor account. Write a PostgreSQL query that returns the second-highest distinct posted financial transaction amount for account RJ-1042.

Requirements

Join advisor_accounts to financial_transactions using the account identifier.
Consider only transactions for account RJ-1042 with a POSTED status and a non-NULL amount.
Rank distinct transaction amounts from highest to lowest within the account, and return every transaction tied at the second-highest amount.
Return the account number, transaction ID, and transaction amount, ordered by transaction ID.

Schema

advisor_accounts

Column	Type	Description
account_idPK	INT	Internal advisor account identifier
account_number	VARCHAR(20)	Raymond James advisor account number
advisor_name	VARCHAR(100)	Advisor assigned to the account

financial_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Advisor account associated with the transaction
transaction_type	VARCHAR(30)	Type of financial transaction
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
currency	VARCHAR(3)	ISO currency code

Your question is Second-Highest Transaction for Advisor. 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