Intuit·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

Intuit SQL round, one meaty question about parsing currency strings stored as VARCHAR. Not a vibe check, just pure technical execution under pressure.

Questions Asked (1)

A database column stores monetary values as strings with suffixes like '1.5M', '2B', or '750K'. Write a SQL query to convert these into actual numeric values (K = 1,000, M = 1,000,000, B = 1,000,000,000) and then aggregate, sort, or filter on the result.

Data ModelingTechnical Trade-offs

Author's notes

This one took me longer to get rolling than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the database dialect and data quality assumptions, then propose a solution that parses the numeric part and applies a multiplier based on the suffix. Use a CASE expression or a lookup table to convert suffixes to multipliers, and wrap the conversion in a subquery or CTE so you can aggregate, sort, or filter on the numeric result. Finally, discuss trade-offs like performance, maintainability, and edge cases.

Pro tip: Mention that storing monetary values as strings with suffixes is an anti-pattern and recommend migrating to a numeric column with a separate unit column or storing values in a consistent base unit (e.g., cents). This shows you think beyond the immediate query to data modeling and long-term maintainability.

1. Clarify requirements and assumptions

Ask about the database engine (e.g., PostgreSQL, MySQL, SQL Server) and whether the data is clean (e.g., always has a suffix, no negative values, no decimals without suffix). Confirm that suffixes are case-insensitive and that only K, M, B are used.

2. Extract numeric part and suffix

Use string functions to separate the numeric portion from the suffix. For example, in PostgreSQL use `LEFT(col, -1)` and `RIGHT(col, 1)`, or use regular expressions to extract the number and the suffix.

3. Map suffix to multiplier

Use a CASE expression to assign the multiplier: K → 1000, M → 1000000, B → 1000000000. Alternatively, use a lookup table or a CTE for better readability and maintainability.

4. Compute numeric value and aggregate/sort/filter

Multiply the extracted number by the multiplier, and wrap the conversion in a subquery or CTE. Then apply the required aggregation (SUM, AVG), sorting (ORDER BY), or filtering (WHERE) on the computed numeric column.

5. Discuss trade-offs and alternatives

Highlight performance considerations (e.g., function calls prevent index usage), suggest a computed column or view for reusability, and recommend schema changes to store numeric values directly for future data.

Key Points to Mention

Use of CASE expression or lookup table for suffix-to-multiplier mapping
String manipulation functions (LEFT, RIGHT, SUBSTRING, REGEXP) to parse the value
Wrapping conversion in a subquery/CTE to aggregate, sort, or filter on the numeric result
Handling edge cases: missing suffix, lowercase suffixes, negative values, decimals
Performance implications: non-sargable expressions, lack of index usage, and potential for computed columns
Data modeling recommendation: store monetary values as numeric types with a unit column or in a base unit (e.g., cents) to avoid parsing overhead

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.