Introduction to Data Engineering — 100 SQL & Python Interview Questions

Real-world country columns leak more than just NULL: '\N' (the default CSV null sentinel from COPY ... FORMAT csv), the literal string '<NULL>', empty strings, whitespace-only rows (' '), and 'NA' / 'N/A'. A naive COALESCE(country, '<NULL>') only catches the first one. The fix has to canonicalize all of them — and report which dirty variants existed so the DQ team can ticket the upstream.

-- Production-grade: collapse every flavor of "missing" into one bucket,
-- normalize case + whitespace so 'BR' and 'br' aggregate together,
-- and surface raw_variants so the DQ team can ticket the upstream owner.
WITH cleaned AS (
  SELECT
    CASE
      WHEN country IS NULL                                              THEN '<NULL>'
      WHEN TRIM(country) = ''                                            THEN '<EMPTY>'
      WHEN UPPER(TRIM(country)) IN ('\N','NULL','<NULL>','NA','N/A')    THEN '<NULL>'
      ELSE UPPER(TRIM(country))             -- normalize case + trim
    END AS country_clean,
    country AS country_raw
  FROM users
)
SELECT country_clean,
       COUNT(*)                                                          AS n,
       COUNT(DISTINCT country_raw)                                       AS raw_variants,
       STRING_AGG(DISTINCT '''' || COALESCE(country_raw, 'NULL') || '''', ', ' ORDER BY '''' || COALESCE(country_raw, 'NULL') || '''') AS variants_seen
FROM cleaned
GROUP BY country_clean
ORDER BY n DESC;

-- Expected output collapses dirty variants into clean buckets,
-- with raw_variants flagging which buckets had multiple input forms.

  country_clean   n  raw_variants           variants_seen
0            US  54             2             ' US', 'US'
1            IN  35             2              'IN', 'in'
2            BR  24             2              'BR', 'br'
3            UK  21             2             'UK ', 'UK'
4            FR  20             1                    'FR'
5            JP  18             1                    'JP'
6        <NULL>  17             2  '<NULL>', 'NULL', '\N'
7            DE   7             1                    'DE'
8       <EMPTY>   4             2             '   ', '  '

GROUP BY country is case-sensitive on Postgres / DuckDB / Snowflake (with default collation). 'BR' and 'br' are two buckets — almost always a DQ bug, never the intent. Normalize with UPPER(TRIM(country)) at the GROUP BY boundary, but keep the raw value visible for the audit trail.

-- See variants per canonical bucket
SELECT UPPER(TRIM(country))                          AS country_clean,
       COUNT(*)                                       AS n,
       ARRAY_AGG(DISTINCT country ORDER BY country)   AS raw_forms_observed
FROM users
WHERE country IS NOT NULL
GROUP BY UPPER(TRIM(country))
HAVING COUNT(DISTINCT country) > 1                    -- only buckets with dirty forms
ORDER BY n DESC;

  country_clean   n raw_forms_observed
0            US  54          [ US, US]
1            IN  35           [IN, in]
2            BR  24           [BR, br]
3            UK  21          [UK, UK ]
4                 4          [  ,    ]

If users is empty, the query returns no rows — not a row with count=0. Wrap in a sentinel CTE if downstream needs a deterministic shape (e.g., a daily metrics job that UNIONs yesterday with today). Self-contained version below.

-- Empty-safe roll-up: always returns at least one row
WITH base AS (
  SELECT country, COUNT(*) AS n
  FROM users WHERE 1=0    -- simulates an empty source for demo
  GROUP BY country
)
SELECT country, n FROM base
UNION ALL
SELECT '<empty_table>', 0 WHERE NOT EXISTS (SELECT 1 FROM base);

         country  n
0  <empty_table>  0

COUNT(*) counts every row including those with NULL country. COUNT(country) skips NULLs. COUNT(DISTINCT country) also skips NULLs. The difference between the first two is your NULL count for free. Sentinel collision warning: in the output below, distinct_incl_null_unsafe equals distinct_excl_null — surprising at first, until you notice the dirty data already contains a literal string '<NULL>', so a COALESCE(col, '<NULL>') sentinel collides with a real value and adds nothing. The portable, sentinel-free fix is to count NULL as +1 explicitly. Avoid CHR(0) as a sentinel: Postgres rejects NUL bytes in TEXT / VARCHAR columns with SQL Error [54000]: null character not permitted. DuckDB allows it; Postgres does not.

-- Section 1 — counts side-by-side, all portable across
-- Postgres / DuckDB / Snowflake / BigQuery.
SELECT
  COUNT(*)                                                  AS rows_total,
  COUNT(country)                                            AS rows_with_country,
  COUNT(*) - COUNT(country)                                 AS country_null_rows,
  COUNT(DISTINCT country)                                   AS distinct_excl_null,
  -- UNSAFE: this sentinel collides with the literal string '<NULL>' in the data
  COUNT(DISTINCT COALESCE(country, '<NULL>'))               AS distinct_incl_null_unsafe,
  -- SAFE: pick a sentinel obviously not in the domain. Long + unlikely.
  COUNT(DISTINCT COALESCE(country, '__NULL_SENTINEL_8f3a__')) AS distinct_incl_null_safe,
  -- BEST: no sentinel at all. Add 1 if any NULLs exist.
  COUNT(DISTINCT country)
    + CASE WHEN COUNT(*) > COUNT(country) THEN 1 ELSE 0 END AS distinct_incl_null_explicit
FROM users;

-- Section 2 — what ARE the distinct values (so you can SEE the mess).
-- Quote each value so empty strings and whitespace become visible.
SELECT
  COALESCE(country, '__NULL__')                              AS country_value_or_null_marker,
  '''' || COALESCE(country, '__NULL__') || ''''              AS country_quoted,
  COUNT(*)                                                   AS n_rows
FROM users
GROUP BY country
ORDER BY n_rows DESC, country_quoted;

   rows_total  rows_with_country  country_null_rows  distinct_excl_null  distinct_incl_null_unsafe  distinct_incl_null_safe  distinct_incl_null_explicit
0         200                193                  7                  15                         15                       16                           16

──── result 2 ────
   country_value_or_null_marker country_quoted  n_rows
0                            US           'US'      53
1                            IN           'IN'      33
2                            BR           'BR'      22
3                            FR           'FR'      20
4                            UK           'UK'      20
5                            JP           'JP'      18
..                          ...            ...     ...
10                           br           'br'       2
11                           in           'in'       2
12                                       '   '       1
13                           US          ' US'       1
14                       <NULL>       '<NULL>'       1
15                          UK           'UK '       1

Plain LIMIT 5 silently drops one of two tied salaries. Use FETCH FIRST 5 ROWS WITH TIES (Postgres 13+, Snowflake) or RANK() to make ties explicit.

-- Postgres 13+: keep all ties at the cutoff
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

-- Portable: filter by RANK
WITH ranked AS (
  SELECT *, RANK() OVER (ORDER BY salary DESC) AS rk FROM employees
)
SELECT * FROM ranked WHERE rk <= 5;

[error] ParserException: Parser Error: syntax error at or near "WITH"

LINE 3: FETCH FIRST 5 ROWS WITH TIES
                           ^

Postgres orders NULLs LAST in DESC by default; MySQL orders them FIRST. State your DB or be explicit with NULLS LAST.

SELECT * FROM employees
ORDER BY salary DESC NULLS LAST   -- portable across Postgres, Snowflake, BigQuery
LIMIT 5;

   employee_id  full_name  manager_id   department    salary
0          100  Dana Chen         NaN  Engineering  250000.0
1          201   Eve Park       100.0  Engineering  180000.0
2          202   Frank Li       100.0  Engineering  175000.0
3          315     IC_315       203.0  Engineering  161966.0
4          312     IC_312       203.0  Engineering  145797.0

COUNT(*) counts rows. COUNT(col) counts non-NULL values of col. SUM/AVG/MIN/MAX all skip NULLs entirely. The denominator surprise: AVG(col) drops NULL rows from both numerator AND denominator — not what you want if NULL means ‘real zero’. The all-NULL surprise: SUM(col) on a column where every row is NULL returns NULL, not 0. The dataset has 3 NULL amount rows after the dirty-data injection, so we can show the divergence side-by-side.

-- Every aggregate's NULL behavior, on the same data:
SELECT
  COUNT(*)                         AS rows_total,                 -- counts rows incl. NULL
  COUNT(amount)                    AS amount_non_null,            -- skips NULL
  COUNT(*) - COUNT(amount)         AS amount_null_rows,           -- diff = NULL count
  COUNT(DISTINCT amount)           AS distinct_amounts,           -- skips NULL too
  ROUND(SUM(amount), 2)            AS sum_amount,                 -- NULL-safe iff at least 1 non-null
  COALESCE(ROUND(SUM(amount),2),0) AS sum_amount_safe,            -- always a number
  ROUND(AVG(amount), 2)            AS avg_amount_pandas_default,  -- mean of NON-NULL only
  ROUND(SUM(amount) / COUNT(*), 2) AS avg_treating_null_as_zero,  -- different number!
  MIN(amount)                      AS min_amount,                 -- skips NULL
  MAX(amount)                      AS max_amount                  -- skips NULL
FROM purchases;

   rows_total  amount_non_null  amount_null_rows  distinct_amounts  sum_amount  sum_amount_safe  avg_amount_pandas_default  avg_treating_null_as_zero  min_amount  max_amount
0         301              298                 3               296   127603.25        127603.25                      428.2                     423.93     -228.72    99999.99

AVG returns NULL (not 0) on a partition where every row is NULL. If you guard with COALESCE(AVG(x), 0) you risk hiding the ‘no data at all’ signal in a real zero. Better: emit COUNT alongside, or use NULLIF / COALESCE deliberately with a label.

-- Per-category averages with explicit empty-handling
SELECT product_category,
       COUNT(*)                                    AS rows,
       COUNT(amount)                               AS rows_with_amount,
       AVG(amount)                                 AS avg_raw,
       COALESCE(AVG(amount), 0)                    AS avg_zero_filled,
       CASE WHEN COUNT(amount) = 0 THEN 'no_data'
            ELSE CAST(ROUND(AVG(amount),2) AS VARCHAR)
       END                                         AS avg_labeled
FROM purchases
GROUP BY product_category
ORDER BY rows DESC;

  product_category  rows  rows_with_amount      avg_raw  avg_zero_filled avg_labeled
0      Electronics    73                73    88.380822        88.380822       88.38
1         Clothing    64                64  1651.377344      1651.377344     1651.38
2             Food    59                59    87.792034        87.792034       87.79
3            Books    56                55   102.794909       102.794909      102.79
4           Sports    49                47    98.507447        98.507447       98.51

Both forms return the same number when the table has rows — SUM ignores NULLs either way. They diverge only on the empty input case:
• COALESCE(SUM(x), 0) applies the default to the aggregated result → returns 0 on empty (and NULL-only) tables.
• SUM(COALESCE(x, 0)) replaces NULL with 0 row-by-row before summing → returns 0 on a NULL-only table, but NULL on a truly empty input (SQL standard: SUM of zero rows is NULL).
Pick by use case: dashboards that must always show a number (KPIs) want the outside form. Per-row imputation (e.g., treating NULL price as 0 for ledger math) wants the inside form.

-- Side-by-side on three scenarios:
--   (a) the real table (has rows + 3 NULL amounts)
--   (b) NULL-only table  (all amount IS NULL)
--   (c) empty table      (zero rows)
WITH
  null_only AS (SELECT * FROM purchases WHERE 1=0
                UNION ALL SELECT 999, 1, NULL, '2025-01-01', 'demo'),
  empty_set AS (SELECT * FROM purchases WHERE 1=0)
SELECT 'real_table'  AS scenario,
       SUM(amount)                          AS sum_raw,
       COALESCE(SUM(amount), 0)             AS coalesce_outside,  -- final NULL -> 0
       SUM(COALESCE(amount, 0))             AS coalesce_inside    -- NULL row -> 0 then sum
FROM purchases
UNION ALL
SELECT 'null_only_table',
       SUM(amount), COALESCE(SUM(amount), 0), SUM(COALESCE(amount, 0))
FROM null_only
UNION ALL
SELECT 'empty_table',
       SUM(amount), COALESCE(SUM(amount), 0), SUM(COALESCE(amount, 0))
FROM empty_set;

          scenario    sum_raw  coalesce_outside  coalesce_inside
0       real_table  127603.25         127603.25        127603.25
1  null_only_table        NaN              0.00             0.00
2      empty_table        NaN              0.00              NaN

Storing amount as DOUBLE/FLOAT will lose cents at scale — SUM is non-associative on floats. The schema uses DECIMAL(10,2); confirm before reporting to finance.

-- SAFE: explicit DECIMAL cast prevents float drift
SELECT ROUND(SUM(amount)::DECIMAL(18,2), 2) AS revenue
FROM purchases
WHERE amount IS NOT NULL;

     revenue
0  127603.25

If revenue is a pre-aggregate, WHERE works. If you compute it in this query (SUM), you must use HAVING. Mixing them is a frequent interview trap.

-- Computed revenue: HAVING, not WHERE
SELECT product_category, SUM(amount) AS revenue
FROM purchases
GROUP BY product_category
HAVING SUM(amount) > 1000;

  product_category    revenue
0            Books    5653.72
1         Clothing  105688.15
2      Electronics    6451.80
3             Food    5179.73
4           Sports    4629.85

Always confirm '> 1000' vs '>= 1000'. Off-by-one at category boundaries silently changes the report.

Always emit COUNT alongside AVG. A platform with 5 sessions and a 10-second average looks identical to one with 50,000 sessions and the same average — but only one is statistically meaningful.

SELECT platform, COUNT(*) AS sessions,
       AVG(duration_sec) AS avg_dur,
       STDDEV(duration_sec) AS sd_dur,
       AVG(duration_sec) / SQRT(COUNT(*)) AS sem
FROM sessions GROUP BY platform;

  platform  sessions      avg_dur       sd_dur          sem
0  Android         2  2085.500000   713.470742  1474.671192
1  android       265  1678.973485  1011.429126   103.138521
2      web       198  1823.908629  1007.822862   129.619542
3      iOS         1  2691.000000          NaN  2691.000000
4      ios       329  1744.445122  1062.677389    96.174373
5      WEB         5  2907.600000   775.846828  1300.318250

A few 12-hour zombie sessions pull AVG up. Report median or a trimmed mean alongside.

SELECT platform,
       AVG(duration_sec) AS avg_dur,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_sec) AS p50_dur
