SQL Window Functions Basics | Dataford Interview Questions

What is a SQL window function, and what is the difference between using it with WHERE versus HAVING or filtering after the window calculation?

Use the provided claims table to demonstrate these evaluation stages in one query. Include only paid claims from 2025, retain members whose total paid amount is at least 500, rank those members by total, and filter to the top rank.

Output

One row per retained member with member_id, total_paid, and member_rank
Include only members with member_rank equal to 1
Order by member_id ascending

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted
status	VARCHAR(20)	Claim processing status
allowed_amount	DECIMAL(10,2)	Allowed amount for the claim

Your question is SQL Window Functions Basics. Start with the requirements and the one table 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

What is a SQL window function, and what is the difference between using it with WHERE versus HAVING or filtering after the window calculation?

Output

One row per retained member with member_id, total_paid, and member_rank
Include only members with member_rank equal to 1
Order by member_id ascending

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted
status	VARCHAR(20)	Claim processing status
allowed_amount	DECIMAL(10,2)	Allowed amount for the claim

Your question is SQL Window Functions Basics. Start with the requirements and the one table 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.