Interview Question Bank · Filter, then first row per group · DISTINCT ON
3. First deposit over $1,000
MediumBankingQuestion #101
Concept: Filter, then first row per group · DISTINCT ONshow
'The first X per group' is two steps in a fixed order: filter to the rows that qualify, then take the earliest per group. DISTINCT ON (group) with ORDER BY group, time keeps each group's first row; ROW_NUMBER() OVER (PARTITION BY group ORDER BY time) = 1 does the same. Ranking every row and then keeping row 1 only works when that row happens to qualify.
Example (different data):
SELECT DISTINCT ON (student_id) student_id, exam, score, taken_on FROM exam_results WHERE score >= 90 ORDER BY student_id, taken_on, exam;
Your task
Copperleaf Bank pays a referral bonus when an account receives its first deposit above $1,000, and finance wants to see which deposit triggered each bonus.
From fin_ledger (positive amount = deposit, negative = withdrawal), return account_id, entry_id, posted_at and amount for each account's earliest deposit of more than $1,000. Leave out accounts with no such deposit. Sort by account_id.
Starting Postgres in your browser…
Checking your access…