runtimetask Validation & types · hard
Parse statement amounts into cents
statement_import holds amounts as text: '$1,234.56', '-12.30', '(12.30)' for negatives, ' 12.30 ', '12.3' and 'USD 12.30'. Return line_id and amount_cents as an exact integer for every line.
what to return
- Columns
line_idamount_centsin this order- Rows
- 1386 rows · any order
- Skill
- Convert formatted money text to exact integer cents. Refresher: CAST, text types & NULL arithmetic →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result1386 rows · any order
| line_id | amount_cents |
|---|---|
| 43 | -104520 |
| 44 | -174395 |
| 45 | -127618 |
first 3 of 1386 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.
statement_import3 columns · 1,386 rows
| line_idnumber · unique id | account_idnumber | amount_texttext |
|---|---|---|
| 43 | 1 | -1045.20 |
| 44 | 9 | (1743.95) |
| 45 | 10 | -1276.18 |
| 46 | 12 | -498.6 |
| 47 | 19 | USD -854.51 |
| 48 | 21 | $-1,911.43 |
- line_idnumber · unique id
- 43
- account_idnumber
- 1
- amount_texttext
- -1045.20
- line_idnumber · unique id
- 44
- account_idnumber
- 9
- amount_texttext
- (1743.95)
- line_idnumber · unique id
- 45
- account_idnumber
- 10
- amount_texttext
- -1276.18
- line_idnumber · unique id
- 46
- account_idnumber
- 12
- amount_texttext
- -498.6
- line_idnumber · unique id
- 47
- account_idnumber
- 19
- amount_texttext
- USD -854.51
- line_idnumber · unique id
- 48
- account_idnumber
- 21
- amount_texttext
- $-1,911.43
first 6 of 1,386 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.