SQL for Data Analysis Questions

Direct answer

NULL means "unknown," not a value, so it doesn't equal anything, not even another NULL. col = NULL always evaluates to NULL (treated as false in WHERE), which is why col != 'x' also silently drops rows where col is NULL: NULL != 'x' is NULL, not true. You have to test for it explicitly with IS NULL / IS NOT NULL. COALESCE(expr1, expr2, ...) returns the first non-NULL argument, commonly used to substitute a default.

Structured elaboration

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL (=, !=, <, >) evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is TRUE, so UNKNOWN rows are silently dropped, exactly like FALSE rows, with no error.

Aggregates mostly ignore NULLs rather than propagating the UNKNOWN logic: SUM, AVG, MAX, MIN skip NULL values, and COUNT(column) counts only non-NULL values in that column, while COUNT(*) counts rows regardless of NULLs.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES (1, 'alice@x.com'), (2, NULL), (3, 'carol@x.com');

CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 1, 100), (2, 1, NULL), (3, 2, 50), (4, 3, 75), (5, 3, NULL);

= NULL versus IS NULL:

SELECT COUNT(*) FROM users WHERE email = NULL;    -- 0 rows
SELECT COUNT(*) FROM users WHERE email IS NULL;    -- 1 row

Verified: 0 matches for = NULL, 1 match for IS NULL (user 2).

!= silently drops the NULL row:

SELECT user_id, email FROM users WHERE email != 'alice@x.com';

Result: only (3, 'carol@x.com'). User 2 (NULL email) is not "not equal to alice," it's UNKNOWN, so it never appears, even though intuitively a missing email is certainly different from a specific address.

COUNT(*) vs COUNT(column):

SELECT COUNT(*) AS row_count, COUNT(amount) AS non_null_amount_count, SUM(amount) AS total
FROM payments;

Result: (5, 3, 225). There are 5 payment rows, but only 3 have a non-NULL amount, and SUM adds only those 3 (100 + 50 + 75 = 225), silently ignoring the 2 NULLs rather than making the whole sum NULL.

COALESCE for defaults:

SELECT payment_id, amount, COALESCE(amount, 0) AS amount_or_zero FROM payments ORDER BY payment_id;

Result: payments 2 and 5 (NULL amount) show amount_or_zero = 0, everything else passes through unchanged.

NULL behavior after a LEFT JOIN (fan-out and double counting). Aggregating right after a multi-table LEFT JOIN is a common trap when a customer has more than one matching row on another joined table. Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (101, 1, 50), (102, 1, 30), (103, 2, 20);

CREATE TABLE refunds (refund_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO refunds VALUES (201, 1, 10), (202, 1, 5);

Alice has 2 orders and, separately, 2 refunds. Joining both to customers in one query multiplies her order rows by her refund count:

SELECT c.customer_id, COUNT(o.order_id) AS order_rows_seen, SUM(o.amount) AS sum_order_amount_wrong
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN refunds r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

Result: Alice shows order_rows_seen = 4 and sum_order_amount_wrong = 160, double the true values (2 orders, $80), because each of her 2 orders got paired with each of her 2 refunds (2x2 = 4 rows). Bob (1 order, 0 refunds, so no fan-out) correctly shows 1 and 20.

Fix, pre-aggregate orders before joining anything else:

WITH order_agg AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS sum_order_amount
  FROM orders GROUP BY customer_id
)
SELECT c.customer_id, oa.order_count, oa.sum_order_amount
FROM customers c LEFT JOIN order_agg oa ON oa.customer_id = c.customer_id;

Result: Alice correctly shows (1, 2, 80). Note AVG(o.amount) in the broken version happened to still read 40.0, the same as the correct average, because Alice's duplication factor was uniform (every order duplicated exactly twice); that's a coincidence of this data, not something to rely on. COUNT and SUM are the numbers that reveal the bug reliably.

Trade-offs & pitfalls

