runtimetask Reconciliation & quality gates · hard
Settlements, refunds and disputes
For settlements in Q1 2025 with any refund or credited chargeback, return settlement_id, settled_cents, refunded_cents, disputed_cents (credited chargebacks) and status: 'double_recovery' when refunds plus credited disputes exceed the settlement, 'fully_recovered' when they equal it, otherwise 'partially_recovered'.
what to return
- Columns
settlement_idsettled_centsrefunded_centsdisputed_centsstatusin this order- Rows
- 523 rows · any order
- Skill
- Reconcile one amount against two kinds of recovery. Refresher: Revenue & payment reconciliation →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result523 rows · any order
| settlement_id | settled_cents | refunded_cents | disputed_cents | status |
|---|---|---|---|---|
| 18689 | 4372 | 4372 | 0 | fully_recovered |
| 7857 | 899 | 271 | 0 | partially_recovered |
| 34657 | 2499 | 0 | 2499 | fully_recovered |
first 3 of 523 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.
settlements4 columns · 37,071 rows
| settlement_idnumber · unique id | authorization_idnumber · links to authorizations | settled_atdate & time | amount_centsnumber |
|---|---|---|---|
| 1 | 3 | 2025-01-23 01:12:53 | 7655 |
| 2 | 4 | 2025-01-30 13:04:51 | 6852 |
| 3 | 5 | 2025-01-30 17:41:21 | 5771 |
| 4 | 6 | 2025-02-11 06:57:34 | 3717 |
| 5 | 7 | 2025-02-11 19:25:53 | 4401 |
| 6 | 8 | 2025-02-20 03:08:18 | 9288 |
- settlement_idnumber · unique id
- 1
- authorization_idnumber · links to authorizations
- 3
- settled_atdate & time
- 2025-01-23 01:12:53
- amount_centsnumber
- 7655
- settlement_idnumber · unique id
- 2
- authorization_idnumber · links to authorizations
- 4
- settled_atdate & time
- 2025-01-30 13:04:51
- amount_centsnumber
- 6852
- settlement_idnumber · unique id
- 3
- authorization_idnumber · links to authorizations
- 5
- settled_atdate & time
- 2025-01-30 17:41:21
- amount_centsnumber
- 5771
- settlement_idnumber · unique id
- 4
- authorization_idnumber · links to authorizations
- 6
- settled_atdate & time
- 2025-02-11 06:57:34
- amount_centsnumber
- 3717
- settlement_idnumber · unique id
- 5
- authorization_idnumber · links to authorizations
- 7
- settled_atdate & time
- 2025-02-11 19:25:53
- amount_centsnumber
- 4401
- settlement_idnumber · unique id
- 6
- authorization_idnumber · links to authorizations
- 8
- settled_atdate & time
- 2025-02-20 03:08:18
- amount_centsnumber
- 9288
first 6 of 37,071 rows · your queries use all of them
refunds5 columns · 1,230 rows
| refund_idnumber · unique id | settlement_idnumber · links to settlements | refunded_atdate & time | amount_centsnumber | reasontext |
|---|---|---|---|---|
| 1 | 25 | 2025-02-12 02:56:32 | 2820 | returned_item |
| 2 | 27 | 2025-03-01 03:02:52 | 11883 | returned_item |
| 3 | 268 | 2025-03-01 07:28:00 | 367 | canceled_service |
| 4 | 313 | 2025-05-30 14:18:37 | 1601 | returned_item |
| 5 | 313 | 2025-06-15 14:18:37 | 2440 | canceled_service |
| 6 | 348 | 2025-05-11 19:21:17 | 19843 | price_adjustment |
- refund_idnumber · unique id
- 1
- settlement_idnumber · links to settlements
- 25
- refunded_atdate & time
- 2025-02-12 02:56:32
- amount_centsnumber
- 2820
- reasontext
- returned_item
- refund_idnumber · unique id
- 2
- settlement_idnumber · links to settlements
- 27
- refunded_atdate & time
- 2025-03-01 03:02:52
- amount_centsnumber
- 11883
- reasontext
- returned_item
- refund_idnumber · unique id
- 3
- settlement_idnumber · links to settlements
- 268
- refunded_atdate & time
- 2025-03-01 07:28:00
- amount_centsnumber
- 367
- reasontext
- canceled_service
- refund_idnumber · unique id
- 4
- settlement_idnumber · links to settlements
- 313
- refunded_atdate & time
- 2025-05-30 14:18:37
- amount_centsnumber
- 1601
- reasontext
- returned_item
- refund_idnumber · unique id
- 5
- settlement_idnumber · links to settlements
- 313
- refunded_atdate & time
- 2025-06-15 14:18:37
- amount_centsnumber
- 2440
- reasontext
- canceled_service
- refund_idnumber · unique id
- 6
- settlement_idnumber · links to settlements
- 348
- refunded_atdate & time
- 2025-05-11 19:21:17
- amount_centsnumber
- 19843
- reasontext
- price_adjustment
first 6 of 1,230 rows · your queries use all of them
chargebacks7 columns · 199 rows
| chargeback_idnumber · unique id | settlement_idnumber · links to settlements | opened_atdate & time | amount_centsnumber | reason_codetext | statustext | resolved_atdate & time |
|---|---|---|---|---|---|---|
| 1 | 55 | 2025-01-27 13:07:58 | 2791 | not_received | open | NULL |
| 2 | 84 | 2025-03-30 16:16:40 | 2854 | not_as_described | credited | 2025-04-21 16:16:40 |
| 3 | 274 | 2025-03-25 12:37:00 | 1549 | duplicate | rejected | 2025-04-17 12:37:00 |
| 4 | 391 | 2025-03-05 04:12:00 | 2499 | not_received | rejected | 2025-04-19 04:12:00 |
| 5 | 584 | 2025-03-22 18:26:08 | 3761 | not_received | credited | 2025-04-27 18:26:08 |
| 6 | 1029 | 2025-06-15 18:35:43 | 5569 | not_received | open | NULL |
- chargeback_idnumber · unique id
- 1
- settlement_idnumber · links to settlements
- 55
- opened_atdate & time
- 2025-01-27 13:07:58
- amount_centsnumber
- 2791
- reason_codetext
- not_received
- statustext
- open
- resolved_atdate & time
- NULL
- chargeback_idnumber · unique id
- 2
- settlement_idnumber · links to settlements
- 84
- opened_atdate & time
- 2025-03-30 16:16:40
- amount_centsnumber
- 2854
- reason_codetext
- not_as_described
- statustext
- credited
- resolved_atdate & time
- 2025-04-21 16:16:40
- chargeback_idnumber · unique id
- 3
- settlement_idnumber · links to settlements
- 274
- opened_atdate & time
- 2025-03-25 12:37:00
- amount_centsnumber
- 1549
- reason_codetext
- duplicate
- statustext
- rejected
- resolved_atdate & time
- 2025-04-17 12:37:00
- chargeback_idnumber · unique id
- 4
- settlement_idnumber · links to settlements
- 391
- opened_atdate & time
- 2025-03-05 04:12:00
- amount_centsnumber
- 2499
- reason_codetext
- not_received
- statustext
- rejected
- resolved_atdate & time
- 2025-04-19 04:12:00
- chargeback_idnumber · unique id
- 5
- settlement_idnumber · links to settlements
- 584
- opened_atdate & time
- 2025-03-22 18:26:08
- amount_centsnumber
- 3761
- reason_codetext
- not_received
- statustext
- credited
- resolved_atdate & time
- 2025-04-27 18:26:08
- chargeback_idnumber · unique id
- 6
- settlement_idnumber · links to settlements
- 1029
- opened_atdate & time
- 2025-06-15 18:35:43
- amount_centsnumber
- 5569
- reason_codetext
- not_received
- statustext
- open
- resolved_atdate & time
- NULL
first 6 of 199 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.