runtimetask Rolling windows & frames · hard
Debug: each account’s last posted amount
For posted entries on accounts 1–30, return transaction_id, account_id and last_amount: the account’s final posted amount (by posted_at, transaction_id). The query returns each row’s own amount. Fix it.
what to return
- Columns
transaction_idaccount_idlast_amountin this order- Rows
- 1067 rows · any order
- Skill
- Repair the query’s logic and confirm the fix on changed data. Refresher: FIRST_VALUE, LAST_VALUE & frames →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result1067 rows · any order
| transaction_id | account_id | last_amount |
|---|---|---|
| 43 | 1 | -6599 |
| 3597 | 1 | -6599 |
| 7099 | 1 | -6599 |
first 3 of 1067 rows
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.
transactions12 columns · 84,798 rows
| transaction_idnumber · unique id | account_idnumber · links to accounts | transaction_typetext | statustext | amount_centsnumber | initiated_atdate & time | posted_atdate & time | descriptiontext | merchant_idnumber · links to merchants | authorization_idnumber · links to authorizations | refund_idnumber · links to refunds | chargeback_idnumber · links to chargebacks |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 103 | card_purchase | posted | -1399 | 2025-01-01 04:00:00 | 2025-01-02 06:23:00 | TRUE STREAMING #711 | 959 | 1980 | NULL | NULL |
| 2 | 377 | card_purchase | posted | -3999 | 2025-01-01 04:00:00 | 2025-01-03 10:26:00 | VISTA GYM #196 | 965 | 6710 | NULL | NULL |
| 3 | 442 | card_purchase | posted | -699 | 2025-01-01 04:00:00 | 2025-01-03 04:21:00 | WILLOW STREAMING #817 | 955 | 7848 | NULL | NULL |
| 4 | 447 | card_purchase | posted | -4999 | 2025-01-01 04:00:00 | 2025-01-02 12:31:00 | OAK GYM #964 | 969 | 7931 | NULL | NULL |
| 5 | 450 | card_purchase | posted | -2499 | 2025-01-01 04:00:00 | 2025-01-02 08:01:00 | BIRCH GYM #498 | 968 | 8096 | NULL | NULL |
| 6 | 497 | card_purchase | posted | -899 | 2025-01-01 04:00:00 | 2025-01-02 12:11:00 | VERDE STREAMING #385 | 958 | 8870 | NULL | NULL |
- transaction_idnumber · unique id
- 1
- account_idnumber · links to accounts
- 103
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -1399
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-02 06:23:00
- descriptiontext
- TRUE STREAMING #711
- merchant_idnumber · links to merchants
- 959
- authorization_idnumber · links to authorizations
- 1980
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
- transaction_idnumber · unique id
- 2
- account_idnumber · links to accounts
- 377
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -3999
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-03 10:26:00
- descriptiontext
- VISTA GYM #196
- merchant_idnumber · links to merchants
- 965
- authorization_idnumber · links to authorizations
- 6710
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
- transaction_idnumber · unique id
- 3
- account_idnumber · links to accounts
- 442
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -699
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-03 04:21:00
- descriptiontext
- WILLOW STREAMING #817
- merchant_idnumber · links to merchants
- 955
- authorization_idnumber · links to authorizations
- 7848
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
- transaction_idnumber · unique id
- 4
- account_idnumber · links to accounts
- 447
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -4999
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-02 12:31:00
- descriptiontext
- OAK GYM #964
- merchant_idnumber · links to merchants
- 969
- authorization_idnumber · links to authorizations
- 7931
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
- transaction_idnumber · unique id
- 5
- account_idnumber · links to accounts
- 450
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -2499
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-02 08:01:00
- descriptiontext
- BIRCH GYM #498
- merchant_idnumber · links to merchants
- 968
- authorization_idnumber · links to authorizations
- 8096
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
- transaction_idnumber · unique id
- 6
- account_idnumber · links to accounts
- 497
- transaction_typetext
- card_purchase
- statustext
- posted
- amount_centsnumber
- -899
- initiated_atdate & time
- 2025-01-01 04:00:00
- posted_atdate & time
- 2025-01-02 12:11:00
- descriptiontext
- VERDE STREAMING #385
- merchant_idnumber · links to merchants
- 958
- authorization_idnumber · links to authorizations
- 8870
- refund_idnumber · links to refunds
- NULL
- chargeback_idnumber · links to chargebacks
- NULL
first 6 of 84,798 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.