SQL Top 5% by Tier | Dataford Interview Questions

Navy Federal Credit Union wants to identify the highest-volume members within each card tier. Write a PostgreSQL query that aggregates completed card transactions and uses window functions to return the top 5% of members in every tier.

Requirements

Join members to card transactions while retaining members with no completed transactions.
Calculate each member's completed transaction volume, treating missing or NULL amounts as zero.
Rank members within each card_tier by volume descending, using member_id as a deterministic tie-breaker.
Return at least one member per tier, even when 5% of a tier contains fewer than one member.

Schema

members

Column	Type	Description
member_idPK	INT	Navy Federal member identifier
member_name	VARCHAR(100)	Member's full name
card_tier	VARCHAR(20)	Member's Navy Federal card tier

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique card transaction identifier
member_id	INT	References members.member_id
transaction_amount	DECIMAL(12,2)	Amount of the card transaction
transaction_status	VARCHAR(20)	Transaction status, such as COMPLETED or PENDING

Your question is SQL Top 5% by Tier. 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