FROM sessions GROUP BY platform;

  platform      avg_dur  p50_dur
0  Android  2085.500000   2085.5
1      WEB  2907.600000   3257.0
2  android  1678.973485   1557.5
3      web  1823.908629   1928.0
4      iOS  2691.000000   2691.0
5      ios  1744.445122   1719.5

AVG ignores NULL — but a negative duration_sec (corrupt event) is treated as a real value and pulls the mean down. The denominator surprise: NULL rows are excluded from both numerator and denominator. Always pre-filter to valid positives before averaging, AND emit a dropped count so the DQ trail is visible.

SELECT
  LOWER(platform)                                  AS platform,
  COUNT(*)                                         AS rows_total,
  COUNT(duration_sec)                              AS rows_with_duration,
  SUM(CASE WHEN duration_sec IS NULL THEN 1 END)   AS dropped_null,
  SUM(CASE WHEN duration_sec < 0    THEN 1 END)    AS dropped_negative,
  ROUND(AVG(duration_sec), 1)                      AS avg_raw,                    -- pulled down by negatives
  ROUND(AVG(CASE WHEN duration_sec >= 0
                  THEN duration_sec END), 1)       AS avg_clean                   -- valid positives only
FROM sessions
GROUP BY 1
ORDER BY rows_total DESC;

  platform  rows_total  rows_with_duration  dropped_null  dropped_negative  avg_raw  avg_clean
0      ios         330                 329           1.0               1.0   1747.3     1752.7
1  android         267                 266           1.0               1.0   1682.0     1688.4
2      web         203                 202           1.0               NaN   1850.7     1850.7

Postgres EXTRACT(DOW): Sun=0..Sat=6. ISODOW: Mon=1..Sun=7. MySQL DAYOFWEEK: Sun=1..Sat=7. Wrong constant = wrong answer.

-- Portable: name-based
SELECT COUNT(*) FROM sessions
WHERE strftime(session_date, '%w') IN ('0','6');  -- DuckDB / SQLite
-- Or: EXTRACT(ISODOW FROM session_date) IN (6, 7)  -- Postgres

   count_star()
0           224

If session_date is derived from a UTC timestamp, a 'weekend' for a Sydney user differs from one for an LA user. Convert to user-local TZ before bucketing.

On a 10B-row events table, SELECT DISTINCT does a full scan. Hit the documented enum/lookup table or a daily-cardinality summary.

-- Cheap on a billion rows: the catalog table
SELECT type FROM event_types ORDER BY type;

[error] CatalogException: Catalog Error: Table with name event_types does not exist!
Did you mean "events"?

LINE 2: SELECT type FROM event_types ORDER BY type
                         ^

DISTINCT is case-sensitive. 'SignUp' and 'signup' are two values; lowercase before grouping if upstream is dirty.

