11 Data Analyst SQL for Analytics Interview Questions and Answers

Short answer: A window function computes an aggregate over a "window" of rows but does not collapse them — every source row survives and gets its own value. GROUP BY instead folds each group into a single row.

In depth:

1. GROUP BY — N rows in a group → 1 result row. Detail is lost.
2. Window function — N rows stay N rows, with an aggregate (sum, rank, average, lag) added alongside.
3. Syntax — func() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY defines the groups, ORDER BY sets order within the window (needed for running totals and ranks).

-- Running total of sales per user
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

⚠️ Common mistake: trying to filter on a window function's result in WHERE — windows are evaluated after WHERE/GROUP BY, so wrap the query in a CTE/subquery and filter on the outside.

Short answer: All three number rows within a window but differ on ties. For "top 3 products per category" you typically use ROW_NUMBER (exactly N rows) or RANK/DENSE_RANK if ties should share a place.

In depth:

-- Top 3 products by revenue in each category
SELECT category, product, revenue
FROM (
  SELECT
    category,
    product,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

⚠️ Common mistake: expecting exactly 3 rows from RANK when there are ties — RANK can return more. If you need strictly N, use ROW_NUMBER.

Short answer: A CTE (WITH ... AS (...)) lifts a subquery into a named block at the top of the query, so logic reads top-to-bottom instead of unwrapping nesting inside-out. One CTE can be reused multiple times.

In depth:

1. Readability — each step gets a name; the query reads as a pipeline, not a russian-doll of subqueries.
2. Reuse — a CTE can be referenced several times; an inline subquery cannot.
3. Recursive CTEs — WITH RECURSIVE walks hierarchies and graphs (employee tree, chains): a base query + UNION ALL with a step that references the CTE itself.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS mth,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;

⚠️ Common mistake: assuming a CTE always speeds things up. It's primarily about readability; in some engines a CTE may materialize and block the optimizer.

Short answer: Define each user's cohort as their signup month, then for each activity compute how many months after signup it occurred, and group by (cohort, month offset) counting distinct users.

In depth:

1. Cohort — DATE_TRUNC('month', signup_date) per user.
2. Offset — the month difference between activity month and cohort month.
3. Metric — COUNT(DISTINCT user_id) per (cohort, offset); dividing by cohort size yields % retention.

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity AS (
  SELECT a.user_id,
         c.cohort_month,
         (DATE_PART('year',  a.event_date) - DATE_PART('year',  c.cohort_month)) * 12
       + (DATE_PART('month', a.event_date) - DATE_PART('month', c.cohort_month)) AS month_offset
  FROM events a
  JOIN cohorts c ON c.user_id = a.user_id
)
SELECT cohort_month,
       month_offset,
       COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

⚠️ Common mistake: using COUNT(*) instead of COUNT(DISTINCT user_id) — multiple events from one user in a month will inflate retention.

Short answer: Count the distinct users reaching each step (via conditional aggregation), then divide each step by the previous one — that's step conversion — and by the very first step — that's overall conversion.

In depth:

1. One pass — COUNT(DISTINCT ...) FILTER (WHERE step = '...') gives the count at each step without repeated joins.
2. Step conversion — step / previous step.
3. Overall conversion — last step / first step.

WITH funnel AS (
  SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit')    AS visited,
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup')   AS signed_up,
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS purchased
  FROM events
)
SELECT
  visited,
  signed_up,
  purchased,
  ROUND(100.0 * signed_up / NULLIF(visited, 0),   1) AS visit_to_signup_pct,
  ROUND(100.0 * purchased / NULLIF(signed_up, 0), 1) AS signup_to_purchase_pct,
  ROUND(100.0 * purchased / NULLIF(visited, 0),   1) AS overall_pct
FROM funnel;

⚠️ Common mistake: ignoring step order — a user must hit signup before purchase; for a strict funnel compare step timestamps, not just whether the event happened.

Short answer: INNER keeps only matched rows, LEFT keeps all left rows plus matches on the right (unmatched right = NULL), FULL OUTER keeps all rows from both tables. To find "orphan" rows, LEFT JOIN and filter WHERE right.id IS NULL.

In depth:

-- Users who never placed an order
SELECT u.user_id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.user_id IS NULL;

⚠️ Common mistake: putting a condition on the right table of a LEFT JOIN in WHERE (e.g. WHERE o.status = 'paid') — it drops the NULL rows and silently turns the LEFT JOIN into an INNER JOIN. Move such conditions into ON.

Short answer: Group by DATE_TRUNC('month', ts) (or 'day'/'week') to bucket events. To keep "empty" periods from vanishing, generate a full calendar and LEFT JOIN the actual data onto it.

In depth:

1. Bucketing — DATE_TRUNC truncates a timestamp to the start of the period; grouping on it yields a daily/weekly/monthly series.
2. Calendar — generate_series builds a continuous date axis.
3. Gap filling — LEFT JOIN facts onto the calendar + COALESCE(metric, 0), otherwise days with no events simply disappear.

WITH calendar AS (
  SELECT generate_series(
    DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day'
  )::date AS day
)
SELECT c.day,
       COALESCE(COUNT(o.id), 0) AS orders
FROM calendar c
LEFT JOIN orders o
  ON DATE_TRUNC('day', o.created_at) = c.day
GROUP BY c.day
ORDER BY c.day;

⚠️ Common mistake: charting a trend only from rows that have events — zero days drop out and the chart/moving average gets distorted.

Short answer: SUM/AVG/COUNT(col) ignore NULLs. COUNT(*) counts every row, while COUNT(col) counts only rows where col is not NULL. In joins and comparisons NULL is "contagious": NULL = NULL is not TRUE, it's NULL.

In depth:

1. Aggregates — NULL is skipped; this matters for AVG: the denominator is only the non-NULL values, not all rows.
2. COUNT — COUNT(*) = number of rows, COUNT(col) = number of non-NULL values in the column.
3. COALESCE / NULLIF — COALESCE(x, 0) substitutes a value for NULL; NULLIF(a, b) produces NULL (often used against divide-by-zero: b/NULLIF(a,0)).

SELECT
  COUNT(*)            AS all_rows,      -- every row
  COUNT(phone)        AS with_phone,    -- only non-NULL phones
  AVG(amount)         AS avg_amount,    -- average over non-NULL
  AVG(COALESCE(amount, 0)) AS avg_with_zeros  -- NULL treated as 0
FROM users;

⚠️ Common mistake: measuring fill rate with COUNT(col)/COUNT(col) or comparing against NULL with = NULL. Use IS NULL / IS NOT NULL for checks.

Short answer: Medians and percentiles are PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). For splitting into equal buckets (quartiles, deciles) use NTILE(n). AVG hides skew: a single outlier drags the mean, while the median stays robust.

