Data Transformation and Processing Logic Questions

Direct answer

Express each condition of a business rule as its own explicit boolean, combine them with AND/OR exactly as the business states the rule (not as a simplified approximation), and when multiple rules can apply to the same record, compute every rule's outcome and take the best one explicitly (e.g. GREATEST(...)), rather than relying on evaluation order to pick a "first match."

Structured elaboration

1. One CASE/boolean expression per rule: each business condition (a flag, a threshold, a lookback window) becomes its own named boolean, both for readability and because each one is independently unit-testable.
2. "Best wins" as an explicit aggregate, not an if/elif chain: an if/elif chain silently depends on the order the rules happen to be written in; computing every applicable discount and taking the maximum is both more readable and immune to someone reordering the rules later and silently changing the outcome.
3. Testability: because each rule is its own boolean condition, you can unit test it in isolation (does the 30-day lookback correctly exclude a customer who returned something 29 days ago? 31 days ago?) rather than only testing the rule set as an opaque whole.
4. Determinism: the whole point of expressing this as data-driven boolean logic rather than procedural code with early returns is that the same input always produces the same output, regardless of which order operations happen to execute in.

Worked example

SELECT item_id,
  GREATEST(
    CASE WHEN user_is_premium AND order_total > 100 AND days_since_last_return >= 30 THEN 0.10 ELSE 0 END,
    CASE WHEN order_total > 120 THEN 0.05 ELSE 0 END
  ) AS discount
FROM line_items;

Verified against three items: item 1 (premium, total 150, 40 days since return) qualifies for the 10% premium rule, which beats the 5% volume rule, so it gets 0.10. Item 2 (premium, total 150, only 5 days since return) fails the 30-day lookback on the premium rule, so it falls back to the 5% volume rule (0.05). Item 3 (not premium, total 150) never qualifies for the premium rule at all, and also gets 0.05 from the volume rule. The GREATEST pattern correctly picks the best applicable discount in every case without depending on rule-evaluation order.

Trade-offs and pitfalls

• GREATEST (or an equivalent aggregate-of-all-rules approach) does more computation than a short-circuiting if/elif, evaluating every rule for every row; for a small number of rules per row this cost is negligible, for hundreds of rules at high throughput it becomes a real design constraint worth naming (see the related rules-engine-design question for that scale).
• Boolean conditions built from business language ("has not returned any order in the previous 30 days") need a precise, tested definition of the boundary (is exactly 30 days included or excluded?); get this wrong and the bug only shows up on specific dates, making it hard to reproduce.
• Unit tests for a rule set like this should specifically include the boundary cases (exactly at a threshold, exactly at the lookback cutoff), since those are where an off-by-one in a > versus >= actually surfaces.

Direct answer

Daily or weekly active users, and retention built on top of it, both hinge on getting the per-period distinct-user deduplication right first; the retention calculation itself is then a straightforward join between a cohort's signup week and its activity in subsequent weeks, expressed as a percentage.

Structured elaboration

1. DAU/WAU (daily active users / weekly active users): COUNT(DISTINCT user_id) per day (or per week), filtering out null/invalid user ids before counting, not after, so a null id can't accidentally get counted as "one more distinct user."
2. Retention, conceptually: for each cohort (typically grouped by signup week), track what fraction of that cohort shows any activity in week 0 (the signup week itself), week 1, week 2, and so on; this requires joining the cohort's user list against the activity log and computing, for each (cohort, week_offset) pair, how many of the cohort's users were active.
3. The most common bug: computing retention by counting activity ROWS rather than distinct users; a single highly active user can otherwise inflate a cohort's apparent retention.
4. Bucketing consistency: whatever definition of "week" you use (Monday-Sunday, signup-anniversary-based) has to be applied identically to both the cohort assignment and the activity-window checks, or the denominators and numerators quietly stop being comparable.

Worked example

SELECT event_time::DATE AS d, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE user_id IS NOT NULL
GROUP BY d ORDER BY d;

Given events where user 1 fires twice on 2024-01-01, user 2 once on the same day, and user 3 once, this correctly returns a DAU of 3 for that day, verified: the duplicate event from user 1 is deduplicated by COUNT(DISTINCT user_id), not double-counted.