SELECT DISTINCT LOWER(event_type) FROM events ORDER BY 1;

  lower(event_type)
0        activation
1          purchase
2            signup
3               NaN

BETWEEN '2024-10-01' AND '2024-12-31' silently misses any midnight-on-Dec-31 timestamps. Half-open ranges (>= start AND < end) are timezone- and DST-safe.

-- WRONG when signup_date can be a TIMESTAMP
WHERE signup_date BETWEEN '2024-10-01' AND '2024-12-31'

-- RIGHT: half-open
WHERE signup_date >= DATE '2024-10-01'
  AND signup_date <  DATE '2025-01-01'

‘Q4 2024’ in PT vs UTC differs by 8 hours. State the report TZ and CONVERT/AT TIME ZONE if signup_date is UTC.

If two orders share the max amount, LIMIT 1 returns one non-deterministically. Use ROW_NUMBER with a deterministic tiebreak.

SELECT order_id, user_id, order_date, amount
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY amount DESC, order_id) AS rn
  FROM orders
) t
WHERE rn = 1;

   order_id  user_id order_date  amount
0       446      176 2025-02-25  958.35

If the table also stores refunds with negative amounts, MAX still returns the largest positive. But MIN may surprise: the ‘biggest refund’ is the most negative.

MAX(session_date) - 6 days might span days with zero sessions. Date-spine left-join if you want exactly 7 daily rows.

WITH spine AS (
  SELECT (SELECT MAX(session_date) FROM sessions) - INTERVAL n DAY AS d
  FROM range(0, 7) t(n)
)
SELECT s.d, COUNT(x.session_id) AS sessions
FROM spine s
LEFT JOIN sessions x ON x.session_date = s.d
GROUP BY s.d ORDER BY s.d;

[error] ParserException: Parser Error: syntax error at or near "DAY"

LINE 2: ...  SELECT (SELECT MAX(session_date) FROM sessions) - INTERVAL n DAY AS d
                                                                          ^

CURRENT_DATE returns the server's date — usually UTC, sometimes the cluster region. Use AT TIME ZONE to report in the dashboard's TZ.

If the subquery returns any NULL, NOT IN evaluates to UNKNOWN for every row — the result set becomes empty. Anti-join via LEFT JOIN + IS NULL or NOT EXISTS is NULL-safe.

-- DANGEROUS
SELECT * FROM users WHERE user_id NOT IN (SELECT user_id FROM purchases);

-- SAFE
SELECT u.* FROM users u
LEFT JOIN purchases p ON p.user_id = u.user_id
WHERE p.purchase_id IS NULL;

    user_id      name                email signup_date country
0         1    user_1    user1@example.com  2024-10-02      IN
1         2    user_2    user2@example.com  2024-10-04      UK
2         4    user_4    user4@example.com  2024-10-08      JP
3         8    user_8    user8@example.com  2024-10-15      US
4        10   user_10   user10@example.com  2024-10-19      IN
5        12   user_12   user12@example.com  2024-10-23      \N
..      ...       ...                  ...         ...     ...
69      190  user_190  user190@example.com  2025-10-15      FR
70      193  user_193  user193@example.com  2025-10-21      US
71      194  user_194  user194@example.com  2025-10-23      UK
72      195  user_195  user195@example.com  2025-10-25      BR
73      197  user_197  user197@example.com  2025-10-28      US
74      199  user_199  user199@example.com  2025-11-02      US

──── result 2 ────
    user_id      name                email signup_date country
0         1    user_1    user1@example.com  2024-10-02      IN
1         2    user_2    user2@example.com  2024-10-04      UK
2         4    user_4    user4@example.com  2024-10-08      JP
3         8    user_8    user8@example.com  2024-10-15      US
4        10   user_10   user10@example.com  2024-10-19      IN
5        12   user_12   user12@example.com  2024-10-23      \N
..      ...       ...                  ...         ...     ...
69      190  user_190  user190@example.com  2025-10-15      FR
70      193  user_193  user193@example.com  2025-10-21      US
71      194  user_194  user194@example.com  2025-10-23      UK
72      195  user_195  user195@example.com  2025-10-25      BR
73      197  user_197  user197@example.com  2025-10-28      US
74      199  user_199  user199@example.com  2025-11-02      US

If users has a deleted_at column, you might be sending a coupon to a churned account. Filter the audience BEFORE the anti-join.

Three-valued logic strikes again: NULL != 'US' evaluates to UNKNOWN, not TRUE — so users with NULL country drop out of the audience silently. If you actually want ‘not US, including unknowns’, use IS DISTINCT FROM (Postgres / DuckDB) or an explicit OR. Side-by-side counts show the gap.

-- All three answers in one row, so the gap is unmissable
SELECT
  SUM(CASE WHEN country != 'US'                    THEN 1 ELSE 0 END) AS not_us_naive,        -- drops NULL
  SUM(CASE WHEN country IS DISTINCT FROM 'US'      THEN 1 ELSE 0 END) AS not_us_safe,         -- NULL-safe
  SUM(CASE WHEN country != 'US' OR country IS NULL THEN 1 ELSE 0 END) AS not_us_explicit_or,  -- same idea
  SUM(CASE WHEN country IS NULL                    THEN 1 ELSE 0 END) AS gap_explained        -- = safe - naive
FROM users;

   not_us_naive  not_us_safe  not_us_explicit_or  gap_explained
0         140.0        147.0               147.0            7.0

INNER JOIN gives the avg only for purchasing countries. LEFT JOIN with AVG gives NULL for non-purchasers — easy to mis-read as 0.

-- LEFT JOIN, COALESCE for clarity
SELECT u.country,
       COALESCE(ROUND(AVG(p.amount), 2), 0) AS avg_basket,
       COUNT(p.purchase_id)                  AS purchases
FROM users u
LEFT JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.country;

   country  avg_basket  purchases
0       JP       79.99         23
1      NaN      135.66          8
2       FR      108.38         31
3   <NULL>        6.21          1
4       BR       99.58         28
5                34.99          4
..     ...         ...        ...
10      US        3.62          1
11                0.00          0
12     UK        27.49          2
13      UK       65.22         29
14      in      147.87          4
15      DE       84.08         13

Always emit purchase count alongside average. A 1-purchase country with avg $999 looks identical to a 1,000-purchase country with avg $999.

