Window Functions Data Retrieval | Dataford Interview Questions

Ancestry wants to understand which paid members had the most productive sessions in Ancestry Record Search during January 2025. Write a PostgreSQL query using a window function to return each paid member's best qualifying session, including paid members with no qualifying session.

Requirements

Consider only members with a Plus or Pro subscription.
Include only completed sessions on the Record Search surface from 2025-01-01 inclusive through 2025-02-01 exclusive.
Rank each member's sessions by result_count descending, then by started_at descending to break ties, and return rank 1 for each member.
Preserve paid members without a qualifying session, showing NULL session fields. Order the final results by member_id.

Schema

members

Column	Type	Description
member_idPK	INTEGER	Unique Ancestry member identifier
display_name	VARCHAR(100)	Member display name
subscription_tier	VARCHAR(20)	Current membership tier
country_code	VARCHAR(2)	Two-letter member country code

search_sessions

Column	Type	Description
session_idPK	INTEGER	Unique search session identifier
member_id	INTEGER	Member associated with the session
started_at	TIMESTAMP	Timestamp when the session began
search_surface	VARCHAR(40)	Ancestry product surface used
result_count	INTEGER	Number of results returned by the session
status	VARCHAR(20)	Session completion state

Your question is Window Functions Data Retrieval. 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