Building a retention table on top of this same principle (per-cohort, per-week-offset, distinct active users divided by cohort size) is the natural extension; the worked SQL for the DAU step above is the part of the pattern most likely to be gotten wrong (via a plain COUNT(*) instead of COUNT(DISTINCT ...)), so it's the part worth walking through explicitly even when the ask is really about the retention number.

Trade-offs and pitfalls

• COUNT(DISTINCT user_id) at very large scale (billions of events per day) can become expensive; an approximate distinct-count structure (HyperLogLog) trades a small, known error for materially less compute, and is often an acceptable trade for a metric that's inherently reported at a rounded precision anyway.
• Timezone and week-boundary definitions matter more than they first appear: a user active at 11:58pm in one timezone convention and 12:02am in another can land in a different day/week bucket depending on which timezone the bucketing uses, causing two systems computing the "same" metric to disagree by a small but visible percentage.
• Retention percentages for very small cohorts are noisy (a cohort of 5 users has retention values that can only be multiples of 20%); report cohort size alongside the retention rate so a stakeholder doesn't over-read noise as signal.

Direct answer

Normalize confidently-parseable phone numbers into a single canonical format (E.164-style, a leading + and country code), extract any extension separately rather than losing it or corrupting the main number, and explicitly return "cannot confidently normalize" for genuinely ambiguous inputs instead of guessing a country code.

Structured elaboration

1. Strip formatting, isolate digits: remove punctuation, spaces, and parentheses to get the raw digit sequence, then separately detect and extract an extension (x123, ext. 123) if present, before trying to interpret the remaining digits as a phone number.
2. Confident cases: a number that already starts with + is assumed to include an explicit country code; a 10-digit number (in a context where you know the default country, e.g. US) can be confidently prefixed with that default country code; an 11-digit number starting with a 1 similarly maps cleanly to a US/Canada E.164 number.
3. Ambiguous cases, fail closed: a number like 001-44-20-1234 mixes an exit code convention with a country code in a way that isn't safely disambiguated without knowing the sender's own dialing convention; rather than guess, return "cannot confidently normalize" and route it to a review queue.
4. Privacy consideration: phone numbers are personal data; whatever normalization and storage approach you use should follow the same access-control and retention discipline as other PII, not be treated as a harmless free-text field just because it's short.

Worked example

def canonicalize_phone(raw, default_country='1'):
    digits = re.sub(r'\D', '', raw)
    ext = None
    m = re.search(r'(?:x|ext\.?)\s*(\d+)$', raw, re.IGNORECASE)
    if m:
        ext = m.group(1)
        digits = re.sub(r'\D', '', raw[:m.start()])
    if raw.strip().startswith('+'):
        return '+' + digits, ext
    if len(digits) == 10:
        return '+' + default_country + digits, ext
    if len(digits) == 11 and digits.startswith('1'):
        return '+' + digits, ext
    return None, ext  # cannot confidently normalize

Verified: '+1 (555) 123-4567' and a bare '5551234567' (with a known US default) both correctly normalize to +15551234567; '5551234 x123' correctly extracts extension 123 while returning None for the main number (a 7-digit number is too ambiguous to confidently assign a country code to); '001-44-20-1234' correctly returns None rather than a confidently-wrong guess.

Trade-offs and pitfalls

• Assuming a default country for any bare 10-digit number is only safe when you actually know the data source's country context; applying a US default to a dataset that actually mixes multiple countries' numbers will silently mis-normalize a real fraction of them.
• A library purpose-built for phone number parsing (handling the full space of international dialing conventions correctly) is generally a better production choice than a hand-rolled regex approach like the one above, which is presented here as an illustration of the fail-closed PRINCIPLE, not a claim that this exact regex handles every real-world case.
• Storing a normalized phone number without also retaining (or being able to reconstruct) the original raw input can make it hard to audit or re-normalize later if the canonicalization rule itself needs to change.

Direct answer

Pivot the category values into columns using conditional aggregation (SUM(CASE WHEN category = 'X' THEN revenue ELSE 0 END) per category), which correctly zero-fills a category that had no sales in a given month rather than leaving it absent from the result entirely.

Structured elaboration

