runtimetask Rolling windows & frames · hard
Predict: a frame that includes peers
Predict the running total ordered by posting date (not time) for account 1 in late January.
what to return
- Columns
transaction_idposted_dayamount_centsrunningin this order- Rows
- in the order asked
- Watch for
- Use only the sample rows shown. NULL is not zero or empty text. Keep duplicate rows.
- Skill
- Reason about the exact result of a fixed SQL query. Refresher: FIRST_VALUE, LAST_VALUE & frames →
data you can query
Finance / Payments dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
posting_sample3 columns · 5 rows
| transaction_id | posted_daydate | amount_cents |
|---|---|---|
| 8149 | 2025-01-23 | -7655 |
| 9015 | 2025-01-24 | 155222 |
| 9273 | 2025-01-24 | -8599 |
| 11396 | 2025-01-30 | -6852 |
| 12203 | 2025-01-30 | -5771 |
- transaction_id
- 8149
- posted_daydate
- 2025-01-23
- amount_cents
- -7655
- transaction_id
- 9015
- posted_daydate
- 2025-01-24
- amount_cents
- 155222
- transaction_id
- 9273
- posted_daydate
- 2025-01-24
- amount_cents
- -8599
- transaction_id
- 11396
- posted_daydate
- 2025-01-30
- amount_cents
- -6852
- transaction_id
- 12203
- posted_daydate
- 2025-01-30
- amount_cents
- -5771
all 5 rows
SELECT transaction_id, posted_day, amount_cents, SUM(amount_cents) OVER (ORDER BY posted_day) AS running FROM posting_sample ORDER BY posted_day, transaction_id
SELECT transaction_id, posted_day, amount_cents, SUM(amount_cents) OVER (ORDER BY posted_day) AS running FROM posting_sample ORDER BY posted_day, transaction_id
Look at the sample tables and SQL. Enter the rows you think the query returns, in this column order: transaction_id, posted_day, amount_cents, running. Put each row in square brackets, then wrap all rows in another pair of brackets (JSON format). For example, [[1,null,null,null]] is one row with 4 values. Replace these with your predicted values. Use numbers without quotes, text in double quotes, and null for SQL NULL. Keep repeated rows. Use [] for no rows. Keep rows in the order specified by ORDER BY.
Hints and the answer are always available. Using them counts the question as solved with help.