SQL for Last Lead by Date | Dataford Interview Questions

Realtor wants to show each agent's most recent lead generated through Realtor surfaces such as the listing portal, referrals, or email campaigns. Write a PostgreSQL query that returns the latest dated lead for every agent.

Requirements

Return every agent, including agents with no valid dated leads.
Select the lead with the greatest generated_at for each agent.
Break timestamp ties deterministically by selecting the greatest lead_id.
Order the final results by agent_id.

Schema

agents

Column	Type	Description
agent_idPK	INT	Unique Realtor agent identifier
agent_name	VARCHAR(100)	Agent display name
market	VARCHAR(80)	Agent's primary real estate market

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
agent_id	INT	Assigned agent identifier
generated_at	TIMESTAMP	Timestamp when the lead was generated
source	VARCHAR(40)	Realtor lead acquisition source
status	VARCHAR(20)	Current lead status

Your question is SQL for Last Lead by Date. 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