runtimetask Deduplication · hard
Retried authorization requests
Per merchant, return merchant_id, attempts (rows), requests (distinct request_key) and approved_amount: the sum of amount over requests whose final attempt (latest requested_at, then highest authorization_id) was approved, rounded to 2. Only merchants with at least one retried request.
what to return
- Columns
merchant_idattemptsrequestsapproved_amountin this order- Rows
- 208 rows · any order
- Skill
- Count requests, not retries. Refresher: Latest row per group →
expected result
Your query should return exactly these columns and values. Run it and compare.
| merchant_id | attempts | requests | approved_amount |
|---|---|---|---|
| 1 | 30 | 29 | 1543.58 |
| 10 | 32 | 31 | 1427.96 |
| 16 | 29 | 28 | 1288.58 |
first 3 of 208 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.
authorizations10 columns · 40,508 rows
| authorization_idnumber · unique id | request_keytext | card_idnumber · links to cards | merchant_idnumber · links to merchants | requested_atdate & time | amountdecimal | currencytext | channeltext | statustext | decline_reasontext |
|---|---|---|---|---|---|---|---|---|---|
| 1 | req_0000001 | 1 | 465 | 2025-01-06 18:49:43 | 100 | USD | in_store | declined | insufficient_funds |
| 2 | req_0000002 | 1 | 420 | 2025-01-08 20:45:55 | 6.9 | USD | in_store | declined | insufficient_funds |
| 3 | req_0000003 | 1 | 32 | 2025-01-21 15:28:53 | 76.55 | USD | in_store | approved | NULL |
| 4 | req_0000004 | 1 | 516 | 2025-01-27 11:09:51 | 175 | USD | in_store | approved | NULL |
| 5 | req_0000005 | 1 | 159 | 2025-01-29 15:06:21 | 57.71 | USD | in_store | approved | NULL |
| 6 | req_0000006 | 1 | 201 | 2025-02-09 23:15:34 | 37.17 | USD | online | approved | NULL |
- authorization_idnumber · unique id
- 1
- request_keytext
- req_0000001
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 465
- requested_atdate & time
- 2025-01-06 18:49:43
- amountdecimal
- 100
- currencytext
- USD
- channeltext
- in_store
- statustext
- declined
- decline_reasontext
- insufficient_funds
- authorization_idnumber · unique id
- 2
- request_keytext
- req_0000002
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 420
- requested_atdate & time
- 2025-01-08 20:45:55
- amountdecimal
- 6.9
- currencytext
- USD
- channeltext
- in_store
- statustext
- declined
- decline_reasontext
- insufficient_funds
- authorization_idnumber · unique id
- 3
- request_keytext
- req_0000003
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 32
- requested_atdate & time
- 2025-01-21 15:28:53
- amountdecimal
- 76.55
- currencytext
- USD
- channeltext
- in_store
- statustext
- approved
- decline_reasontext
- NULL
- authorization_idnumber · unique id
- 4
- request_keytext
- req_0000004
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 516
- requested_atdate & time
- 2025-01-27 11:09:51
- amountdecimal
- 175
- currencytext
- USD
- channeltext
- in_store
- statustext
- approved
- decline_reasontext
- NULL
- authorization_idnumber · unique id
- 5
- request_keytext
- req_0000005
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 159
- requested_atdate & time
- 2025-01-29 15:06:21
- amountdecimal
- 57.71
- currencytext
- USD
- channeltext
- in_store
- statustext
- approved
- decline_reasontext
- NULL
- authorization_idnumber · unique id
- 6
- request_keytext
- req_0000006
- card_idnumber · links to cards
- 1
- merchant_idnumber · links to merchants
- 201
- requested_atdate & time
- 2025-02-09 23:15:34
- amountdecimal
- 37.17
- currencytext
- USD
- channeltext
- online
- statustext
- approved
- decline_reasontext
- NULL
first 6 of 40,508 rows · your queries use all of them
Hints and the answer are always available. Using them counts the question as solved with help.