← Intuit Interview InsightsSoftware Engineer
I got the basic CASE-on-the-last-character approach out pretty fast, something like multiplying CAST(LEFT(val, LEN(val)-1) AS DECIMAL) by the suffix factor.
Start by clarifying the database dialect and edge cases, then outline a parsing strategy using string functions to extract the numeric part and suffix. Convert the suffix to a multiplier, compute the numeric value, and finally demonstrate aggregation, sorting, or filtering on the derived column.
Pro tip: Mention that storing monetary values as VARCHAR is an anti-pattern and propose a migration to a DECIMAL column, showing you think beyond the immediate query.
Ask about the database system, data consistency, and handling of invalid or missing values. Confirm whether the query should be a one-time fix or part of a long-term solution.
Use string functions (e.g., SUBSTRING, LEFT, RIGHT, REGEXP) to separate the numeric portion from the suffix (K, M, B). Handle cases where the suffix might be absent or lowercase.
Map each suffix to its multiplier (K=1000, M=1000000, B=1000000000) using a CASE expression, then multiply the parsed number by the multiplier to get the true numeric value.
Use the computed numeric value in an ORDER BY, WHERE, or aggregate function (e.g., SUM, AVG) to demonstrate the final result. Consider using a subquery or CTE for readability.
Highlight performance implications of string parsing and suggest schema improvements, such as storing values as DECIMAL or using a separate multiplier column.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.