Data Transformation and Processing Logic Questions

Implementing transformation logic: joins, aggregations, deduplication, pivoting/reshaping, and business-rule application over datasets. Covers writing correct and maintainable transformation code, handling edge cases in the transform layer, and preparing data for downstream consumption. Focuses on the logic of turning raw data into analytics-ready outputs.

MediumTechnical

35 practiced

Given an orders table and a refunds table, compute net revenue per order date (or per month) applying the business rule that a refund reduces revenue on the original order's date, not the refund's date. Handle orders with no refunds, refunds with no matching order (flag them rather than silently dropping them), and partial refunds. As a related exercise: compute monthly revenue by region in a single reporting currency when orders carry mixed currencies and you must join to a table of historical FX rates, using the most recent rate on or before each order's date.

Sample Answer

MediumTechnical

34 practiced

Implement a discount or promotion rule as deterministic, testable logic: for example, a line item qualifies for a discount only when several conditions all hold (a flag on the customer, a threshold on the order total, and a lookback window with no qualifying event). When multiple promotions could apply to the same item, the best discount should win. Provide an implementation (SQL or pseudocode) and describe how you would unit-test the rule set, including its edge cases.

Sample Answer

MediumTechnical

30 practiced

Given a free-text address field with inconsistent layouts across countries, describe a strategy to parse and canonicalize it into structured components (street, city, region, postal code, country) suitable for matching and analytics, including how you would handle a case your approach can't confidently resolve. As a smaller related exercise, implement a general text-normalization function that trims whitespace, collapses repeated internal whitespace, strips control characters, and preserves Unicode letters and emoji.

Sample Answer

Direct answer Parse a free-text address into structured components using a mix of regex heuristics and a purpose-built parsing library, attach a confidence score to uncertain matches rather than a binary success/fail, and escalate low-confidence cases to an external geocoding service rather than silently accepting a bad parse; a smaller, related need is a general text-normalization utility for cleaning up whitespace and control characters before any of this structured parsing happens. Structured elaboration Normalize before parsing: trim, collapse repeated whitespace, and strip control characters from the raw text first, since inconsistent whitespace alone can break a naive tokenizer before it even gets to the address-specific logic. Tokenize and match against known patterns: a parsing library (built specifically for postal address formats, which vary significantly by country) handles the bulk of well-formed cases; expect it to still fail or guess on truly irregular inputs. Confidence scoring, not a binary outcome: rather than "parsed" or "failed," attach a confidence score so a low-confidence parse can be routed differently (to a cheaper fallback or a human reviewer) than a high-confidence one, which can be trusted directly. Escalation to an external geocoding service: for addresses the local parser can't confidently resolve, a paid geocoding API can often disambiguate using real-world postal data, at a per-call cost that needs to be weighed against how often escalation is actually needed. General text normalization, as the smaller companion utility: trimming whitespace, collapsing repeated internal whitespace, and stripping control characters while explicitly preserving Unicode letters and emoji is a narrower, reusable piece of the same "clean free text before matching" competency, useful well beyond just addresses. Worked example pythondef normalize_text(s): s = s.strip() s = re.sub(r'[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]', '', s) # control chars s = re.sub(r'\s+', ' ', s) return s Verified: " hello world\t\n " correctly normalizes to "hello world" (trimmed and collapsed), while "café \U0001F600" (an accented letter plus an emoji) is correctly left untouched, control-character stripping and whitespace collapsing do not damage legitimate Unicode content. This normalization step is what you'd run on a raw address field BEFORE handing it to an address-parsing library or a confidence-scored matcher, since inconsistent whitespace and stray control characters are a common, avoidable source of otherwise-good addresses failing to parse. Trade-offs and pitfalls A confidence threshold for auto-accepting a parsed address is a precision/recall trade-off exactly like the one in fuzzy entity resolution: too permissive and bad parses get treated as good; too strict and too many legitimate addresses get escalated unnecessarily, driving up geocoding API cost. Escalating every low-confidence address to a paid geocoding service without a volume cap can turn an occasional fallback into a significant, unplanned recurring cost; rate-limit or sample escalations if volume is unpredictable. Aggressively "cleaning" free text can occasionally strip something meaningful if the regex is too broad (a legitimate but unusual Unicode character mistaken for a control character); test the normalization function against a representative sample of real international addresses, not just ASCII-only examples.

MediumTechnical

28 practiced

Given a table of per-entity transactions (an entity id, an amount, and a timestamp), write a SQL query using window functions that computes a rolling N-day average or sum per entity per day, including days where the entity had no activity (rather than silently skipping them). Explain how you generate the calendar of dates needed for this and discuss the RANGE versus ROWS framing for the window.

Sample Answer

MediumTechnical

28 practiced

Given a table of per-user transactions (amount, timestamp), write a SQL query using window functions that flags an outlier transaction as one where the amount exceeds the user's own historical mean plus 3 standard deviations, computed over a trailing period (e.g. the past 365 days). Explain how you handle a user with fewer than 2 prior transactions, and discuss why a simple z-score threshold can miss an outlier that a robust method like IQR would catch, since the outlier itself inflates the standard deviation used to judge it.

Sample Answer

Unlock Full Question Bank

Get access to all 25 Data Transformation and Processing Logic interview questions and detailed answers.

Join thousands of developers preparing for their dream job.