Parse CSV and format transactions
Company: Kikoff
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a CSV file containing many transactional records with columns Merchant, Amount, Date (YYYY-MM-DD), and Status, write a script to parse the file and print, for each transaction, the following two-line block exactly in the given order:
---------------------------
| {Merchant} ${Amount with two decimals} |
| {Date} {Status} |
---------------------------
Example for one row:
---------------------------
| Merchant $10.04 |
| 2020-01-01 pending |
---------------------------
Requirements:
- Preserve the input row order.
- Format Amount as a dollar value with two decimals (e.g., $10.
04).
- Trim surrounding whitespace in fields before formatting.
- Output one block per transaction with the same delimiter lines and spacing pattern.
Overview: This question evaluates a candidate's ability to parse and format structured transactional data, emphasizing CSV parsing, trimming whitespace, numeric formatting to two decimal places, and preserving input row order.
You are given a table that stores transaction records imported from a CSV file. The CSV had columns Merchant, Amount, Date (YYYY-MM-DD), and Status. The import process has stored the rows in the table in their original order using an auto-incrementing primary key.
Write a SQL query that, for each transaction, outputs the following 4-line block as separate rows, preserving the original input order:
---------------------------
| {Merchant} ${Amount with two decimals} |
| {Date} {Status} |
---------------------------
Example for one transaction:
---------------------------
| Merchant A $10.04 |
| 2020-01-01 pending |
---------------------------
Requirements:
- Preserve the original row order using the primary key.
- Format Amount as a dollar value with two decimals (e.g., $10.04, $5.00).
- Trim surrounding whitespace in Merchant and Status before formatting.
- Output one 4-row block per transaction, with the delimiter line '---------------------------' before and after the two content lines.
- The result should have one row per printed line, with columns:
- txn_id (the transaction id)
- line_no (1, 2, 3, or 4 within each block)
- line_text (the exact text of that line)
Tables
transactions(txn_id INT, merchant VARCHAR(100), amount DECIMAL(10,2), txn_date DATE, status VARCHAR(20))
Hints
- Use TRIM on merchant and status, and a formatting function like TO_CHAR (or equivalent in your SQL dialect) to ensure two decimal places on amount.
- Generate one row per printed line using VALUES/UNION ALL or a lateral join, and order by the transaction id and the line number within each block.