• Never write = NULL or != NULL; both silently return zero rows and produce no error, which makes this bug easy to ship unnoticed.
• NOT IN (subquery) is a related NULL trap: if the subquery returns even one NULL, NOT IN returns no rows at all for every outer row, because comparing against a NULL element makes the whole NOT IN expression UNKNOWN. Prefer NOT EXISTS when the inner column can contain NULLs.
• When aggregating after joining multiple one-to-many relationships in a single query, pre-aggregate each one-to-many table separately (as in the fix above) before joining, or use COUNT(DISTINCT ...) as a partial guard, though DISTINCT alone won't fix a corrupted SUM.
• GROUP BY treats all NULLs as one group (they're considered equal for grouping purposes, unlike in a WHERE ... = NULL comparison), and DISTINCT does the same, collapsing multiple NULLs into a single NULL row.

Direct answer

Clean messy raw values in two separate steps, never one: first reshape the string into a canonical representation (strip whitespace, currency symbols, thousands separators; lowercase and trim for text), then validate the reshaped value against a pattern before casting. Anything that fails validation becomes NULL plus a flag, not a thrown error and not a silently wrong number. This same reshape-then-validate pattern works for amounts, emails, and any other inconsistently formatted upstream field.

Structured elaboration

The three-stage pattern:

1. Reshape: trim() and regexp_replace() to strip characters that don't belong (currency symbols, commas, stray quotes) while preserving the characters that carry meaning (leading minus sign, decimal point).
2. Validate: test the reshaped string against a strict pattern (^-?[0-9]+(\.[0-9]+)?$ for a numeric amount, a basic email shape for an email) before casting. Never cast first and catch the error; a bad cast aborts the whole statement in most engines. TRY_CAST/SAFE_CAST is the safe alternative here (NULL on failure by design, verified against DuckDB above and true across Postgres 16+, SQL Server, Snowflake, and BigQuery), but that NULL then silently propagates into a SUM/AVG as if the row did not exist, unless you separately COUNT the flagged rows. The real silent-coercion risk lives in legacy non-strict engines: MySQL's non-strict mode and SQLite's plain CAST genuinely do coerce garbage (verified: SQLite's CAST('12x' AS INTEGER) returns 12 and CAST('abc' AS INTEGER) returns 0), which is exactly why explicit TRY_CAST-then-flag beats implicit casting.
3. Flag: carry a boolean (or reason code) alongside the cleaned value so malformed rows are countable and reviewable, not just dropped.

Dialect note: the SQL below was run on DuckDB, whose regexp_replace/regexp_matches functions and 'g' (global) flag match PostgreSQL's. On engines without POSIX-style regex (older MySQL, some SQLite builds) you'd swap in TRANSLATE/REPLACE chains or a UDF for the reshape step, but the reshape-then-validate structure is unchanged.

Worked example

Seed data:

WITH cleaned AS (
  SELECT
    tx_id,
    regexp_replace(trim(raw_amount), '[$,]', '', 'g') AS amount_str,
    lower(trim(raw_email)) AS email_clean
  FROM staging
),
validated AS (
  SELECT
    tx_id,
    CASE WHEN regexp_matches(amount_str, '^-?[0-9]+(\.[0-9]+)?$')
         THEN CAST(amount_str AS DECIMAL(10,2))
         ELSE NULL
    END AS amount,
    CASE WHEN regexp_matches(email_clean, '^[^@\s]+@[^@\s]+\.[^@\s]+$')
         THEN email_clean
         ELSE NULL
    END AS email
  FROM cleaned
)
SELECT s.tx_id, s.raw_amount, v.amount, s.raw_email, v.email,
       (v.amount IS NULL AND s.raw_amount IS NOT NULL) AS amount_flagged,
       (v.email IS NULL AND s.raw_email IS NOT NULL) AS email_flagged
FROM staging s JOIN validated v USING (tx_id)
ORDER BY tx_id;

Actual run output:

Row 4 shows the amount cleaning working on a value with no $ at all, and row 5 shows both fields correctly flagged as malformed instead of silently becoming 0 or an empty string.

Alternate scenario: extract the domain from an email

Same email_clean reshaping, then split on @:

SELECT tx_id, raw_email, lower(trim(raw_email)) AS email_clean,
       CASE WHEN regexp_matches(lower(trim(raw_email)), '^[^@\s]+@[^@\s]+\.[^@\s]+$')
            THEN split_part(lower(trim(raw_email)), '@', 2)
            ELSE NULL END AS domain
FROM staging ORDER BY tx_id;

Real output: rows 1-3 all resolve to example.com (case and whitespace differences collapse to the same domain), row 6 resolves to sample.io, and rows 4-5 correctly return NULL for domain (missing or invalid email). The key point: domain extraction must run on the already-cleaned email, not the raw one, or Example.COM and example.com count as different domains.

Alternate scenario: inconsistent customer_id formats across sources

A different shape of the same underlying problem: three source systems format the same natural id differently (CUST-0001, cust0001, 0001).

SELECT source, raw_customer_id,
       'CUST-' || lpad(regexp_replace(upper(trim(raw_customer_id)), '[^0-9]', '', 'g'), 4, '0') AS canonical_customer_id
FROM customer_ids_raw
ORDER BY canonical_customer_id, source;

Real output (7 rows from 3 sources):

Stripping everything but digits and re-padding gives all three sources the same canonical key for the same customer, which is exactly what you need before joining across systems.

Trade-offs & pitfalls

• Locale-aware amount parsing (e.g. to_number(raw, 'FM999G999D99'), or the reverse: European 1.234,56 where . is the thousands separator) is more "correct" for a single known locale but breaks silently if the source ever mixes formats. Stripping [$,] is more robust to genuinely messy, multi-format input at the cost of not handling every locale.
• Don't over-trust a passing regex. ^-?[0-9]+(\.[0-9]+)?$ accepts -0.00, 000123, and other technically-valid-but-suspicious values; validation catches malformed shape, not business-rule violations (negative transaction amounts, absurdly large values), which need separate checks.
• Push cleaning upstream when the volume justifies it. For a one-off analysis, cleaning in the query is fine. For a recurring pipeline, the same logic belongs in an ETL/dbt staging layer so every downstream query inherits clean data instead of re-implementing the regex.
• Extracting a domain from an uncleaned email double-counts variants (Example.COM vs example.com) as different domains; always normalize before deriving.

Reach for SQL whenever the task needs to run against the full underlying data rather than a manually pasted extract, needs to be exactly reproducible, or needs to combine several tables by a key. A spreadsheet's VLOOKUP and pivot table are fine for a small, single-table slice a person can eyeball once; they degrade badly as the row count, the number of source tables, or the need to repeat the calculation correctly next month all grow.

Two concrete examples

1. Joining and deduplicating across multiple sources. Combining a CRM export, a billing export, and a support-ticket export by customer_id into one row per customer. VLOOKUP handles one lookup column against one other sheet; the moment a task needs a three-way join with duplicate keys on either side, VLOOKUP returns only the first match and silently drops or miscounts the rest. A SQL JOIN handles the full match set deterministically, and a GROUP BY handles the deduplication explicitly and visibly.

2. Any calculation someone else needs to reproduce exactly, or that needs to run on a schedule. A pivot table's field configuration lives inside the spreadsheet's UI: it isn't version-controlled, isn't easy to diff, and has to be manually rebuilt correctly by whoever opens the file next. A saved SQL query (or a view) is text: it runs identically every time, can sit in source control, and can be scheduled without a human reopening a workbook.

The reverse direction: SQL's equivalent of a pivot table or VLOOKUP

A GROUP BY with aggregate functions is SQL's version of a pivot table's row-grouping plus value-aggregation. A JOIN on a shared key is SQL's version of VLOOKUP or INDEX-MATCH: instead of "look up this key in that other sheet's range," it's "match every row in table B to the row in table A that shares this key," for as many tables as needed at once.

Worked example

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  region   TEXT,
  amount   NUMERIC
);

INSERT INTO orders (region, amount) VALUES
  ('East', 400), ('East', 600), ('West', 150),
  ('West', 200), ('West', 250), ('North', 2400);

SELECT
  region,
  SUM(amount) AS region_revenue,
  ROUND(100.0 * SUM(amount) / (SELECT SUM(amount) FROM orders), 1) AS pct_of_total
FROM orders
GROUP BY region
ORDER BY region_revenue DESC;

Result:

┌────────┬────────────────┬──────────────┐
│ region │ region_revenue │ pct_of_total │
├────────┼────────────────┼──────────────┤
│ North  │ 2400           │ 60.0         │
│ East   │ 1000           │ 25.0         │
│ West   │ 600            │ 15.0         │
└────────┴────────────────┴──────────────┘

A pivot table gets the region_revenue column easily. Getting pct_of_total right, as a value that stays correct if a new region is added later, needs either a spreadsheet formula referencing a moving total range (easy to break: forgetting to update the range after adding a row is a classic spreadsheet bug) or exactly this scalar subquery, which recomputes the grand total from the same source rows every time the query runs.

Trade-offs & pitfalls

• SQL is not better at everything. Ad-hoc, throwaway exploration by someone without database access, or the final polished chart for a stakeholder deck, is usually faster and clearer done in a spreadsheet or the BI layer.
• Centralize a calculation once in SQL (a shared view) instead of letting five dashboards each compute "active users" a slightly different way in their own pivot tables. This is one of the most common reasons dashboards disagree with each other.
• Common wrong turn: over-engineering a one-time, 20-row question into a SQL pipeline when a five-minute spreadsheet look would answer it. Match the tool to whether the task repeats or needs to scale, not to which tool seems more technical.

Direct answer

"Retention" and "churn" are two sides of the same measurement, but each can be defined from either of two different signals: a contractual signal (is the subscription still active or cancelled) or a behavioral signal (did the user actually use the product). The two definitions do not just differ semantically, they can disagree on specific individual users, so picking one changes who counts as retained or churned, not only the reported percentage.

Structured elaboration

Trade-offs of each:

• Contractual: cheap to compute (one status/timestamp column), matches billing and revenue reporting directly, but misses "zombie" users who keep paying but stopped using the product (a silent-churn risk invisible until renewal), and misses "grace period" users who cancelled but keep logging in until the paid period actually ends.
• Behavioral: reflects real engagement and catches disengagement earlier (useful for a re-engagement intervention), but requires picking an inactivity window and an "active event" definition, and can flag someone as churned who is still a paying customer, just on a break.

Worked example

Sample data, tested in DuckDB 1.5:

subscriptions:

events (product usage):

Definition 1, contractual churn for February 2024:

WITH base AS (
  SELECT COUNT(*) AS active_start_of_month
  FROM subscriptions
  WHERE status = 'active' OR cancelled_at >= DATE '2024-02-01'
),
churned AS (
  SELECT COUNT(*) AS churned_users
  FROM subscriptions
  WHERE cancelled_at >= DATE '2024-02-01' AND cancelled_at < DATE '2024-03-01'
)
SELECT b.active_start_of_month, c.churned_users,
       ROUND(100.0 * c.churned_users / b.active_start_of_month, 2) AS contractual_churn_pct
FROM base b, churned c;

Result: active_start_of_month = 5, churned_users = 2, contractual_churn_pct = 40.0.

Definition 2, behavioral churn (active in January, zero events in February):

WITH jan_active AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_time >= DATE '2024-01-01' AND event_time < DATE '2024-02-01'
),
feb_active AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_time >= DATE '2024-02-01' AND event_time < DATE '2024-03-01'
)
SELECT COUNT(*) AS jan_active_users,
       COUNT(*) FILTER (WHERE f.user_id IS NULL) AS churned_users,
       ROUND(100.0 * COUNT(*) FILTER (WHERE f.user_id IS NULL) / COUNT(*), 2) AS behavioral_churn_pct
