1e:I[344796,["/_next/static/chunks/0whdtqcc5h8md.js","/_next/static/chunks/1qo17jq3o72yb.js","/_next/static/chunks/00mfjk9z2ewie.js","/_next/static/chunks/3_0kqrj9zuodm.js","/_next/static/chunks/2q9x6spyz4qxv.js","/_next/static/chunks/2cxhk-z7t3ztw.js","/_next/static/chunks/35kg0tmkl08ow.js","/_next/static/chunks/09hukzrse-ioj.js","/_next/static/chunks/3-qfh2-yexhl7.js","/_next/static/chunks/0_-iwh06bu1or.js"],"default"] 1f:T142b,

Problem Description

A financial institution is building an online application to simplify tax calculations. Write a SQL query to calculate a fixed 20% tax on the total income generated by each account during the year 2023.

Schema

Table: accounts
+-----------+--------------+-------------+-------------------------------+
| Name      | Type         | Constraints | Description                   |
+-----------+--------------+-------------+-------------------------------+
| id        | INT          | PRIMARY KEY | The identifier of the account |
| iban      | VARCHAR(255) |             | The IBAN of the account       |
+-----------+--------------+-------------+-------------------------------+

Table: income
+------------+--------------+------------------+------------------------------+
| Name       | Type         | Constraints      | Description                  |
+------------+--------------+------------------+------------------------------+
| account_id | INT          | FK (accounts.id) | The reference to the account |
| dt         | VARCHAR(19)  |                  | Date and time of the income  |
| amount     | DECIMAL(6,2) |                  | The income amount            |
+------------+--------------+------------------+------------------------------+

Required Output

The query should return a result set with the following columns in the exact order below:

iban: The IBAN of the account.
total_income: The total income for the account in 2023, mathematically rounded to exactly two decimal places (e.g., 500.00).
tax_rate: A fixed text column containing exactly '20%'.
calculated_tax: The calculated tax (20% of total_income), mathematically rounded to exactly two decimal places (e.g., 500.00).

Order the final results in ascending order by iban.

Constraints or Notes

Evaluate only income records where the year is 2023.
Accounts with no income in 2023 should be completely excluded from the final results.
The dt column is formatted consistently as YYYY-MM-DD HH:MM:SS but is strictly stored as a VARCHAR(19).

Examples

Example 1

Input:

Table: accounts
+----+------------------------------------+
| id | iban                               |
+----+------------------------------------+
| 1  | FR55 4477 6154 73ND TN3F HMOU T36  |
| 2  | DK46 1272 1831 2573 01             |
| 3  | RS53 5237 5794 6016 5411 43        |
+----+------------------------------------+ 

Table: income
+------------+---------------------+----------+
| account_id | dt                  | amount   |
+------------+---------------------+----------+
| 1          | 2022-12-31 10:03:42 | 2779.19  |
| 1          | 2023-02-04 08:50:14 | 1777.68  |
| 1          | 2023-02-13 04:22:07 | 1954.81  |
| 1          | 2023-03-04 14:46:04 | 1547.79  |
| 1          | 2023-05-23 15:42:13 | 1208.49  |
| 1          | 2023-05-24 23:24:07 | 1521.72  |
| 1          | 2023-07-28 11:01:46 | 1792.75  |
| 1          | 2023-12-07 14:19:09 | 2374.25  |
| 1          | 2024-01-27 05:55:36 | 2803.39  |
| 2          | 2022-12-03 18:04:34 | 1826.65  |
| 2          | 2023-02-17 00:59:57 | 3074.11  |
| 2          | 2023-03-01 08:17:15 | 1007.30  |
| 2          | 2023-08-19 09:16:41 | 4515.04  |
| 2          | 2024-01-08 04:14:22 | 3321.78  |
| 2          | 2024-01-10 15:16:28 | 2033.87  |
| 3          | 2023-05-09 07:28:27 | 3158.66  |
| 3          | 2023-05-22 04:39:34 | 3851.20  |
| 3          | 2023-07-21 19:51:14 | 4152.29  |
| 3          | 2023-10-05 05:42:49 | 4722.20  |
| 3          | 2023-11-11 02:42:59 | 1592.16  |
+------------+---------------------+----------+

Output:

