runtimetask Correlated subqueries · medium
Big spenders for their state
Using settled card amounts, return customer_id, state and settled_cents for customers whose total settled card spend is above the average total of customers in the same state (only customers with any settled spend count toward that average).
what to return
- Columns
customer_idstatesettled_centsin this order- Rows
- 355 rows · any order
- Skill
- Compare each row with an average of its own group. Refresher: Subqueries →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result355 rows · any order
| customer_id | state | settled_cents |
|---|---|---|
| 3 | AZ | 131000 |
| 4 | IL | 715055 |
| 11 | NC | 184149 |
first 3 of 355 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.
customers7 columns · 1,120 rows
| customer_idnumber · unique id | full_nametext | citytext | statetext | date_of_birthdate | customer_sincedate | risk_tiertext |
|---|---|---|---|---|---|---|
| 1 | Tara Kowalski | Newark | NJ | 1957-10-31 | 2022-02-24 | low |
| 2 | Anna Okafor | Detroit | MI | 1985-03-01 | 2025-06-11 | medium |
| 3 | Jasmine Chang | Phoenix | AZ | 2005-12-14 | 2024-07-07 | medium |
| 4 | Victor Yamamoto | Chicago | IL | 1996-06-22 | 2023-07-18 | low |
| 5 | Ryan Fernandez | Minneapolis | MN | 1965-07-23 | 2018-02-14 | low |
| 6 | Leah Russell | Seattle | WA | 1965-01-02 | 2024-08-16 | low |
- customer_idnumber · unique id
- 1
- full_nametext
- Tara Kowalski
- citytext
- Newark
- statetext
- NJ
- date_of_birthdate
- 1957-10-31
- customer_sincedate
- 2022-02-24
- risk_tiertext
- low
- customer_idnumber · unique id
- 2
- full_nametext
- Anna Okafor
- citytext
- Detroit
- statetext
- MI
- date_of_birthdate
- 1985-03-01
- customer_sincedate
- 2025-06-11
- risk_tiertext
- medium
- customer_idnumber · unique id
- 3
- full_nametext
- Jasmine Chang
- citytext
- Phoenix
- statetext
- AZ
- date_of_birthdate
- 2005-12-14
- customer_sincedate
- 2024-07-07
- risk_tiertext
- medium
- customer_idnumber · unique id
- 4
- full_nametext
- Victor Yamamoto
- citytext
- Chicago
- statetext
- IL
- date_of_birthdate
- 1996-06-22
- customer_sincedate
- 2023-07-18
- risk_tiertext
- low
- customer_idnumber · unique id
- 5
- full_nametext
- Ryan Fernandez
- citytext
- Minneapolis
- statetext
- MN
- date_of_birthdate
- 1965-07-23
- customer_sincedate
- 2018-02-14
- risk_tiertext
- low
- customer_idnumber · unique id
- 6
- full_nametext
- Leah Russell
- citytext
- Seattle
- statetext
- WA
- date_of_birthdate
- 1965-01-02
- customer_sincedate
- 2024-08-16
- risk_tiertext
- low
first 6 of 1,120 rows · your queries use all of them
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
cards8 columns · 1,774 rows
| card_idnumber · unique id | account_idnumber · links to accounts | card_typetext | networktext | last4text | issued_atdate | expires_ondate | statustext |
|---|---|---|---|---|---|---|---|
| 1 | 1 | debit | visa | 1995 | 2024-09-06 | 2028-09-05 | active |
| 2 | 2 | debit | visa | 3252 | 2025-06-11 | 2029-06-10 | active |
| 3 | 3 | debit | visa | 1241 | 2024-07-13 | 2028-07-12 | active |
| 4 | 5 | credit | visa | 3044 | 2024-09-26 | 2028-09-25 | active |
| 5 | 6 | debit | mastercard | 9945 | 2023-07-18 | 2027-07-17 | active |
| 6 | 8 | debit | visa | 7443 | 2023-08-23 | 2027-08-22 | active |
- card_idnumber · unique id
- 1
- account_idnumber · links to accounts
- 1
- card_typetext
- debit
- networktext
- visa
- last4text
- 1995
- issued_atdate
- 2024-09-06
- expires_ondate
- 2028-09-05
- statustext
- active
- card_idnumber · unique id
- 2
- account_idnumber · links to accounts
- 2
- card_typetext
- debit
- networktext
- visa
- last4text
- 3252
- issued_atdate
- 2025-06-11
- expires_ondate
- 2029-06-10
- statustext
- active
- card_idnumber · unique id
- 3
- account_idnumber · links to accounts
- 3
- card_typetext
- debit
- networktext
- visa
- last4text
- 1241
- issued_atdate
- 2024-07-13
- expires_ondate
- 2028-07-12
- statustext
- active
- card_idnumber · unique id
- 4
- account_idnumber · links to accounts
- 5
- card_typetext
- credit
- networktext
- visa
- last4text
- 3044
- issued_atdate
- 2024-09-26
- expires_ondate
- 2028-09-25
- statustext
- active
- card_idnumber · unique id
- 5
- account_idnumber · links to accounts
- 6
- card_typetext
- debit
- networktext
- mastercard
- last4text
- 9945
- issued_atdate
- 2023-07-18
- expires_ondate
- 2027-07-17
- statustext
- active
- card_idnumber · unique id
- 6
- account_idnumber · links to accounts
- 8
- card_typetext
- debit
- networktext
- visa
- last4text
- 7443
- issued_atdate
- 2023-08-23
- expires_ondate
- 2027-08-22
- statustext
- active
first 6 of 1,774 rows · your queries use all of them
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
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
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.