FROM jan_active j
LEFT JOIN feb_active f ON f.user_id = j.user_id;

Result: jan_active_users = 5, churned_users = 2, behavioral_churn_pct = 40.0.

Both land on 40% for this month, but a per-user comparison shows they disagree on WHO churned:

User 3 (subscription still "active," zero February activity) is churned only under the behavioral definition. User 4 (cancelled subscription, but still logging in during a grace period) is churned only under the contractual definition.

Trade-offs & pitfalls

• The two definitions can land on the same aggregate rate by coincidence (40% both, in this example) while disagreeing on which individual users churned. Never assume "the numbers match" means "the definitions agree."
• The same ambiguity applies to "inactive" and "reactivated" users: an "inactive" user could mean subscription-cancelled or simply zero recent events, and a "reactivated" user could mean a resumed subscription or a return to activity after a behavioral-inactivity flag. Each needs the same clarifying pass before it gets implemented, using whichever of the two lenses above the downstream decision actually needs.
• FILTER (WHERE ...) is Postgres/DuckDB syntax; the portable fallback is SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Direct answer

Convert each order's UTC instant into the customer's own local wall-clock time using their IANA timezone name (the standard named-timezone database, e.g. America/New_York), then take the date part of that converted timestamp for bucketing. Applying one global UTC offset to every user is wrong for two separate reasons: different users need different offsets, and even a single user's offset changes across a DST (daylight saving time) transition, so any fixed number is only ever correct for part of your users for part of the year.

