Format VARCHAR amounts into $ with M/B suffix
Company: Intuit
Role: Software Engineer
Category: Software Engineering Fundamentals
Difficulty: medium
Interview Round: Take-home Project
You are given a table `transactions` with:
- `id` (primary key)
- `amount_str` (VARCHAR): a numeric string representing an amount in dollars (e.g., `'950'`, `'2500000'`, `'9876543210'`).
**Task (SQL):** Write a query that outputs `id` and a formatted amount string:
- If amount `< 1,000,000`: output like `$950` (no suffix).
- If `1,000,000 <= amount < 1,000,000,000`: output as dollars in millions with suffix `M`, rounded to **1 decimal** (e.g., `2500000 -> $2.5M`).
- If `>= 1,000,000,000`: output as dollars in billions with suffix `B`, rounded to **1 decimal** (e.g., `9876543210 -> $9.9B`).
Assume `amount_str` may contain leading/trailing spaces; you should convert it to a numeric type before comparing/dividing.
Quick Answer: This question evaluates a candidate's ability to perform SQL data manipulation tasks such as string-to-number conversion, conditional scaling and suffixing, numeric rounding, and formatting for presentation.