Rank Top Advisors by Region | Dataford Interview Questions

Your question is Rank Top Advisors by Region. 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

Edward Jones wants to identify the strongest financial advisors in each region based on new clients acquired during 2025. Write a PostgreSQL query that includes advisors with no qualifying acquisitions and returns the top three advisors per region.

Requirements

Count each advisor's clients acquired from 2025-01-01 through 2025-12-31.
Use a LEFT JOIN so advisors with zero new clients remain eligible.
Rank advisors within each region by acquisition count descending, breaking ties by advisor ID ascending.
Return only the top three advisors per region, ordered by region and rank.

Schema

advisors

Column	Type	Description
advisor_idPK	INTEGER	Unique financial advisor identifier
advisor_name	VARCHAR(100)	Financial advisor name
region	VARCHAR(50)	Edward Jones operating region

clients

Column	Type	Description
client_idPK	INTEGER	Unique client identifier
advisor_id	INTEGER	Advisor assigned to the client
acquired_date	DATE	Date the client was acquired