user_profiles intentionally has 334 rows for 200 users (Q22's dedup target). NOT EXISTS still works — it short-circuits — but if you switched to LEFT JOIN, you must SELECT DISTINCT.

-- LEFT JOIN form needs DISTINCT
SELECT DISTINCT u.user_id, u.name
FROM users u
LEFT JOIN user_profiles up ON up.user_id = u.user_id
WHERE up.user_id IS NULL;

Empty DataFrame
Columns: [user_id, name]
Index: []

If user_profiles has rows with NULL user_id (corrupt loads), they do not match anything and don't pollute the anti-join — but they are a DQ bug worth alerting on separately.

If orders.user_id can reference a missing users row, INNER JOIN drops them; LEFT JOIN keeps them but the user name is NULL. State which behavior the dashboard wants.

SELECT u.name,
       SUM(o.amount) FILTER (WHERE u.user_id IS NOT NULL) AS spend_known,
       SUM(o.amount) FILTER (WHERE u.user_id IS NULL)     AS spend_orphan
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id;

[error] BinderException: Binder Error: column "name" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(name)" if the exact value of "name" is not important.

LINE 1: SELECT u.name,
               ^

If amount can be negative (refunds), SUM is the net. Some teams report gross + refund separately; clarify before coding.

INNER JOIN drops the CEO (manager_id IS NULL). LEFT JOIN keeps them but their manager column is NULL — which the export must handle, not crash.

SELECT e.full_name AS employee,
       COALESCE(m.full_name, '<no manager>') AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

     employee       manager
0    Eve Park     Dana Chen
1    Frank Li     Dana Chen
2   Grace Kim     Dana Chen
3      IC_301      Eve Park
4      IC_302      Eve Park
5      IC_303      Eve Park
..        ...           ...
22     IC_320        IC_302
23     IC_321        IC_302
24     IC_322        IC_306
25     IC_323        IC_306
26     IC_324        IC_306
27  Dana Chen  <no manager>

If a manager has been terminated and the row removed, the report shows NULL manager. Real HRIS systems keep terminated employees with a deleted_at flag for exactly this reason.

A new senior IC may legitimately out-earn their L4 manager. Don't auto-flag; surface the pair for HRBP review with comp band context.

SELECT e.full_name AS report,  e.salary, e.department,
       m.full_name AS manager, m.salary,
       (e.salary - m.salary) AS report_premium
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY report_premium DESC;

   report    salary   department manager  salary_1  report_premium
0  IC_317  131077.0  Engineering  IC_301  116962.0         14115.0
1  IC_316  121279.0  Engineering  IC_301  116962.0          4317.0

Salaries should be normalized to FTE / annualized USD before comparison. A 0.6-FTE manager out-earning a 1.0-FTE report is expected, not a flag.

‘Top by revenue’ can mean SUM(amount) (gross), or SUM(amount * (1 - return_rate)) (net), or COUNT * AVG (units × ASP). Confirm before writing.

LIMIT 3 drops one of two tied categories at #3. Use DENSE_RANK if the carousel can show ties.

WITH ranked AS (
  SELECT product_category, SUM(amount) AS revenue,
         DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM purchases GROUP BY product_category
)
SELECT * FROM ranked WHERE rk <= 3;

  product_category    revenue  rk
0         Clothing  105688.15   1
1      Electronics    6451.80   2
2            Books    5653.72   3

INTERSECT in standard SQL is set semantics — duplicates collapse. INTERSECT ALL preserves duplicates. Check your DB; not every engine implements ALL.

‘Active in events AND logins’ needs a window — last 7 days? Lifetime? The intersection definition is meaningless without one. Add a date filter.

SELECT user_id FROM events  WHERE event_date >= CURRENT_DATE - INTERVAL '7' DAY
INTERSECT
SELECT user_id FROM logins  WHERE login_date >= CURRENT_DATE - INTERVAL '7' DAY;

Empty DataFrame
Columns: [user_id]
Index: []

‘Conversion’ is undefined without a window: ever, first 7 days, first 30 days, first session? State which.

-- 30-day-window conversion (more honest than ‘ever’)
SELECT u.country,
       100.0 * COUNT(DISTINCT p.user_id) FILTER (
           WHERE p.purchase_date <= u.signup_date + INTERVAL '30' DAY
       ) / NULLIF(COUNT(DISTINCT u.user_id), 0) AS conv_30d_pct
FROM users u
LEFT JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.country;

   country  conv_30d_pct
0   <NULL>    100.000000
1              66.666667
2       JP     61.111111
3      NaN     57.142857
4       US     62.264151
5       IN     54.545455
..     ...           ...
10      BR     50.000000
11      in    100.000000
12     UK     100.000000
13      FR     60.000000
14      UK     40.000000
15      DE     71.428571

DE with 8 users gives a 100% conversion that is statistically meaningless. Add a min_cohort filter for the chart, but report raw counts for completeness.

If your SCD2 builder has a bug, two rows can have is_current='Y' for the same product. Add an assert.

-- DQ: must be 1 current row per product
SELECT product_id, COUNT(*) AS current_rows
FROM product_dim
WHERE is_current = 'Y'
GROUP BY product_id
HAVING COUNT(*) > 1;

   product_id  current_rows
0          11             2
1          19             2

It is a maintained flag — easily out of sync with effective/expiry. For correctness-critical queries, derive 'current' from expiry_date IS NULL OR expiry_date > CURRENT_DATE.

If three users tie at #1: RANK gives 1,1,1,4; DENSE_RANK gives 1,1,1,2; ROW_NUMBER gives 1,2,3 non-deterministically. Pick by downstream consumer.

SELECT user_id, total_spend,
       RANK()       OVER (ORDER BY total_spend DESC) AS r,
       DENSE_RANK() OVER (ORDER BY total_spend DESC) AS dr,
       ROW_NUMBER() OVER (ORDER BY total_spend DESC, user_id) AS rn
FROM (SELECT user_id, SUM(amount) AS total_spend FROM orders GROUP BY 1) t;

     user_id  total_spend    r   dr   rn
0        176      1445.05    1    1    1
1         70      1337.78    2    2    2
2        168      1117.62    3    3    3
3        112       971.71    4    4    4
4         36       952.59    5    5    5
5        140       808.65    6    6    6
..       ...          ...  ...  ...  ...
178       15        13.34  179  179  179
179       22         9.74  180  180  180
180       41         8.39  181  181  181
181       71         7.88  182  182  182
182      183         3.41  183  183  183
183       25         3.16  184  184  184

If you LEFT JOIN to keep all users, SUM is NULL. ORDER BY DESC places NULLs last in Postgres but FIRST in MySQL. Be explicit.

Without a secondary tiebreak, two profile rows with identical updated_at produce a coin-flip dedup. Add a deterministic secondary key (e.g., city DESC, primary key DESC).

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY updated_at DESC, city DESC, ROWID DESC   -- always deterministic
) AS rn

If a partition has only NULL update timestamps, ROW_NUMBER still assigns rn=1 to one row arbitrarily. Decide: drop the partition, or pick by another field.

LAG returns NULL on the first row, and LAG/0 is undefined — NULLIF the divisor or COALESCE the result, depending on whether downstream wants NULL or 0.

SELECT week_start, wau,
       100.0 * (wau - LAG(wau) OVER w)
             / NULLIF(LAG(wau) OVER w, 0) AS wow_pct
FROM weekly_metrics
WINDOW w AS (ORDER BY week_start);

   week_start    wau   wow_pct
0  2024-01-01  10000       NaN
1  2024-01-08  10230  2.300000
2  2024-01-15  10455  2.199413
3  2024-01-22  10819  3.481588
4  2024-01-29  10434 -3.558554
5  2024-02-05  10972  5.156220
..        ...    ...       ...
46 2024-11-18  15532 -1.296390
47 2024-11-25  15750  1.403554
48 2024-12-02  16883  7.193651
49 2024-12-09  16215 -3.956643
50 2024-12-16  15425 -4.872032
51 2024-12-23  15680  1.653160

LAG is positional, not date-aware: if week 2024-01-08 is missing, LAG jumps from 01-01 to 01-15 silently. Date-spine first.

If a new platform ('windows') ships, the CASE pivot silently drops those rows from the named columns. Add a 'other' bucket and an alert when total != iOS+Android+Web+other.

SELECT session_date,
   SUM(CASE WHEN platform='ios'     THEN 1 ELSE 0 END) AS ios,
   SUM(CASE WHEN platform='android' THEN 1 ELSE 0 END) AS android,
   SUM(CASE WHEN platform='web'     THEN 1 ELSE 0 END) AS web,
   SUM(CASE WHEN platform NOT IN ('ios','android','web') THEN 1 ELSE 0 END) AS other,
   COUNT(*) AS total
FROM sessions GROUP BY session_date;

   session_date  ios  android  web  other  total
0    2025-01-22  4.0      3.0  5.0    0.0     12
1    2025-02-13  7.0      4.0  1.0    0.0     12
2    2025-01-20  2.0      5.0  4.0    1.0     12
3    2025-01-25  7.0      1.0  4.0    0.0     12
4    2025-01-30  4.0      6.0  2.0    0.0     12
5    2025-02-07  5.0      3.0  3.0    1.0     12
..          ...  ...      ...  ...    ...    ...
62   2025-02-11  2.0      5.0  5.0    0.0     12
63   2025-02-19  5.0      3.0  4.0    0.0     12
64   2025-02-20  3.0      7.0  1.0    0.0     11
65   2025-03-01  5.0      3.0  4.0    0.0     12
66   2025-01-18  3.0      6.0  3.0    0.0     12
67   2025-01-31  7.0      2.0  3.0    0.0     12

'iOS' vs 'ios' fails the equality test. Lowercase upstream or in the CASE comparator.

DENSE_RANK gives 1,2,2,3 — three rows survive. RANK gives 1,2,2,4 — two rows survive (no #3). Pick semantically.

A category with 2 products returns 2 rows, not 3. The carousel must handle short-shelf gracefully.

-- Pad short shelves with NULL placeholders
SELECT category, position, product_name
FROM (
  SELECT category, ROW_NUMBER() OVER (PARTITION BY category
                                      ORDER BY revenue DESC) AS position,
         product_name
  FROM products
) t
WHERE position <= 3;

       category  position product_name
0        Sports         1      prod_40
1        Sports         2      prod_36
2        Sports         3      prod_38
3         Books         1      prod_19
4         Books         2      prod_11
5         Books         3      prod_15
..          ...       ...          ...
9      Clothing         1      prod_28
10     Clothing         2      prod_30
11     Clothing         3      prod_29
12  Electronics         1       prod_4
13  Electronics         2       prod_1
14  Electronics         3       prod_5

PERCENTILE_CONT(0.5) interpolates between the two middle rows; PERCENTILE_DISC(0.5) returns one of them. For currency, CONT is usually wrong (it produces fractions of a cent).

-- DISC for whole-cent values
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;

   quantile_disc(0.5 ORDER BY amount)
0                               51.52

MySQL 8 lacks WITHIN GROUP. Use the ROW_NUMBER + COUNT trick from the answer block.

If a user has two orders with the same max amount, ROW_NUMBER picks one arbitrarily. Tiebreak by order_date and order_id deterministically.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY amount DESC, order_date DESC, order_id  -- fully deterministic
) AS rn

