runtimetask Correlated subqueries · medium
Purchases far above a card’s norm
Return authorization_id, card_id, amount and card_avg (the card’s average approved amount, 2 decimals) for approved authorizations more than three times their card’s average approved amount.
what to return
- Columns
authorization_idcard_idamountcard_avgin this order- Rows
- 2226 rows · any order
- Skill
- Flag outliers relative to each entity’s own baseline. Refresher: Subqueries →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result2226 rows · any order
| authorization_id | card_id | amount | card_avg |
|---|---|---|---|
| 49 | 3 | 201.34 | 66.07 |
| 38 | 4 | 175 | 44.37 |
| 66 | 5 | 342.83 | 58.78 |
first 3 of 2226 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
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.