Rank Rows Within Each Group | Dataford Interview Questions

Your question is Rank Rows Within Each Group. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

RBC branch operations wants to identify the highest-balance active accounts at each branch. Write a PostgreSQL query that joins account records to RBC branches and ranks eligible accounts within each branch using a window function.

Requirements

Include only accounts whose status is Active and whose current_balance is not NULL.
Use RANK() with PARTITION BY branch_id, ordering balances from highest to lowest.
Preserve ties, so accounts with equal balances receive the same rank.
Return branch details, account details, balance, and rank, ordered by branch and rank.

Schema

branches

Column	Type	Description
branch_idPK	INTEGER	Unique RBC branch identifier
branch_name	VARCHAR(100)	RBC branch name
province	VARCHAR(50)	Canadian province containing the branch

accounts

Column	Type	Description
account_idPK	INTEGER	Unique account identifier
branch_id	INTEGER	RBC branch associated with the account
account_type	VARCHAR(30)	Account product type
status	VARCHAR(20)	Current account status
current_balance	NUMERIC(12,2)	Current balance of the account