Structured elaboration

• IANA zone names vs fixed offsets: AT TIME ZONE 'America/New_York' looks up the full DST rule set (the tzdb) for that region and applies whichever offset is correct for that specific timestamp. A fixed offset like -05:00 has no rules, it is just always five hours, which is right for New York in winter (EST) and wrong in summer (EDT).
• Steps: join orders to users on user_id, convert order_date (a UTC instant) into local time with order_date AT TIME ZONE u.timezone, cast the result to DATE, filter and aggregate on that local date.
• Two distinct offset traps, both present in the worked example below:
  1. One offset cannot fit users in different zones at all (Kolkata is UTC+5:30 with no DST; New York alternates between UTC-4 and UTC-5).
  2. Even for a single zone, the correct offset itself changes at the DST boundary, so a number that was right for New York in July is wrong in December.
• Dialect notes: Postgres and DuckDB (with the icu extension loaded) both support timestamp AT TIME ZONE 'IANA/Name' directly, using the underlying tzdata. MySQL needs the timezone tables populated (mysql_tzinfo_to_sql) before CONVERT_TZ() recognizes IANA names; without that load it silently returns NULL instead of erroring. BigQuery uses DATETIME(timestamp_expr, tz_string). Snowflake uses CONVERT_TIMEZONE(tz, timestamp).

