Netflix Data Analyst Interview Preparation Guide – Junior Level

SELECT
  variant,
  COUNT(*)::int AS n,
  AVG(session_duration_seconds)::numeric AS mean_seconds,
  stddev_samp(session_duration_seconds)::numeric AS sd_seconds,
  (stddev_samp(session_duration_seconds) / sqrt(COUNT(*)::numeric))::numeric AS stderr_seconds,
  (AVG(session_duration_seconds) - 1.96 * (stddev_samp(session_duration_seconds) / sqrt(COUNT(*)::numeric)))::numeric AS ci_lower_95,
  (AVG(session_duration_seconds) + 1.96 * (stddev_samp(session_duration_seconds) / sqrt(COUNT(*)::numeric)))::numeric AS ci_upper_95
FROM events
WHERE session_duration_seconds IS NOT NULL
GROUP BY variant
ORDER BY variant;

WITH normalized AS (
  SELECT *,
    lower(trim(email)) AS norm_email
  FROM users_raw
),
completeness AS (
  SELECT *,
    -- count non-null columns you care about
    ( (first_name IS NOT NULL)::int
    + (last_name IS NOT NULL)::int
    + (phone IS NOT NULL)::int
    + (email IS NOT NULL)::int
    ) AS non_null_count
  FROM normalized
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY norm_email
      ORDER BY non_null_count DESC, created_at DESC, id
    ) AS rn,
    -- order within group for merging
    RANK() OVER (
      PARTITION BY norm_email
      ORDER BY non_null_count DESC, created_at DESC
    ) AS rnk
  FROM completeness
),
canonical AS (
  -- keep one canonical id per norm_email
  SELECT * FROM ranked WHERE rn = 1
),
merged AS (
  -- merge non-null fields: prefer canonical, else next best, etc.
  SELECT
    c.norm_email,
    c.id AS canonical_id,
    COALESCE(c.first_name,
             (SELECT first_name FROM ranked r2 WHERE r2.norm_email = c.norm_email AND r2.id <> c.id ORDER BY r2.non_null_count DESC, r2.created_at DESC LIMIT 1)
    ) AS first_name,
    COALESCE(c.last_name,
             (SELECT last_name FROM ranked r2 WHERE r2.norm_email = c.norm_email AND r2.id <> c.id ORDER BY r2.non_null_count DESC, r2.created_at DESC LIMIT 1)
    ) AS last_name,
    COALESCE(c.phone,
             (SELECT phone FROM ranked r2 WHERE r2.norm_email = c.norm_email AND r2.id <> c.id ORDER BY r2.non_null_count DESC, r2.created_at DESC LIMIT 1)
    ) AS phone,
    c.created_at
  FROM canonical c
)
SELECT * FROM merged;

SELECT
  p.product_id,
  p.category_id,
  s.total_sales,
  RANK() OVER (PARTITION BY p.category_id ORDER BY s.total_sales DESC) AS category_rank
FROM sales s
JOIN products p ON s.product_id = p.product_id
;

WITH ranked_sales AS (
  SELECT
    product_id,
    total_sales,
    RANK() OVER (PARTITION BY product_id_category ORDER BY total_sales DESC) AS category_rank -- assume category known in sales or joined in CTE
  FROM sales
  /* or join minimal product info here if sales lacks category */
)
SELECT
  r.product_id,
  p.category_id,
  r.total_sales,
  r.category_rank
FROM ranked_sales r
JOIN products p USING (product_id);

WITH means AS (
  -- compute mean outcome and count per group-period
  SELECT
    "group",
    period,
    AVG(outcome)        AS mean_outcome,
    COUNT(*)            AS n_obs
  FROM experiments
  GROUP BY "group", period
),
pivoted AS (
  -- pivot to get four means in one row
  SELECT
    MAX(CASE WHEN "group" = 'treatment' AND period = 'pre'  THEN mean_outcome END)  AS mean_treatment_pre,
    MAX(CASE WHEN "group" = 'treatment' AND period = 'post' THEN mean_outcome END)  AS mean_treatment_post,
    MAX(CASE WHEN "group" = 'control'   AND period = 'pre'  THEN mean_outcome END)  AS mean_control_pre,
    MAX(CASE WHEN "group" = 'control'   AND period = 'post' THEN mean_outcome END)  AS mean_control_post,
    MAX(CASE WHEN "group" = 'treatment' AND period = 'pre'  THEN n_obs END)         AS n_treatment_pre,
    MAX(CASE WHEN "group" = 'treatment' AND period = 'post' THEN n_obs END)         AS n_treatment_post,
    MAX(CASE WHEN "group" = 'control'   AND period = 'pre'  THEN n_obs END)         AS n_control_pre,
    MAX(CASE WHEN "group" = 'control'   AND period = 'post' THEN n_obs END)         AS n_control_post
  FROM means
)
SELECT
  mean_treatment_pre,
  mean_treatment_post,
  mean_control_pre,
  mean_control_post,
  -- DiD: (T_post - T_pre) - (C_post - C_pre)
  (mean_treatment_post - mean_treatment_pre) - (mean_control_post - mean_control_pre) AS did_estimate,
  -- optional: return subgroup sizes to assess reliability
  n_treatment_pre,
  n_treatment_post,
  n_control_pre,
  n_control_post
FROM pivoted;

WITH RECURSIVE dates AS (
  SELECT DISTINCT date FROM balances
),
base AS (
  -- ensure every account has a row for each date (0 if missing)
  SELECT a.account_id, d.date, COALESCE(b.amount, 0) AS amount
  FROM accounts a
  CROSS JOIN dates d
  LEFT JOIN balances b
    ON b.account_id = a.account_id AND b.date = d.date
),
agg_recursive AS (
  -- seed: leaf/current balances
  SELECT
    a.account_id,
    a.parent_id,
    b.date,
    b.amount,
    ARRAY[a.account_id] AS path -- cycle detection
  FROM accounts a
  JOIN base b ON a.account_id = b.account_id

  UNION ALL

  -- propagate child totals up to parent
  SELECT
    p.account_id,
    p.parent_id,
    c.date,
    c.amount, -- amount being contributed from descendant
    path || p.account_id
  FROM agg_recursive c
  JOIN accounts p ON p.account_id = c.parent_id
  -- prevent cycles: don't revisit accounts already in path
  WHERE NOT p.account_id = ANY(path)
)
SELECT
  account_id,
  date,
  SUM(amount) AS total_amount
FROM agg_recursive
GROUP BY account_id, date
ORDER BY account_id, date;