Given an orders table, compute a trailing 95th-percentile of order value for every row.
Data
Input schema
orders(
order_id INTEGER PRIMARY KEY,
user_id INTEGER,
value DOUBLE PRECISION CHECK (value >= 0),
created_at TIMESTAMP
)
Task
For each row, compute the 95th percentile of value over the trailing 1000-row window ordered by order_id: the current row plus the 999 rows immediately preceding it (fewer than 1000 when not enough rows exist yet — e.g. row 1 has a window of 1, row 500 a window of 500). Use the continuous percentile PERCENTILE_CONT(0.95) (linear interpolation between ranks, not nearest-rank). Round the result to 2 decimal places.
Output schema
result(order_id, value, p95_trailing_1000) -- one row per input row, ORDER BY order_id ASC
Worked example (illustrative — window = 3)
This example uses a window of 3 (current row + 2 preceding) over 6 rows so you can verify it by hand. The graded dataset uses the full 1000-row trailing window.
Input orders Expected output (PERCENTILE_CONT(0.95), rounded 2 dp)
order_id | value order_id | value | p95_trailing_1000
---------+------ ---------+-------+------------------
1 | 10 1 | 10 | 10.00
2 | 20 2 | 20 | 19.50
3 | 30 3 | 30 | 29.00
4 | 100 4 | 100 | 93.00
5 | 40 5 | 40 | 94.00
6 | 50 6 | 50 | 95.00
For example, row 4's window is the 3 trailing values [20, 30, 100]; PERCENTILE_CONT(0.95) over those sorted values interpolates to 30 + 0.9 × (100 − 30) = 93.00.