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: For an ordered funnel, count users completing visit → signup → purchase in that order within a defined window. Step conversion is the current-step count divided by the previous-step count; overall conversion is purchase / visit. Independent event-membership counts do not enforce order.

In depth:

In events(user_id, step, event_ts), event_ts is integer Unix time in seconds and user_id is non-null. Parameters cohort_start and cohort_end define the entry period; positive conversion_window is the window length in seconds. The SQL client binds named parameters; adapt parameter syntax to the driver.

This example anchors the window to each user's first visit in [cohort_start, cohort_end); another visit does not restart it. Signup and purchase must occur strictly after the previous step and before visit + conversion_window; the upper bound is excluded. The cohort period restricts visits only: later steps may occur after cohort_end. Data must cover each participant's full window. This is an explicit modeling choice; allowing repeated entry attempts requires different logic.

WITH visits AS (
  SELECT user_id, MIN(event_ts) AS visit_ts
  FROM events
  WHERE step = 'visit'
    AND event_ts >= :cohort_start AND event_ts < :cohort_end
  GROUP BY user_id
), signups AS (
  SELECT v.user_id, v.visit_ts, MIN(e.event_ts) AS signup_ts
  FROM visits v
  LEFT JOIN events e ON e.user_id = v.user_id AND e.step = 'signup'
    AND e.event_ts > v.visit_ts
    AND e.event_ts < v.visit_ts + :conversion_window
  GROUP BY v.user_id, v.visit_ts
), purchases AS (
  SELECT s.user_id, s.signup_ts, MIN(e.event_ts) AS purchase_ts
  FROM signups s
  LEFT JOIN events e ON e.user_id = s.user_id AND e.step = 'purchase'
    AND e.event_ts > s.signup_ts
    AND e.event_ts < s.visit_ts + :conversion_window
  GROUP BY s.user_id, s.signup_ts
), counts AS (
  SELECT COUNT(*) AS visited, COUNT(signup_ts) AS signed_up,
         COUNT(purchase_ts) AS purchased
  FROM purchases
)
SELECT *,
  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 counts;

1. MIN selects the first eligible event at each step; repeated events do not inflate user counts. LEFT JOIN retains users who did not reach the next step, so step and time conditions stay in ON. Each input row for counts represents one visitor: COUNT(*) counts all rows, while COUNT(signup_ts) and COUNT(purchase_ts) count only non-null values.
2. Other events may occur between steps. This is an ordered funnel, not a requirement for consecutive events without intermediate actions.
3. Equal timestamps do not count as “after” here: add a reliable sequence ID if those events must be ordered. Late-arriving events and incomplete observation windows need explicit policies.
4. NULLIF(denominator, 0) prevents division by zero. With no visitors, all percentages are NULL; with visitors but no signups, visit → signup and overall conversion are 0%, while signup → purchase is NULL because there is no denominator population.

⚠️ Common mistake: counting event membership without checking order. For a single user with the path visit → purchase → signup within the window, independent counts report 100% for both transitions; the query above reports 100% for visit → signup and 0% for signup → purchase and overall conversion. A rate at or below 100% does not by itself establish correctness.

Sources: Amplitude — funnel rules; PostgreSQL — COUNT and MIN; PostgreSQL — NULLIF.

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 < ....