Worked example (executed, DuckDB 1.5 with the icu extension loaded, session TimeZone set to UTC)

Seed data: three users in three timezones, and five orders clustered right around the UTC midnight boundary for 2025-11-01 so the correct local date differs from the naive interpretation for most of them.

INSTALL icu; LOAD icu; SET TimeZone='UTC';

CREATE TABLE users (user_id INTEGER, tz_name VARCHAR);
INSERT INTO users VALUES
  (1, 'America/New_York'),
  (2, 'Asia/Kolkata'),
  (3, 'Europe/London');

CREATE TABLE orders (order_id INTEGER, user_id INTEGER, order_date TIMESTAMPTZ, amount_usd DECIMAL(10,2));
INSERT INTO orders VALUES
  (1, 1, TIMESTAMPTZ '2025-11-01 03:30:00 UTC', 100.00),
  (2, 1, TIMESTAMPTZ '2025-11-01 12:00:00 UTC', 50.00),
  (3, 2, TIMESTAMPTZ '2025-10-31 19:00:00 UTC', 75.00),
  (4, 2, TIMESTAMPTZ '2025-11-01 20:00:00 UTC', 200.00),
  (5, 3, TIMESTAMPTZ '2025-11-01 10:00:00 UTC', 30.00);

Correct query:

