runtimetask Reconciliation & quality gates · hard
What actually moved money?
For accounts 1–100, return account_id, closing_cents (opening balance plus posted entries), pending_cents (sum of pending entries) and reversed_cents (sum of reversed entries), using 0 when none.
what to return
- Columns
account_idclosing_centspending_centsreversed_centsin this order- Rows
- 100 rows · any order
- Skill
- Distinguish money movement from pending and reversed items. Refresher: Revenue & payment reconciliation →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result100 rows · any order
| account_id | closing_cents | pending_cents | reversed_cents |
|---|---|---|---|
| 1 | 1092869 | 0 | 0 |
| 2 | 721985 | -2914 | 0 |
| 3 | 3641184 | 0 | 0 |
first 3 of 100 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.
accounts7 columns · 2,109 rows
| account_idnumber · unique id | customer_idnumber · links to customers | account_typetext | opened_atdate | closed_attext | credit_limit_centsnumber | opening_balance_centsnumber |
|---|---|---|---|---|---|---|
| 1 | 1 | checking | 2022-02-24 | NULL | NULL | 43125 |
| 2 | 2 | checking | 2025-06-11 | NULL | NULL | 0 |
| 3 | 3 | checking | 2024-07-07 | NULL | NULL | 216925 |
| 4 | 3 | savings | 2024-10-07 | NULL | NULL | 475941 |
| 5 | 3 | credit_card | 2024-09-26 | NULL | 1020600 | -339148 |
| 6 | 4 | checking | 2023-07-18 | NULL | NULL | 155995 |
- account_idnumber · unique id
- 1
- customer_idnumber · links to customers
- 1
- account_typetext
- checking
- opened_atdate
- 2022-02-24
- closed_attext
- NULL
- credit_limit_centsnumber
- NULL
- opening_balance_centsnumber
- 43125
- account_idnumber · unique id
- 2
- customer_idnumber · links to customers
- 2
- account_typetext
- checking
- opened_atdate
- 2025-06-11
- closed_attext
- NULL
- credit_limit_centsnumber
- NULL
- opening_balance_centsnumber
- 0
- account_idnumber · unique id
- 3
- customer_idnumber · links to customers
- 3
- account_typetext
- checking
- opened_atdate
- 2024-07-07
- closed_attext
- NULL
- credit_limit_centsnumber
- NULL
- opening_balance_centsnumber
- 216925
- account_idnumber · unique id
- 4
- customer_idnumber · links to customers
- 3
- account_typetext
- savings
- opened_atdate
- 2024-10-07
- closed_attext
- NULL
- credit_limit_centsnumber
- NULL
- opening_balance_centsnumber
- 475941
- account_idnumber · unique id
- 5
- customer_idnumber · links to customers
- 3
- account_typetext
- credit_card
- opened_atdate
- 2024-09-26
- closed_attext
- NULL
- credit_limit_centsnumber
- 1020600
- opening_balance_centsnumber
- -339148
- account_idnumber · unique id
- 6
- customer_idnumber · links to customers
- 4
- account_typetext
- checking
- opened_atdate
- 2023-07-18
- closed_attext
- NULL
- credit_limit_centsnumber
- NULL
- opening_balance_centsnumber
- 155995
first 6 of 2,109 rows · your queries use all of them
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.