codejump
HardPro challengeSQL

First and Last Deal Per Rep

SQLDatabasesWindow Functions

Table: deals

repdeal_dateamount
Alice2024-01-01100
Alice2024-02-01300
Alice2024-03-01200
Bob2024-01-1550
Bob2024-02-1580

Write a query that returns every deal alongside that **rep's first deal
amount and last deal amount** (chronologically), as two extra columns
on every row.

Expected output (columns: rep, deal_date, amount,
first_deal, last_deal), ordered by rep then deal_date:

Alice|2024-01-01|100|100|200
Alice|2024-02-01|300|100|200
Alice|2024-03-01|200|100|200
Bob|2024-01-15|50|50|80
Bob|2024-02-15|80|50|80

Hint — FIRST_VALUE/LAST_VALUE need PARTITION BY rep so each

rep's deals are considered independently. LAST_VALUE additionally needs

an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

frame — otherwise it (confusingly) only sees up to the *current* row, and

"last" would change on every row instead of being the partition's actual

last deal.

Sample tests

Test #1Two reps, each with multiple deals
Input: "CREATE TABLE deals (rep TEXT, deal_date TEXT, amount INTEGER);\nINSERT INTO deals VALUES\n ('Alice', '2024-01-01', 100),\n ('Alice', '2024-02-01', 300),\n ('Alice', '2024-03-01', 200),\n ('Bob', '2024-01-15', 50),\n ('Bob', '2024-02-15', 80);"
Output: "Alice|2024-01-01|100|100|200\nAlice|2024-02-01|300|100|200\nAlice|2024-03-01|200|100|200\nBob|2024-01-15|50|50|80\nBob|2024-02-15|80|50|80"
Test #2Single deal — first and last are the same
Input: "CREATE TABLE deals (rep TEXT, deal_date TEXT, amount INTEGER);\nINSERT INTO deals VALUES ('Solo', '2024-01-01', 999);"
Output: "Solo|2024-01-01|999|999|999"
← All challenges