Inner-style filtering drops them. If the email needs every user, left-join from users and COALESCE the subject line.

Subtle but real: CUME_DIST(x) = fraction of rows ≤ x; PERCENT_RANK(x) = (rank(x)-1)/(N-1). At the median they differ.

Two users with identical spend can land in different deciles. For high-stakes segmentation, use PERCENT_RANK ≤ 0.10 instead.

SELECT user_id, total_spend,
       PERCENT_RANK() OVER (ORDER BY total_spend DESC) AS pr
FROM (SELECT user_id, SUM(amount) AS total_spend FROM orders GROUP BY 1) t
WHERE PERCENT_RANK() OVER (ORDER BY total_spend DESC) <= 0.10;

[error] BinderException: Binder Error: WHERE clause cannot contain window functions!

LINE 4: WHERE PERCENT_RANK() OVER (ORDER BY total_spend DESC) <= 0.10
              ^

AVG over a partition of 1 equals that one value, so delta is 0. Filter to users with >= some min order count for anomaly logic.

WITH stats AS (
  SELECT order_id, user_id, amount,
         AVG(amount) OVER (PARTITION BY user_id) AS u_avg,
         COUNT(*)    OVER (PARTITION BY user_id) AS u_n
  FROM orders
)
SELECT * FROM stats WHERE u_n >= 5 AND amount > u_avg * 2;

    order_id  user_id  amount       u_avg  u_n
0        208       99  347.94  107.280000    5
1        381      111  305.84  100.684000    5
2        298       17  280.68  106.044000    5
3         14      151  318.91  116.080000    5
4        384      161   97.23   44.912000    5
5        446      176  958.35  180.631250    8
..       ...      ...     ...         ...  ...
13       382       47  281.25  130.128000    5
14       419      156  236.33   98.251667    6
15       494      156  202.47   98.251667    6
16       128       62  128.41   44.758000    5
17       445       96  365.97  107.082000    5
18       238      155  133.38   65.010000    6

Linear delta over-flags whales' big-but-normal orders. Use log(amount) - log(u_avg) for ratio thinking.

A user firing two 'signup' events is dirty data. NOT EXISTS still works (set-based) but you should DQ-flag it separately.

SELECT user_id, COUNT(*) AS signup_events
FROM events WHERE event_type = 'signup'
GROUP BY user_id HAVING COUNT(*) > 1;

   user_id  signup_events
0        5              2
1        2              2
2        4              2
3        1              2
4        3              2

Lifetime ‘never’ is a different cohort from ‘not within 30 days of signup’. State the window.

ROWS BETWEEN 6 PRECEDING counts rows, regardless of date gaps. If a day is missing, your ‘7-day’ window actually spans 8 calendar days. RANGE BETWEEN INTERVAL '6' DAY is date-aware.

SELECT ds, daily_revenue,
       AVG(daily_revenue) OVER (
           ORDER BY ds
           RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
       ) AS rev_7d_avg
FROM daily_metrics;

            ds  daily_revenue    rev_7d_avg
0   2025-01-01       50008.19  50008.190000
1   2025-01-02       79294.66  64651.425000
2   2025-01-03       55245.52  61516.123333
3   2025-01-04       51104.66  58913.257500
4   2025-01-05       55714.88  58273.582000
5   2025-01-06       80359.13  61954.506667
..         ...            ...           ...
115 2025-04-25       71673.07  46890.241429
116 2025-04-26       82002.86  49454.362857
117 2025-04-27       51165.87  52132.301429
118 2025-04-28       63400.18  55192.535714
119 2025-04-29       52785.14  57301.432857
120 2025-04-30       55986.60  61164.007143

The first 6 days have fewer than 7 rows in the window — the average is over those few rows, which is misleading at the trend head. Either return NULL until 7 rows are available, or label with the actual sample size.

If a backfill writes a row dated 2024-12-15 today, the MTD for December changes retroactively. Snapshot the curated table or your dashboard becomes a moving target.

DATE_TRUNC('month', ds) is in the server's TZ. For a US-based team, an order at 2024-12-31T23:30 PT is December but the UTC row is January.

SELECT DATE_TRUNC('month', ds AT TIME ZONE 'America/Los_Angeles') AS m_pt,
       SUM(daily_revenue)
FROM daily_metrics GROUP BY 1;

                       m_pt  sum(daily_revenue)
0 2025-03-01 00:00:00+00:00          1470069.99
1 2025-01-01 00:00:00+00:00          1588729.72
2 2025-04-01 00:00:00+00:00          1542605.98
3 2025-02-01 00:00:00+00:00          1327662.03

DATE_TRUNC('week', ...) snaps to Monday in ISO mode but Sunday in MySQL. A user signing up 2024-01-07 (Sun) lands in different cohorts depending on engine. State the convention.

If a user logs in before their signup_date (impossible? but stitching IDs creates this), weeks_since goes negative. Filter WHERE weeks_since >= 0 explicitly.

Without a monotonicity guard, a user with activation_at < signup_at counts as activated. Add the >= guard or your funnel rate exceeds 100% in some weeks.

COUNT(*) FILTER (WHERE activation_at IS NOT NULL
                   AND activation_at >= signup_at) AS step2

What if a user purchases without an activation event? Two valid definitions: strict (must hit each step) vs lax (any later step counts). Confirm with PM before coding.

generate_series from MIN to MAX of the existing data. If the report wants Jan 1 → today, anchor the spine to those literal dates instead.

WITH spine AS (
  SELECT CAST(d AS DATE) AS ds
  FROM range(DATE '2025-01-01', CURRENT_DATE + INTERVAL 1 DAY, INTERVAL 1 DAY) t(d)
)
SELECT s.ds, COALESCE(g.daily_revenue, 0) AS rev
FROM spine s LEFT JOIN daily_metrics_gapped g USING (ds);

            ds       rev
0   2025-01-01  50008.19
1   2025-01-02  79294.66
2   2025-01-03  55245.52
3   2025-01-04  51104.66
4   2025-01-06  80359.13
5   2025-01-07  38076.88
..         ...       ...
476 2026-02-10      0.00
477 2026-02-18      0.00
478 2026-02-20      0.00
479 2026-02-26      0.00
480 2026-04-10      0.00
481 2026-04-12      0.00

0 is appropriate for revenue/sessions (no activity); ffill is appropriate for stocks/balances (state persists). Pick by metric semantics.

30 minutes is canonical for web; mobile push notifications can reasonably use 5 minutes; video heartbeats can use 90 seconds. Threshold belongs to the product, not the SQL.

Late-arriving events with timestamps before their predecessor produce negative gaps. ORDER BY event_time correctly, but a session_start AFTER session_end is a DQ flag.

-- DQ check: surface inverted sessions
SELECT user_id, session_id, MIN(event_time) AS s_start, MAX(event_time) AS s_end
FROM sessionized
GROUP BY 1, 2
HAVING MIN(event_time) > MAX(event_time);

[error] CatalogException: Catalog Error: Table with name sessionized does not exist!
Did you mean "sessions"?

LINE 3: FROM sessionized
             ^

If the source has two login rows for the same user on the same day, the row_number trick over-counts the streak length. DEDUP first.

-- DEDUP before the gaps-and-islands trick
WITH dedup AS (SELECT DISTINCT user_id, login_date FROM logins)
SELECT user_id, login_date FROM dedup;

      user_id login_date
0           1 2025-03-06
1           1 2025-03-28
2           2 2025-03-05
3           2 2025-03-09
4           3 2025-03-07
5           4 2025-03-19
...       ...        ...
1919       98 2025-03-17
1920       98 2025-03-26
1921       98 2025-03-29
1922       99 2025-03-08
1923       99 2025-03-10
1924       99 2025-03-14

Two logins at 23:30 and 00:30 UTC look like two consecutive days in UTC but the same day for a Pacific user. Bucket login_date in user-local TZ if available.

A heavy hot key may be one value (hot_key) but skew can also happen on (key, time) — peak-hour traffic on one merchant. Group by (join_key, hour) for time-aware skew.

‘Top 1% holds 50%’ is one heuristic; ‘any single key > partition target size’ is the operational one. Compute against your shuffle target.

AVG(DAU) on a 7-day window counts a user multiple times if they appear on multiple days; WAU is unique users in the week. Both are valid; both are different. Label your axis.

-- Real WAU
SELECT DATE_TRUNC('week', login_date) AS week_start,
       COUNT(DISTINCT user_id)        AS wau