SELECT o.order_id, o.user_id, o.amount_usd,
       CAST((o.order_date AT TIME ZONE u.tz_name) AS DATE) AS local_date
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE CAST((o.order_date AT TIME ZONE u.tz_name) AS DATE) = DATE '2025-11-01';

Per-order local conversion (the intermediate step, shown for every order):

Correct result for "orders placed on 2025-11-01" (3 orders, $155.00): order 2 (NY, local 08:00 Nov 1), order 3 (Kolkata, UTC Oct 31 19:00 is already local 00:30 Nov 1), order 5 (London, same day in both).

Now the pitfalls, run against the identical seed data:

-- Pitfall 1: bucket by the raw UTC date, ignore timezone entirely
SELECT COUNT(*) AS n_orders, SUM(amount_usd) AS revenue
FROM orders WHERE CAST(order_date AS DATE) = DATE '2025-11-01';
-- result: 4 orders, $380.00  (orders 1, 2, 4, 5)

-- Pitfall 2: apply ONE global fixed offset (UTC-5) to every user regardless of their actual zone
SELECT COUNT(*) AS n_orders, SUM(amount_usd) AS revenue
FROM orders WHERE CAST((order_date - INTERVAL 5 HOUR) AS DATE) = DATE '2025-11-01';
-- result: 3 orders, $280.00  (orders 2, 4, 5)

Correct: 3 orders, $155.00. Raw-UTC-date: 4 orders, $380.00 (wrongly includes order 1, which was really Oct 31 in New York, and wrongly includes order 4, which was really Nov 2 in Kolkata; wrongly excludes order 3). Global -5h offset: coincidentally also lands on 3 orders, but the WRONG 3: it wrongly excludes order 3 (Kolkata's true offset is +5:30, not -5) and wrongly includes order 4, while order 1 happens to still land on Oct 31 by coincidence. The row count matching the correct answer by accident is the most dangerous failure mode here: a count-only sanity check would not catch it, only the revenue total ($280 vs the correct $155) or a row-by-row diff would.

Trade-offs and pitfalls

• A users.timezone that is NULL or an invalid IANA string needs an explicit policy: LEFT JOIN plus COALESCE(u.timezone, 'UTC') with the fallback rows flagged in a separate is_timezone_assumed column, rather than silently dropping those users or silently mis-bucketing them as UTC without a flag.
• Per-row AT TIME ZONE conversion is not sargable (an index can't be used to search on it, because the column is wrapped in a function) against a plain index on order_date, so filtering a huge table to "around 2025-11-01" should first narrow with a cheap UTC range wide enough to cover every timezone's version of that local day (roughly order_date >= '2025-10-31' AND order_date < '2025-11-03' covers the full +14/-12 UTC offset range), then apply the exact per-user local-date predicate on that smaller set.
• The global-offset pitfall is really two bugs wearing one trenchcoat: cross-user (one offset cannot serve users in different zones) and cross-time (one offset cannot serve one zone across a DST boundary). Fixing only one of them (e.g. hardcoding "New York is always -5" outside DST season) still leaves the other live and waiting for the next March or November boundary.
• IANA tzdb data itself is updated periodically (governments change DST rules with short notice); pin and update the engine's tzdata/ICU version deliberately rather than assuming it is frozen forever.