+------------------------------------+--------------+----------+----------------+
| iban                               | total_income | tax_rate | calculated_tax |
+------------------------------------+--------------+----------+----------------+
| DK46 1272 1831 2573 01             | 8596.45      | 20%      | 1719.29        |
| FR55 4477 6154 73ND TN3F HMOU T36  | 12177.49     | 20%      | 2435.50        |
| RS53 5237 5794 6016 5411 43        | 17476.51     | 20%      | 3495.30        |
+------------------------------------+--------------+----------+----------------+

Assumptions Added

SQL Dialect: Standard SQL.
Formatting: To ensure trailing zeros are preserved (e.g., 2435.50), assume the testing environment correctly preserves precision when returning a DECIMAL(10,2) or NUMERIC(10,2) type. You may explicitly cast your numeric outputs to achieve this format.

20:T79b,["CREATE TABLE accounts (id INT PRIMARY KEY, iban VARCHAR(255));\nCREATE TABLE income (account_id INT, dt VARCHAR(19), amount DECIMAL(6,2));\nINSERT INTO accounts VALUES (1, 'FR55 4477 6154 73ND TN3F HMOU T36');\nINSERT INTO accounts VALUES (2, 'DK46 1272 1831 2573 01');\nINSERT INTO accounts VALUES (3, 'RS53 5237 5794 6016 5411 43');\nINSERT INTO income VALUES (1, '2022-12-31 10:03:42', 2779.19);\nINSERT INTO income VALUES (1, '2023-02-04 08:50:14', 1777.68);\nINSERT INTO income VALUES (1, '2023-02-13 04:22:07', 1954.81);\nINSERT INTO income VALUES (1, '2023-03-04 14:46:04', 1547.79);\nINSERT INTO income VALUES (1, '2023-05-23 15:42:13', 1208.49);\nINSERT INTO income VALUES (1, '2023-05-24 23:24:07', 1521.72);\nINSERT INTO income VALUES (1, '2023-07-28 11:01:46', 1792.75);\nINSERT INTO income VALUES (1, '2023-12-07 14:19:09', 2374.25);\nINSERT INTO income VALUES (1, '2024-01-27 05:55:36', 2803.39);\nINSERT INTO income VALUES (2, '2022-12-03 18:04:34', 1826.65);\nINSERT INTO income VALUES (2, '2023-02-17 00:59:57', 3074.11);\nINSERT INTO income VALUES (2, '2023-03-01 08:17:15', 1007.30);\nINSERT INTO income VALUES (2, '2023-08-19 09:16:41', 4515.04);\nINSERT INTO income VALUES (2, '2024-01-08 04:14:22', 3321.78);\nINSERT INTO income VALUES (2, '2024-01-10 15:16:28', 2033.87);\nINSERT INTO income VALUES (3, '2023-05-09 07:28:27', 3158.66);\nINSERT INTO income VALUES (3, '2023-05-22 04:39:34', 3851.20);\nINSERT INTO income VALUES (3, '2023-07-21 19:51:14', 4152.29);\nINSERT INTO income VALUES (3, '2023-10-05 05:42:49', 4722.20);\nINSERT INTO income VALUES (3, '2023-11-11 02:42:59', 1592.16);", "CREATE TABLE accounts (id INT PRIMARY KEY, iban VARCHAR(255));\nCREATE TABLE income (account_id INT, dt VARCHAR(19), amount DECIMAL(6,2));\nINSERT INTO accounts VALUES (10, 'NL00BANK0123456789');\nINSERT INTO income VALUES (10, '2022-12-31 23:59:59', 1000.00);\nINSERT INTO income VALUES (10, '2024-01-01 00:00:00', 1000.00);"]21:Tffa,# Calculate Account Tax from 2023 Income ## Problem Description A financial institution is building an online application to simplify tax calculations. Write a SQL query to calculate a fixed 20% tax on the total income generated by each account during the year 2023. ## Schema ### Table: `accounts` | Name | Type | Constraints | Description | |------|---------------|-------------|--------------------------------| | id | INT | PRIMARY KEY | The identifier of the account | | iban | VARCHAR(255) | | The IBAN of the account | ### Table: `income` | Name | Type | Constraints | Description | |------------|---------------|----------------------|--------------------------------------| | account_id | INT | FK (accounts.id) | The reference to the account | | dt | VARCHAR(19) | | Date and time of the income | | amount | DECIMAL(6,2) | | The income amount | ## Required Output The query should return a result set with the following columns in the exact order below: - `iban`: The IBAN of the account. - `total_income`: The total income for the account in 2023, mathematically rounded to exactly two decimal places (e.g., 500.00). - `tax_rate`: A fixed text column containing exactly `'20%'`. - `calculated_tax`: The calculated tax (20% of `total_income`), mathematically rounded to exactly two decimal places (e.g., 500.00). Order the final results in ascending order by `iban`. ## Constraints or Notes - Evaluate only income records where the year is 2023. - Accounts with no income in 2023 should be completely excluded from the final results. - The `dt` column is formatted consistently as `YYYY-MM-DD HH:MM:SS` but is strictly stored as a `VARCHAR(19)`. - Tax rate is fixed at 20%. - Numeric results (`total_income` and `calculated_tax`) should be rounded to exactly two decimal places, preserving trailing zeros (e.g., 2435.50). ## Examples ### Example 1 **Input:** Table: `accounts` | id | iban | |----|-------------------------------------------| | 1 | FR55 4477 6154 73ND TN3F HMOU T36 | | 2 | DK46 1272 1831 2573 01 | | 3 | RS53 5237 5794 6016 5411 43 | Table: `income` | account_id | dt | amount | |------------|----------------------|---------| | 1 | 2022-12-31 10:03:42 | 2779.19 | | 1 | 2023-02-04 08:50:14 | 1777.68 | | 1 | 2023-02-13 04:22:07 | 1954.81 | | 1 | 2023-03-04 14:46:04 | 1547.79 | | 1 | 2023-05-23 15:42:13 | 1208.49 | | 1 | 2023-05-24 23:24:07 | 1521.72 | | 1 | 2023-07-28 11:01:46 | 1792.75 | | 1 | 2023-12-07 14:19:09 | 2374.25 | | 1 | 2024-01-27 05:55:36 | 2803.39 | | 2 | 2022-12-03 18:04:34 | 1826.65 | | 2 | 2023-02-17 00:59:57 | 3074.11 | | 2 | 2023-03-01 08:17:15 | 1007.30 | | 2 | 2023-08-19 09:16:41 | 4515.04 | | 2 | 2024-01-08 04:14:22 | 3321.78 | | 2 | 2024-01-10 15:16:28 | 2033.87 | | 3 | 2023-05-09 07:28:27 | 3158.66 | | 3 | 2023-05-22 04:39:34 | 3851.20 | | 3 | 2023-07-21 19:51:14 | 4152.29 | | 3 | 2023-10-05 05:42:49 | 4722.20 | | 3 | 2023-11-11 02:42:59 | 1592.16 | **Output:** | iban | total_income | tax_rate | calculated_tax | |-------------------------------------------|--------------|----------|----------------| | DK46 1272 1831 2573 01 | 8596.45 | 20% | 1719.29 | | FR55 4477 6154 73ND TN3F HMOU T36 | 12177.49 | 20% | 2435.50 | | RS53 5237 5794 6016 5411 43 | 17476.51 | 20% | 3495.30 | **Explanation:** Only rows dated in 2023 are summed. The 2022 and 2024 rows are excluded. The calculated tax is 20% of each account's 2023 total, and the rows are ordered by IBAN.16:[["$","script",null,{"type":"application/ld+json","dangerouslySetInnerHTML":{"__html":"{\"@context\":\"https://schema.org\",\"@type\":\"LearningResource\",\"learningResourceType\":\"Coding Question\",\"name\":\"Calculate Account Tax from 2023 Income\",\"description\":\"Problem Description A financial institution is building an online application to simplify tax calculations. Write a SQL query to calculate a fixed 20% tax on the total income generated by each account during the year 2023. Schema Table: accounts +-----------+--------------+-------------+-------------------------------+ | Name | Type | Constraints | Description | +-----------+--------------+-------------+-------------------------------+ | id | INT | PRIMARY KEY | The identifier of the account | |\",\"educationalLevel\":\"medium\",\"about\":{\"@type\":\"Organization\",\"name\":\"Ubs_30june\"},\"provider\":{\"@type\":\"Organization\",\"name\":\"OAHelper\",\"url\":\"https://www.oahelper.in\"},\"isPartOf\":{\"@type\":\"WebSite\",\"name\":\"OAHelper\",\"url\":\"https://www.oahelper.in\"},\"url\":\"https://www.oahelper.in/problems/OTQ5fDA/calculate-account-tax-from-2023-income\"}"}}],["$","script",null,{"type":"application/ld+json","dangerouslySetInnerHTML":{"__html":"{\"@context\":\"https://schema.org\",\"@type\":\"BreadcrumbList\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https://www.oahelper.in/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Problems\",\"item\":\"https://www.oahelper.in/problems\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Calculate Account Tax from 2023 Income\",\"item\":\"https://www.oahelper.in/problems/OTQ5fDA/calculate-account-tax-from-2023-income\"}]}"}}],["$","$L1e",null,{"fallback":["$","section",null,{"className":"mx-auto max-w-5xl px-6 py-24 text-white","children":[["$","h1",null,{"className":"mb-5 text-3xl font-bold","children":"Calculate Account Tax from 2023 Income"}],["$","p",null,{"children":["Company: ","Ubs_30june"]}],["$","p",null,{"children":["Difficulty: ","medium"]}],false,["$","div",null,{"children":[["$","h2",null,{"children":"Problem Statement"}],["$","p",null,{"children":"Problem Description A financial institution is building an online application to simplify tax calculations. Write a SQL query to calculate a fixed 20% tax on the total income generated by each account during the year 2023. Schema Table: accounts +-----------+--------------+-------------+-------------------------------+ | Name | Type | Constraints | Description | +-----------+--------------+-------------+-------------------------------+ | id | INT | PRIMARY KEY | The identifier of the account | | iban | VARCHAR(255) | | The IBAN of the account | +-----------+--------------+-------------+-------------------------------+ Table: income +------------+--------------+------------------+------------------------------+ | Name | Type | Constraints | Description | +------------+--------------+------------------+------------------------------+ | account_id | INT | FK (accounts.id) | The reference to the account | | dt | VARCHAR(19) | | Date and time of the income | | amount | DECIMAL(6,2) | | The "}]]}],false,["$","nav",null,{"aria-label":"Related company pages","children":[["$","$L9",null,{"href":"/company-questions/ubs30june","children":["More ","Ubs_30june"," OA questions"]}],["$","$L9",null,{"href":"/interview-experiences","children":"Interview experiences"}]]}]]}],"id":"OTQ5fDA","initialQuestion":{"id":"OTQ5fDA","title":"Calculate Account Tax from 2023 Income","google_doc_link":null,"problem_statement":"$1f","solution_cpp":"SELECT\n a.iban,\n printf('%.2f', SUM(i.amount)) AS total_income,\n '20%' AS tax_rate,\n printf('%.2f', SUM(i.amount) * 0.20) AS calculated_tax\nFROM\n accounts a\nJOIN\n income i ON a.id = i.account_id\nWHERE\n SUBSTR(i.dt, 1, 4) = '2023'\nGROUP BY\n a.iban\nORDER BY\n a.iban;","solution_python":null,"solution_java":null,"pregiven_code_cpp":null,"pregiven_code_python":null,"pregiven_code_java":null,"difficulty":"medium","question_type":"sql","premium_required":0,"created_at":"2025-07-26T10:50:26+00:00","updated_at":"2026-08-10T13:22:18.359315+00:00","solution_code":null,"solution":null,"pregiven_code":"-- Write your SQLite 3 query below\n-- Keep selected columns and ordering exactly as required by the question.\n\nSELECT\n *\nFROM\n your_table_name;\n","input_test_case":"$20","output_test_case":"[\"iban|total_income|tax_rate|calculated_tax\\nDK46 1272 1831 2573 01|8596.45|20%|1719.29\\nFR55 4477 6154 73ND TN3F HMOU T36|12177.49|20%|2435.5\\nRS53 5237 5794 6016 5411 43|17476.51|20%|3495.3\", \"iban|total_income|tax_rate|calculated_tax\"]","lc_tags":null,"visual_animation":null,"difficulty_score":25,"md_problem_statement":"$21","unique_company_name":"ubs","sql_solution":"SELECT a.iban, \n printf('%.2f', SUM(i.amount)) AS total_income, \n '20%' AS tax_rate, \n printf('%.2f', SUM(i.amount) * 0.2) AS calculated_tax\nFROM accounts a\nJOIN income i ON a.id = i.account_id\nWHERE i.dt LIKE '2023-%'\nGROUP BY a.id, a.iban\nORDER BY a.iban ASC;","bash_solution":null,"js_solution":null,"editorial":null,"hidden_test_cases_blob_count":null,"youtube_tutorial":null,"prompt_solution":null,"campus_type":"on_campus","college_name":null,"role":null,"hidden_test_cases_ref":null,"is_mock_oa":false,"publication_status":"published","has_image":true,"company_name":"Ubs_30june","is_first_in_company":true,"company_ref":"Mjk5fDA","total_test_cases":10,"hidden_test_cases_count":8},"isQotdToday":false,"qotdReward":5}]]