FROM logins GROUP BY 1;

  week_start  wau
0        NaT    3
1 2025-03-10  100
2 2025-03-03  100
3 2025-03-24  100
4 2025-02-24   83
5 2025-03-17  100

The first and last week may not have 7 full days. Drop or label them, don't average them silently.

A 90-day window has 13 Mondays and 12 Sundays — averaging without disclosing days_observed makes the rank misleading.

Black Friday is a Friday; Christmas falls mid-week. Day-of-week ranking averages everything together. Slice by promo period for honest weekday comparison.

If two product_dim rows have overlapping [effective, expiry) for the same product, the as-of join returns multiple rows per purchase — the SCD2 invariant is broken. DQ-assert on every load.

-- DQ: detect overlapping windows
WITH g AS (
  SELECT product_id, effective_date, expiry_date,
         LEAD(effective_date) OVER (
             PARTITION BY product_id ORDER BY effective_date
         ) AS next_eff
  FROM product_dim
)
SELECT * FROM g
WHERE expiry_date IS NOT NULL AND next_eff < expiry_date;

   product_id effective_date expiry_date   next_eff
0          13     2024-01-01  2024-03-11 2024-01-16
1          13     2024-01-16  2024-05-14 2024-03-11

[effective_date, expiry_date) (half-open) is industry standard and avoids double-coverage at the boundary. expiry_date IS NULL = still current. Always document the convention.

A purchase dated before any product_dim row leaves the as-of join NULL. Either bootstrap a synthetic 'genesis' row (effective_date = -infinity) or DQ-flag.

If A reports to B and B reports to A (data error), the recursive CTE loops forever. Add a depth cap and a visited-set guard.

WITH RECURSIVE org AS (
    SELECT employee_id, manager_id, full_name, 0 AS depth, '/' || full_name AS path
    FROM employees WHERE manager_id IS NULL
  UNION ALL
    SELECT e.employee_id, e.manager_id, e.full_name, o.depth + 1,
           o.path || '/' || e.full_name
    FROM employees e JOIN org o ON e.manager_id = o.employee_id
    WHERE o.depth < 20                 -- cycle guard
      AND POSITION('/' || e.full_name || '/' IN o.path) = 0  -- visited check
)
SELECT * FROM org;

    employee_id  manager_id  full_name  depth                               path
0           100         NaN  Dana Chen      0                         /Dana Chen
1           201       100.0   Eve Park      1                /Dana Chen/Eve Park
2           202       100.0   Frank Li      1                /Dana Chen/Frank Li
3           203       100.0  Grace Kim      1               /Dana Chen/Grace Kim
4           301       201.0     IC_301      2         /Dana Chen/Eve Park/IC_301
5           302       201.0     IC_302      2         /Dana Chen/Eve Park/IC_302
..          ...         ...        ...    ...                                ...
22          319       302.0     IC_319      3  /Dana Chen/Eve Park/IC_302/IC_319
23          320       302.0     IC_320      3  /Dana Chen/Eve Park/IC_302/IC_320
24          321       302.0     IC_321      3  /Dana Chen/Eve Park/IC_302/IC_321
25          322       306.0     IC_322      3  /Dana Chen/Frank Li/IC_306/IC_322
26          323       306.0     IC_323      3  /Dana Chen/Frank Li/IC_306/IC_323
27          324       306.0     IC_324      3  /Dana Chen/Frank Li/IC_306/IC_324

If two employees have manager_id IS NULL (CEO + acting CEO), the recursive CTE starts from both and you get two trees. State the intent.

The seed step puts (root_id, root_id) into descendants — the root counts itself. Subtract 1 in the final aggregate or filter self out in the seed.

Postgres default cycle protection is off; SQL Server has MAXRECURSION 100 default. For deep orgs (>100 levels — rare but real), increase the cap explicitly.

An empty cohort_week still appears in the join; the percentage is 0/0. Filter cohort_n > 0 or NULLIF the divisor.

Cohorts near the data edge have not had time to be observed at W3. Either mark them as incomplete (W3 = NULL) or compute only the cohorts with full observation.

-- Only fully-observed cohorts
WHERE cohort_week + INTERVAL '3' WEEK <= (SELECT MAX(login_date) FROM logins)

‘New today’ could mean ‘first ever’ or ‘first this month’ or ‘first since subscription’. Three valid metrics. Pick one and label.

If users log in from multiple devices and you stitch IDs, MIN(login_date) per stitched user differs from MIN per device ID. The stitching pipeline ordering matters.

If users has a deleted_at column and you filter deleted_at IS NULL, every order from a churned account becomes an orphan. Distinguish deleted vs never-existed.

SELECT o.order_id, o.user_id,
       CASE WHEN u.user_id IS NULL          THEN 'never_existed'
            WHEN u.deleted_at IS NOT NULL   THEN 'soft_deleted'
       END AS orphan_kind
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id
WHERE u.user_id IS NULL OR u.deleted_at IS NOT NULL;

[error] BinderException: Binder Error: Values list "u" does not have a column named "deleted_at"

LINE 7: WHERE u.user_id IS NULL OR u.deleted_at IS NOT NULL
                                   ^

Many orgs have test users with user_id in a special range. Filter them OUT of orphan checks AND in business metrics.

Storing amount as DOUBLE makes SUM non-associative; reconciling with =, even rounded, drifts. Use DECIMAL or convert to integer cents before summing.

If purchases excludes refunds but daily_revenue is net, your reconciliation will always show a gap equal to refund volume. Same definition on both sides or document the gap.

Postgres STDDEV with one row returns NULL (sample variance); amount > u_avg + 3 * NULL is NULL, so no rows flagged. Filter u_n >= 5 (or use STDDEV_POP).

Order amounts are heavy-tailed. 3σ on raw amount mis-flags. Better: log-transform first, or use IQR-based fences (Q1 - 1.5×IQR).

-- IQR-based outlier
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) FROM orders)
              + 1.5 * (
              SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount)
                   - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount)
              FROM orders);

    order_id  amount
0         14  318.91
1         15  890.88
2         19  245.36
3         38  450.92
4         49  308.07
5         52  371.89
..       ...     ...
39       424  404.41
40       434  425.90
41       444  251.34
42       445  365.97
43       446  958.35
44       448  919.86

NTILE distributes ties arbitrarily, so two users with the same spend can land in different deciles. PERCENT_RANK is tie-respecting.

If a user has orders but no sessions (or vice versa), they drop out of the inner join. State whether the segment requires both signals or one.

daily_dau may count any login event; orders.user_id COUNT(DISTINCT) counts purchasing users only. Different denominators ⇒ different ARPUs. Reconcile definitions first.

Float drift makes exact 0 unrealistic. Define a tolerance (e.g., abs(delta)/curated < 0.001) and alert above it.

SELECT *, ABS(arpu_delta) / NULLIF(arpu_curated, 0) AS pct_drift
FROM reconciled
WHERE ABS(arpu_delta) / NULLIF(arpu_curated, 0) > 0.001;

[error] CatalogException: Catalog Error: Table with name reconciled does not exist!
Did you mean "user_profiles"?

LINE 2: FROM reconciled
             ^

Without parse_dates, 'signup_date' is dtype=object (string). Every .dt call downstream raises AttributeError. Always parse at read time.

# WRONG: signup_date is now a string
users = pd.read_csv("users.csv")
users["signup_date"].dt.month   # AttributeError

# RIGHT
users = pd.read_csv("users.csv", parse_dates=["signup_date"])

If the CSV has both '2024-10-02' and '10/2/2024', auto-parse may fall back to dtype=object silently. Pass explicit format.

users = pd.read_csv("users.csv",
                    parse_dates=["signup_date"],
                    date_format="%Y-%m-%d")

Filtering returns a view sometimes and a copy other times. Without explicit .copy(), the next assignment may mutate the original — or warn and refuse.

# DANGEROUS
in_users = users[users["country"] == "IN"]
in_users["new_col"] = 1   # SettingWithCopyWarning

# SAFE
in_users = users.loc[users["country"] == "IN"].copy()

users[users.country == 'in'] returns 0 rows because matching is case-sensitive. Lowercase consistently or use str.casefold.

Default groupby sets the group as the index. For downstream merges and pivots you usually want as_index=False.

by_cat = (purchases.groupby("product_category", as_index=False)
                   .agg(revenue=("amount", "sum"),
                        n=("amount", "size")))

groupby drops NaN groups by default. If product_category can be NULL, pass dropna=False to surface them.

purchases.groupby("product_category", dropna=False, as_index=False).size()

nlargest excludes NaN by default. If you want NaN at the top (unusual but possible for outlier review), pass keep='all' and filter manually.

