Drill 3 of 4 in Banking questions
0

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…