In depth:

1. Percentiles — PERCENTILE_CONT(0.5) (median), 0.9 (p90), 0.95 (p95); interpolates between values.
2. NTILE — NTILE(4) OVER (ORDER BY x) distributes rows into 4 equal buckets (quartiles).
3. Why not AVG — for skewed quantities (revenue, response time) the mean is inflated by the tail; look at the median and p90/p95.

SELECT
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY response_ms) AS median_ms,
  PERCENTILE_CONT(0.9)  WITHIN GROUP (ORDER BY response_ms) AS p90_ms,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_ms,
  AVG(response_ms)                                          AS mean_ms
FROM requests;

⚠️ Common mistake: reporting only the mean on right-skewed data — p95 shows the real "tail" users actually experience, which the average masks.

Short answer: Wrap CASE WHEN inside SUM or COUNT to compute several metrics under different conditions in a single pass — this "pivots" rows into columns without a separate query per group.

In depth:

1. SUM(CASE ...) — SUM(CASE WHEN cond THEN amount ELSE 0 END) sums only the matching rows.
2. COUNT(CASE ...) — COUNT(CASE WHEN cond THEN 1 END) counts rows by condition (ELSE yields NULL, which COUNT skips).
3. FILTER — in PostgreSQL it reads cleaner: COUNT(*) FILTER (WHERE cond).

-- Monthly metrics: pivot statuses into columns
SELECT
  DATE_TRUNC('month', created_at) AS mth,
  COUNT(*)                                       AS total,
  SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid_revenue,
  COUNT(*) FILTER (WHERE status = 'refunded')    AS refunds
FROM orders
GROUP BY 1
ORDER BY 1;

Before (rows)          After (columns)
mth   status           mth   paid  refunds
01    paid       →      01    120   3
01    refunded         02    150   1

⚠️ Common mistake: writing COUNT(CASE WHEN cond THEN 0 END) — zero is not NULL, so every row gets counted. Return 1/a value, and NULL in the ELSE.

Short answer: Indexes speed up selective filters and joins but barely help full-scan aggregations over the whole table. Avoid SELECT *, filter early, pre-aggregate, and remember DISTINCT and wide GROUP BY are costly due to sorting/hashing.

In depth:

1. Indexes — win on narrow filters (WHERE dt >= ...) and join keys; for aggregating "everything" the optimizer will still pick a seq scan.
2. **SELECT *** — drags extra columns, breaks index-only scans and inflates I/O; list only what you need.
3. Pre-aggregation — materialized views / rollup tables compute the heavy work ahead of time.
4. DISTINCT / large GROUP BY — require a sort or hash; on large volumes this is the bottleneck. EXPLAIN ANALYZE shows the real plan.

-- Narrow filter + only needed columns → index-only scan is viable
CREATE INDEX idx_orders_created ON orders (created_at);

EXPLAIN ANALYZE
SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01'
GROUP BY 1;

⚠️ Common mistake: wrapping an indexed column in a function in WHERE (WHERE DATE(created_at) = ...) — it kills the index. Compare against a range instead: created_at >= ... AND created_at < ....