nlargest(10, 'amount', keep='first'|'last'|'all'). 'all' returns >10 rows when ties straddle the cutoff. Pick deliberately.

orders.nlargest(10, "amount", keep="all")

== NaN always returns False; use isna() / isnull() (synonyms in pandas). For object columns, also check for empty strings.

# Treat empty strings AS missing
mask_missing = up.isna() | (up.astype(object) == "")
print(mask_missing.sum())

datetime NaT, float NaN, and pd.NA in nullable Int64 are different types — but isna() catches all three.

Period('2024-12') < Period('2025-01') correctly. ‘2024-12’ < ‘2025-01’ correctly. But ‘2024-12’ < ‘25’ (wrong format) sorts lexically. Stick with Period for sorting; cast to string only for display.

If signup_date is tz-aware UTC, to_period('M') is in UTC. Convert to local TZ first if the cohort is reported locally.

If different chunks have different dtypes (e.g., user_id sometimes int sometimes float), pyarrow may upcast or fail. Coerce dtypes before writing.

in_users["user_id"] = in_users["user_id"].astype("int64")
in_users.to_parquet("/tmp/users_in.parquet", engine="pyarrow",
                    compression="snappy", index=False)

Snappy = fast read, decent ratio. Zstd = ~30% smaller but slower decode. For analytics on cold-storage, zstd is usually right; for hot tables, snappy.

By default value_counts excludes NaN. dropna=False surfaces it as a row — what you want for DQ.

sessions["platform"].value_counts(dropna=False)

‘iOS’ vs ‘ios’ counted separately. Lowercase upstream or before the count.

If revenue loaded as object (e.g., '1,234'), .sum() concatenates strings. Always check dtypes before summing currency.

# Defensive: strip commas, cast
products["revenue"] = (products["revenue"].astype(str)
                        .str.replace(",", "").astype("float64"))

Float sums of currency drift. For ledger-grade totals, store and sum as integer cents, then divide by 100 at display time.

On a billion-row table, .nunique() loads all distinct values into memory. HyperLogLog (datasketch) trades a tiny error for huge memory wins.

from datasketch import HyperLogLog
hll = HyperLogLog()
for u in logins["user_id"]: hll.update(str(u).encode())
print("approx unique:", hll.count())

If user_id loaded as float because of NaN rows, ‘1.0’ and ‘1’ are the same value but the float repr can confuse downstream string joins.

Without validate=, a duplicated key on either side multiplies rows silently. Always validate the relationship.

# Will raise if relationship is unexpectedly m:m
merged = users.merge(purchases, on="user_id", how="left",
                     validate="one_to_many")

If both frames have a 'name' column, pandas appends _x / _y silently — confusing. Set explicit suffixes.

merged = users.merge(profiles, on="user_id",
                     suffixes=("_user", "_profile"))

duplicated() defaults to keep='first', which marks ONLY the duplicates as True. keep=False marks all members of a duplicate group as True — what you want for inspection.

duplicated() on the full row vs subset=['user_id'] gives very different counts. Be explicit.

If you didn't sort by updated_at DESC, drop_duplicates keeps the wrong row silently. Always sort before drop_duplicates.

up_clean = (up.sort_values(["user_id", "updated_at"],
                           ascending=[True, False])
              .drop_duplicates(subset=["user_id"], keep="first"))
assert up_clean["user_id"].is_unique

If two rows share updated_at, drop_duplicates picks one arbitrarily. Add a deterministic secondary sort key.

Inner drops countries with no purchases. Left keeps them with NaN. Pick by the question (‘avg basket among purchasers’ vs ‘avg basket per country, NaN if none’).

Default drops NaN groups. dropna=False surfaces them — but merging on NaN keys is generally a DQ bug to fix upstream.

A print() in a pipeline becomes invisible. raise ValueError or log at ERROR level so the orchestrator marks the task failed.

bad = purchases[purchases["amount"] < 0]
if len(bad):
    raise ValueError(f"DQ failed: {len(bad)} negative amounts")

An empty purchases table satisfies ‘no negative amounts’ trivially — but is itself a DQ failure. Add an assert len(purchases) > 0 (or a freshness check).

pd.date_range returns a DatetimeIndex. .difference() preserves dtype and order; set(spine) - set(dmg.ds) returns a Python set (unordered, slower).

If ds is a Timestamp with non-zero time, the spine (midnight-anchored) doesn't match. Floor with .dt.normalize() first.

If the very first row is NaN, ffill leaves it NaN. Either left-pad with a default or accept the head-NaN semantics.

filled["daily_dau"] = filled["daily_dau"].ffill().fillna(0)

Plain .ffill() on a stacked frame leaks values across users. Use .groupby('user_id').ffill() if the data is multi-entity.

Computing mean & std INCLUDING the outliers makes the thresholds too lax. Use a robust stat (median + MAD) or iteratively trim before re-computing.

from scipy import stats
mad = stats.median_abs_deviation(orders["amount"], scale="normal")
median = orders["amount"].median()
robust_z = (orders["amount"] - median) / mad
outliers = orders[robust_z.abs() > 3.5]

If you compute z within tiny groups, std is NaN/0. Filter groups with at least N rows before flagging.

pd.to_datetime(s, errors='ignore') returns the original strings on parse failure — silent dtype regression. Use errors='raise' or 'coerce' (NaT) and DQ-flag.

cs["event_time"] = pd.to_datetime(cs["event_time"], errors="coerce")
bad = cs["event_time"].isna().sum()
if bad: raise ValueError(f"{bad} unparseable event_time rows")

Sessionization, gaps-and-islands, all need sorted order. reset_index after sort or downstream iloc lies.

Days with no sessions on one platform become NaN. Downstream .sum() may then propagate NaN unexpectedly.

If platform is dtype='category' with unobserved categories, the pivot includes empty columns. observed=True (groupby) or .cat.remove_unused_categories() to clean.

min_periods=1 avoids NaN at the head but produces 1-day ‘averages’ for the first 6 rows — misleading. min_periods=7 returns NaN until 7 rows accrue, which is the honest 7-day average.

dm["rev_7d"] = dm["daily_revenue"].rolling(window=7, min_periods=7).mean()

rolling(7) is trailing by default. For a smoothed trend chart, rolling(7, center=True) centers — but you cannot use it operationally (uses future data).

'W' defaults to Sunday. 'W-MON' ends on Monday. Mismatch with the warehouse week definition is a common source of metric drift.

First and last week may have <7 days. Resample sums NaN as 0 by default; the first/last week is undercounted. Drop or label.

weekly = dm.set_index("ds").resample("W-MON").agg(
    week_revenue=("daily_revenue", "sum"),
    days_in_week=("daily_revenue", "size"))
weekly = weekly[weekly["days_in_week"] == 7]   # drop partial weeks

.dt.to_period('M') is what you want for month labels. pd.Grouper(freq='M') works but resamples — different semantics if the index isn't sorted.

MTD that recomputes on every run isn't snapshotted. If a row for Dec 15 lands today, December's MTD numbers change. Snapshot or label as ‘rolling MTD’.

Pandas .rolling() doesn't support nunique. The naive list-comp is O(N×W) — fine for 365 days, awful for 4 years.

# Faster: deque + Counter
from collections import deque, Counter
window, days = deque(), 7
counter = Counter()
result = {}
for d, group in logins.groupby("login_date"):
    for u in group["user_id"]: counter[u] += 1
    window.append((d, set(group["user_id"])))
    while window and (d - window[0][0]).days >= days:
        old_d, old_users = window.popleft()
        for u in old_users:
            counter[u] -= 1
            if counter[u] == 0: del counter[u]
    result[d] = len(counter)

If the same human logs in from two devices, raw user_id double-counts in DAU. The identity-graph step belongs upstream.

.pct_change() can't compute change for the first observation — NaN is correct. Drop or label, don't fillna(0) (which falsely says ‘no growth’).

If wau was 0 last week, pct_change is inf. Guard with NULLIF logic or report as 'new growth' separately.

import numpy as np
wm["wow_pct"] = wm["wau"].pct_change().replace([np.inf, -np.inf], np.nan) * 100

first == last for one-purchase users ⇒ days_active = 0. ‘0 days lifespan’ vs ‘censored’ is a definition fight worth having.

If timestamps are UTC and the user is in HST, ‘day’ boundaries shift. .dt.tz_convert before .dt.date.

groupby + head(3) picks the first 3 by sort order, silently dropping ties. For shelf rotation that should keep all ties, use rank with method='dense' filtered by <= 3.

products["rk"] = (products.groupby("category")["revenue"]
                              .rank(method="dense", ascending=False))
top3 = products[products["rk"] <= 3]