1. Conditional aggregation as the portable pivot mechanism: not every SQL dialect has a native PIVOT operator, but every dialect supports CASE WHEN inside an aggregate, which is why this pattern is the more broadly portable choice, at the cost of needing to know the category values up front (baked into the query) rather than pivoting dynamically.
2. Zero vs NULL for a missing category-month combination: SUM(CASE WHEN ... THEN revenue ELSE 0 END) naturally produces 0 for a category with no matching rows in a given month, which is usually the right answer for a revenue pivot (no sales means zero revenue, not an unknown value).
3. Portability across dialects: engines that DO have a native PIVOT syntax (SQL Server, Snowflake) offer more concise syntax for the same result, but the conditional-aggregation form works everywhere and is the safer default to reach for when the target dialect isn't fixed yet.

Worked example

SELECT DATE_TRUNC('month', sale_date) AS month,
  SUM(CASE WHEN category='A' THEN revenue ELSE 0 END) AS cat_a,
  SUM(CASE WHEN category='B' THEN revenue ELSE 0 END) AS cat_b
FROM psales GROUP BY month;

Verified against sales of 100 (category A) and 50 (category B) in January, and 80 (category A only) in February: January correctly shows cat_a=100, cat_b=50; February correctly shows cat_a=80, cat_b=0, not NULL and not a missing row, confirming category B is zero-filled for the month it had no sales.

Trade-offs and pitfalls

• This form requires the set of category values to be known and written into the query; a genuinely dynamic set of categories (one that changes over time) needs either a code-generated query (building the CASE expressions programmatically before execution) or a different tool (a BI layer's native pivot feature) that can pivot on values discovered at query time.
• A high-cardinality column pivoted this way (thousands of distinct values) produces thousands of columns, which most engines and most downstream consumers handle poorly; conditional-aggregation pivoting is best suited to a bounded, known, moderate-cardinality set of pivot values.
• Portability comes at a small verbosity cost (one CASE expression per pivoted value, versus one line for a native PIVOT); for a fixed, small number of categories, this cost is trivial and the portability is usually worth it.

Direct answer

NULL means "unknown," not "empty" and not "zero," and it does not behave like an ordinary value in comparisons: any direct comparison involving NULL (NULL = NULL, x = NULL) evaluates to NULL (neither true nor false), which is why WHERE x = NULL never matches anything, even rows where x actually is NULL.

Structured elaboration

1. NULL vs empty string vs zero, by type: in a string column, '' is a real, present value (zero-length text) while NULL means no value was recorded; in a numeric column, 0 is a real, present value while NULL again means unknown; in a date column there is no equivalent "empty" value at all, only NULL, which is a common source of confusion when developers try to use a sentinel date instead.
2. Pitfall 1, filtering: WHERE x = NULL silently returns zero rows; the correct form is WHERE x IS NULL.
3. Pitfall 2, aggregation: COUNT(column) skips NULLs while COUNT(*) counts every row regardless; SUM/AVG over a column with NULLs ignore the NULLs rather than treating them as zero, which changes an average's denominator in a way that is easy to miss.
4. Pitfall 3, joins: a LEFT JOIN with no match produces NULLs for every column from the right-hand table, not zeros or empty strings; code that assumes a numeric join result defaults to 0 will get NULL instead unless it explicitly wraps the column in COALESCE.
5. Handling strategies: COALESCE(x, default) to substitute a value for display or arithmetic; explicit IS NULL / IS NOT NULL checks in filters; and, where the schema allows it, deciding at write time whether "unknown" should even be representable, versus requiring a value.

Worked example

SELECT id FROM users2
WHERE email IS NULL OR TRIM(email) = '';

Against rows (1, NULL), (2, ''), (3, ' '), (4, 'a@x.com'), this correctly returns ids 1, 2, and 3 (verified): NULL, an empty string, and a whitespace-only string are three distinct representations of "no real email," and all three need to be checked explicitly, none of them is caught by checking for only one of the three.

Trade-offs and pitfalls

• COALESCE(amount, 0) is the right fix when "unknown" should be treated as zero for a sum, but the wrong fix when "unknown" should exclude the row from an average entirely (it will pull the average toward zero).
• Comparing a nullable column to another nullable column with plain = silently drops rows where either side is NULL from an equi-join; some engines offer a NULL-safe equality operator (IS NOT DISTINCT FROM in Postgres, <=> in MySQL) for the cases where NULL should be treated as matching NULL.
• Casting a NULL numeric to a string for display purposes can turn it into the literal text "None" or "null" in some client libraries if not explicitly handled, which then fails an IS NULL check silently downstream.