Groww mid FIRST_VALUELAST_VALUEWindow FunctionsPARTITION BYCTE

For each user in portfolio_snapshots, ignoring any row where portfolio_value is NULL, find their first_value (portfolio_value on their earliest snapshot_date) and last_value (portfolio_value on their most recent snapshot_date). Calculate growth_amount as last_value minus first_value, and growth_pct as that difference as a percentage of first_value, rounded to 2 decimal places. Return one row per user_name with first_value, last_value, growth_amount, and growth_pct, sorted by growth_pct in descending order.

Groww · Portfolio Growth Tracking — practice this real-world SQL scenario live in your browser.

📖 Story Groww · Portfolio Growth Tracking

Sunday evening at Groww's Investor Insights team. portfolio_snapshots logs a user's total portfolio value every time it's captured — usually monthly, but a sync job that ran on 2026-06-15 for one user failed partway through and logged a row with no value at all, a placeholder that shouldn't be treated as a real data point. The team wants a simple growth signal for every investor: their portfolio value on their very first recorded snapshot compared against their most recent one, to spot who's actually growing and who's been quietly losing money since they started.

🎯 Your Mission

📋 Table Structure

🗂 portfolio_snapshots

id INTEGER 1

user_name TEXT Aarav

snapshot_date TEXT 2026-05-01

portfolio_value REAL 100000

⚡ Step-by-Step Walkthrough

Look at the raw snapshots, including the failed sync

query.sql

SELECT *
FROM portfolio_snapshots
ORDER BY user_name, snapshot_date;

💡 Explanation

portfolio_snapshots has one row per capture, not one row per user — user_name repeats across every date a snapshot was taken, and the three users have different numbers of snapshots (Aarav has more than Karthik, who joined later).
Aarav's 2026-06-15 row has NULL portfolio_value — the sync job that day failed partway through, so a row got logged with a date but no actual value; it isn't a genuine zero and shouldn't count as a real data point.
Karthik's earliest snapshot is 2026-06-01, a full month after Aarav's and Priya's earliest ones — 'first snapshot' has to mean each user's own earliest date, not some shared calendar date across all three.
Nothing is compared yet — this step only confirms which rows are real data points and which one is a gap that needs to be excluded before any first/last comparison happens.

See the trap: LAST_VALUE without an explicit frame just returns the current row

query.sql

SELECT user_name, snapshot_date, portfolio_value,
       FIRST_VALUE(portfolio_value) OVER (PARTITION BY user_name ORDER BY snapshot_date) AS first_value,
       LAST_VALUE(portfolio_value) OVER (PARTITION BY user_name ORDER BY snapshot_date) AS last_value_bug
FROM portfolio_snapshots
WHERE portfolio_value IS NOT NULL
ORDER BY user_name, snapshot_date;

💡 Explanation

WHERE portfolio_value IS NOT NULL runs before the window functions ever see a row, so Aarav's failed-sync row is gone from this result entirely — filtering out a bad row is simpler than trying to make FIRST_VALUE or LAST_VALUE skip NULLs mid-calculation.
FIRST_VALUE(portfolio_value) OVER (PARTITION BY user_name ORDER BY snapshot_date) behaves exactly as expected: every row for a given user shows that same user's earliest portfolio_value, 100000 for every one of Aarav's rows.
LAST_VALUE looks like it should be the mirror image of FIRST_VALUE, but last_value_bug for every single row is identical to that row's own portfolio_value — Aarav's 2026-05-01 row reports a last_value_bug of 100000, not his actual latest value of 121000.
Neither FIRST_VALUE nor LAST_VALUE was given an explicit window frame here. SQL's default frame for an ORDER BY'd window is 'from the start of the partition up to the current row' — for FIRST_VALUE that default happens to give the right answer (the earliest row is always in range), but for LAST_VALUE it means 'the last row of whatever's been seen so far,' which is just the current row itself.

Fix LAST_VALUE with an explicit frame, then collapse to one row per user

query.sql

WITH filtered AS (
  SELECT user_name, snapshot_date, portfolio_value
  FROM portfolio_snapshots
  WHERE portfolio_value IS NOT NULL
),
bounds AS (
  SELECT user_name,
         FIRST_VALUE(portfolio_value) OVER (PARTITION BY user_name ORDER BY snapshot_date) AS first_value,
         LAST_VALUE(portfolio_value) OVER (
           PARTITION BY user_name ORDER BY snapshot_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS last_value
  FROM filtered
)
SELECT DISTINCT user_name,
       first_value,
       last_value,
       last_value - first_value AS growth_amount,
       ROUND((last_value - first_value) * 100.0 / first_value, 2) AS growth_pct
FROM bounds
ORDER BY growth_pct DESC;

💡 Explanation

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING widens the frame to the entire partition for every row, not just up to the current one — now LAST_VALUE genuinely means 'the last row of this whole user's data,' regardless of which row is currently being evaluated.
bounds still has one row per snapshot, with first_value and last_value repeated identically across every row for a user — SELECT DISTINCT in the outer query collapses that down to exactly one summary row per user_name, since first_value and last_value are now the same for all of a user's rows.
Karthik is the one case where growth is negative: his first_value of 75000 is higher than his last_value of 68000, and growth_amount correctly comes out as -7000 rather than something that needs a separate CASE WHEN to handle — subtraction already handles a decline the same way it handles growth.
growth_pct uses 100.0, not 100, as the multiplier — a plain 100 would force integer arithmetic in some engines and silently truncate the percentage; 100.0 keeps the whole expression in floating point so Karthik's -9.33% doesn't get rounded down to -9 or worse before ROUND ever runs.

▶ Practice this live ← Browse all questions