Without a tiebreak in sort_values, two products with identical revenue swap order across runs. Add product_id as secondary.

If you forget the groupby in shift, the previous user's last event becomes the next user's prev_time. Always groupby user.

# CORRECT: per-user lag
prev = cs.groupby("user_id")["event_time"].shift()

30 minutes is canonical for web; mobile push 5 min; video 90s. The threshold is product knowledge, not a fixed constant.

Some pipelines emit signup on every onboarding step. groupby user_id + event_type with .min() collapses to first occurrence — what you want.

If a user purchases without an ‘activation’ event, strict ordering excludes them. Decide: strict (must hit each step) or any-later-step counts.

A signup from last week has at most W0 observed; W2 is literally impossible to compute. Mark as NaN or filter.

max_week = (logins["login_date"].max() - u["cohort_week"]) // pd.Timedelta(weeks=1)
# Only include cohorts with N+ weeks of full observation

.dt.to_period('W') uses Mon–Sun ISO week; the warehouse may use Sun–Sat. Mismatch ⇒ cohorts shifted by one day.

Two login rows for the same user/day inflate row_number, shifting the group_key. drop_duplicates on (user_id, login_date) first.

23:30 PT and 00:30 PT are the same day for a Pacific user but different days in UTC. Bucket login_date in user-local TZ.

Both sides must be sorted on the on= column. Sort first or merge_asof raises ValueError: right keys must be sorted.

pdim = pd_dim.sort_values("effective_date")
p    = purchases.sort_values("purchase_date")
priced = pd.merge_asof(p, pdim, by="product_id",
                       left_on="purchase_date",
                       right_on="effective_date",
                       direction="backward")

direction='nearest' can match a future row — wrong for as-of. Use 'backward' (last <= left) and add tolerance=pd.Timedelta(days=N) to bound staleness.

If you salt the fact table but not the dim, no salted key matches anything. The dim must be CROSS JOINed with the salt values.

salts = pd.DataFrame({"salt": range(N_SALTS)})
dim_salted = dim.merge(salts, how="cross")
dim_salted["join_key_salted"] = (dim_salted["join_key"]
                                 .str.cat(dim_salted["salt"].astype(str), sep="__"))

After the salted join, the final group-by must un-salt (strip the suffix) to get the real key. Forgetting this step is the most common skew-mitigation bug.

Recursive build() with a cycle recurses until RecursionError. Use iterative DFS or a visited set.

def build(node_id, visited=None):
    visited = visited or set()
    if node_id in visited:
        raise ValueError(f"cycle at {node_id}")
    visited = visited | {node_id}
    return {...}

If two employees have manager_id IS NULL, the tree has two roots. Decide whether the JSON is a list of trees or a synthetic single root with both as children.

Default quantile(0.5, interpolation='linear') interpolates between two middle rows — fractional cents result. Use 'lower' / 'higher' / 'midpoint' for currency.

med = (orders.groupby("user_id")["amount"]
              .quantile(0.5, interpolation="lower"))

n=1 partitions return that one value as the median. Filter to users with min order count if ‘typical’ basket is the goal.

If chunk 1 has user_id as int and chunk 2 has it as float (NaNs introduced), concat raises a dtype warning. Pre-declare dtypes via dtype= in read_csv.

for chunk in pd.read_csv(src, chunksize=200_000,
                         usecols=["page", "user_id"],
                         dtype={"user_id": "Int64", "page": "string"}):
    ...

If your aggregation needs ordering (sessionization, gaps), chunking by row count is wrong — chunks aren't time-aligned. Chunk by date instead.

Python's assert compiles out under -O optimization. Use raise ValueError(...) in production DQ checks.

if not df["order_id"].is_unique:
    raise ValueError("duplicate order_id")

The function checks dtypes and uniqueness. Real DQ also checks value ranges, freshness (max date), volume (row count not 0). Use Great Expectations / pandera for the full set.

to_parquet writing then crashing leaves a half-written file. Stage to a temp path and rename atomically, or use Delta/Iceberg.

tmp = out + ".tmp"
combined.to_parquet(tmp, index=False)
import os; os.replace(tmp, out)   # atomic on POSIX FS

Two ETL runs writing to the same Parquet at once corrupt each other. Use a per-batch path + a manifest file, or a lakehouse format with optimistic concurrency.

If updates arrive with change_date going backwards, the SCD2 build inserts a row that overlaps prior windows. Sort and validate before assigning effective/expiry.

Two updates with the same change_date for the same product produce a zero-width window — invariant violation. Dedup or flag.

NULL = current is conventional but the analytic query expiry_date > CURRENT_DATE excludes NULL rows (NULL > anything is NULL). Use COALESCE(expiry_date, '9999-12-31') or test IS NULL explicitly.

If [effective, expiry] is closed-closed, a fact dated exactly on the boundary matches BOTH rows. Standard is half-open [effective, expiry). Document and assert.

# Half-open semantics in the as-of join condition
mask = (fact["dt"] >= dim["effective"]) & (fact["dt"] < dim["expiry"].fillna(pd.Timestamp.max))

Spark's left_anti uses NULL-aware comparison: if the right key has NULL rows, they don't match, and the anti-join keeps all left rows. Confirm by counting matches before relying on it.

option('header', True) assumes headers; without inferSchema=True all columns are strings. user_id == ‘1’ vs 1 silently fails the join.

users_df = (spark.read
              .option("header", True)
              .option("inferSchema", True)
              .csv("users.csv"))

Window.orderBy(...) alone (no partitionBy) collapses all data to one task. For top-N per group, partition by the group key; for global top-N, accept the shuffle but consider limit first.

If two products tie at #2, dense_rank gives 1,2,2,3 (3 rows ≤ 3); row_number gives 1,2,3 deterministically. Same SQL tradeoff: pick by intent.

Spark auto-broadcasts under spark.sql.autoBroadcastJoinThreshold (default 10 MB). Above that, force with the broadcast() hint. Above ~200 MB, broadcast becomes a memory liability — use bucketing instead.

Broadcast helps when the small side is the dim. If the FACTS are skewed on the join key, broadcast still works but the skewed task in the fact is the bottleneck. Salt the fact instead.

approx_count_distinct(col, rsd=0.05) means 5% relative standard deviation. For a funnel chart 5% is fine; for billing it isn't. Pick deliberately.

from pyspark.sql.functions import approx_count_distinct
funnel = events_df.agg(approx_count_distinct("user_id", rsd=0.05)
                       .alias("approx_unique"))

.pivot('event_type') without an explicit list scans all values (extra job). Pass the list ['signup','activation','purchase'] to skip the scan.

N=16 salts means at most 16 tasks for the hot key. If your cluster has 64 cores, 16 salts under-utilize. Tune N to the available parallelism.

After salting the fact, the dim must be replicated across the salt buckets — either by cross-joining with a salt-numbers table or by emitting N copies, one per salt.

from pyspark.sql.functions import explode, array, lit
dim_replicated = dim.withColumn("salt", explode(array(*[lit(i) for i in range(N)])))

If the DAG has a cycle, topo_run returns fewer nodes than the graph; the final assert catches it. Without the assert, the executor silently runs a partial DAG.

The toy executor doesn't handle task failure. Real schedulers retry-with-backoff, mark downstream as upstream_failed, and expose state to a dashboard.

OrderedDict isn't thread-safe. For multi-threaded callers wrap with a Lock, or use functools.lru_cache (which is thread-safe).

from threading import Lock
class LRUCache:
    def __init__(self, cap): self.cap = cap; self.d = OrderedDict(); self.lock = Lock()
    def get(self, k):
        with self.lock:
            if k not in self.d: return None
            self.d.move_to_end(k); return self.d[k]

Capacity 0 should be valid (no caching). Test that put on a 0-capacity cache is a no-op rather than an exception.

The two-heap pattern only supports add. Real streaming median with deletes needs a counter for tombstones and lazy removal — non-trivial.

For very large N with floats, the median itself is fine, but downstream variance computation can drift. Use Welford's online algorithm for variance.

Running backfill for 2025-01-15 twice must produce the same files. Overwriting the partition (Path.rename or Delta MERGE) achieves this; appending does not.

If new rows for 2025-01-15 land while you backfill, you may miss them. Snapshot the source (e.g., a fixed read offset) before computing.

# Snapshot orders to a versioned read
orders_snap = orders[orders["order_date"] <= ds]   # bound by your run timestamp

Path.rename is atomic only within the same filesystem. On S3, rename is a copy + delete, not atomic. Use Delta/Iceberg replaceWhere or write to a manifest file.

The assert guards against passing a multi-date frame. Without it, you'd silently overwrite the partition with rows from other days. Always validate the partition key before publish.