SQL for Data Analysis: The Six Query Patterns Analyst Interviews Actually Test

Analyst SQL gets easier when you decide the output grain before choosing syntax. Start with the business unit you need to report, such as one row per ad, cohort, device, or day. Then trace how each join, filter, aggregation, and window changes that grain.

This guide develops six reusable patterns on small PostgreSQL tables. Each example shows the input, the operation, the row flow, and the exact output so you can audit the logic instead of trusting a familiar-looking query.

Start with grain: six practical analyst patterns

Use this map before writing SQL:

Write the grain beside the question. After every query stage, ask whether the rows are still at that grain, are temporarily more detailed, or have already been collapsed too far.

Conditional metrics and cohort retention

Click-through rate is one row per ad. Clicks and impressions live in the same event column, so conditional aggregates create both measures from the same grouped population.

Input: ad_events

SELECT
  ad_id,
  COUNT(*) FILTER (WHERE event_type = 'impression') AS impressions,
  COUNT(*) FILTER (WHERE event_type = 'click') AS clicks,
  ROUND(
    COUNT(*) FILTER (WHERE event_type = 'click')::numeric
    / NULLIF(COUNT(*) FILTER (WHERE event_type = 'impression'), 0),
    3
  ) AS ctr
FROM ad_events
GROUP BY ad_id
ORDER BY ad_id;

Output

Retention needs a stricter contract. Here, W1 means at least one login from day 1 through day 7 after signup. Signup-day activity does not count. A cohort is reported only when its entire W1 window is on or before the last complete date, 2026-07-27.

Input: users

Input: logins

WITH params AS (
  SELECT DATE '2026-07-27' AS last_complete_date
),
eligible_users AS (
  SELECT u.user_id, u.signup_date
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_date + 7 <= p.last_complete_date
)
SELECT
  u.signup_date AS cohort_date,
  COUNT(DISTINCT u.user_id) AS cohort_size,
  COUNT(DISTINCT l.user_id) AS retained_users,
  ROUND(
    100.0 * COUNT(DISTINCT l.user_id)
    / COUNT(DISTINCT u.user_id),
    1
  ) AS retention_pct
FROM eligible_users AS u
LEFT JOIN logins AS l
  ON l.user_id = u.user_id
 AND l.login_date >= u.signup_date + 1
 AND l.login_date <= u.signup_date + 7
GROUP BY u.signup_date
ORDER BY u.signup_date;

Output

The zero-retention cohort stays visible because users are the left-side population. For more on counts after outer joins, see SQL COUNT.

One row per entity and rolling metrics

When a fact table has several rows per entity, rank first and filter in a second query block. The tie-breaker makes the choice deterministic when timestamps match.

Input: device_logins

WITH ranked AS (
  SELECT
    login_id,
    user_id,
    device_id,
    login_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, device_id
      ORDER BY login_at, login_id
    ) AS row_num
  FROM device_logins
)
SELECT
  user_id,
  device_id,
  login_id,
  TO_CHAR(login_at, 'YYYY-MM-DD HH24:MI') AS first_login_at
FROM ranked
WHERE row_num = 1
ORDER BY user_id, device_id;

Output

See the window-functions guide for ranking and frame variations.

A three-day rolling average should represent three calendar dates, not the last three stored rows. A calendar spine makes the missing August 3 explicit as zero orders.

Input: daily_orders

WITH bounds AS (
  SELECT MIN(order_date) AS first_date, MAX(order_date) AS last_date
  FROM daily_orders
),
calendar AS (
  SELECT day::date AS order_date
  FROM bounds
  CROSS JOIN LATERAL GENERATE_SERIES(
    first_date,
    last_date,
    INTERVAL '1 day'
  ) AS day
),
series AS (
  SELECT c.order_date, COALESCE(d.orders, 0) AS orders
  FROM calendar AS c
  LEFT JOIN daily_orders AS d USING (order_date)
)
SELECT
  order_date,
  orders,
  ROUND(
    AVG(orders) OVER (
      ORDER BY order_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ),
    1
  ) AS rolling_3d_avg
FROM series
ORDER BY order_date;

Output

Sessionization and data-quality profiling

Sessions are defined here by a gap rule: a new session starts when a user's event is more than 30 minutes after their previous event. LAG exposes the prior timestamp; a running sum turns each start flag into a session number.

Input: events

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    event_ts,
    LAG(event_ts) OVER (
      PARTITION BY user_id
      ORDER BY event_ts, event_id
    ) AS previous_event_ts
  FROM events
),
marked AS (
  SELECT
    *,
    CASE
      WHEN previous_event_ts IS NULL
        OR event_ts > previous_event_ts + INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS new_session
  FROM ordered
),
assigned AS (
  SELECT
    *,
    SUM(new_session) OVER (
      PARTITION BY user_id
      ORDER BY event_ts, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM marked
)
SELECT
  user_id,
  session_num,
  TO_CHAR(MIN(event_ts), 'YYYY-MM-DD HH24:MI') AS session_start,
  TO_CHAR(MAX(event_ts), 'YYYY-MM-DD HH24:MI') AS session_end,
  COUNT(*) AS event_count
FROM assigned
GROUP BY user_id, session_num
ORDER BY user_id, session_num;

Output

This is a gaps-and-islands problem; the gaps-and-islands guide develops related grouping techniques.

Finally, profiling should make missingness visible. COUNT(column) counts only known values, while COUNT(*) counts rows. PostgreSQL's IS DISTINCT FROM is useful when a NULL status should be treated as not equal to cancelled.

Input: deliveries

SELECT
  COUNT(*) AS all_orders,
  COUNT(minutes_to_deliver) AS known_delivery_times,
  ROUND(AVG(minutes_to_deliver), 1) AS avg_known_minutes,
  COUNT(*) FILTER (WHERE status IS NULL) AS missing_statuses,
  COUNT(*) FILTER (
    WHERE status IS DISTINCT FROM 'cancelled'
  ) AS not_cancelled_including_unknown
FROM deliveries;

Output

A practical analyst workflow

For a new analysis, write five lines before the query:

1. Population: which entities must appear, including zero-activity entities.
2. Grain: what one output row represents.
3. Metric: the exact numerator, denominator, and NULL policy.
4. Time: timezone, interval boundaries, maturity rule, and missing-date policy.
5. Determinism: tie-breakers and final output order.

Then build the query in stages and inspect the row count and key uniqueness after each stage. A query can run successfully while changing the population or grain. Small edge fixtures make that visible. Add a zero-activity entity, a duplicate timestamp, a missing date, a NULL, and an immature cohort before trusting the result. The SQL practice guide provides more exercises that reward this kind of explicit reasoning.

FAQ

Should I start a query with joins or with the final metric?

Start with the population and output grain. Choose the table that owns that population, then join the detail needed for the metric. This prevents a convenient fact table from silently excluding entities with no activity.

When should I use a window instead of GROUP BY?

Use GROUP BY when the output should collapse to one row per group. Use a window when each current row should remain visible while receiving a group-level or ordered calculation. Many analyses aggregate in a CTE and then window over that new grain.

Why include tiny tables in an analysis guide?

They make logic auditable. You can predict every result, see which edge case changes the answer, and move the same test into a larger pipeline. The point is not the volume of data; it is whether the query preserves the intended contract.

Are these patterns specific to PostgreSQL?

The analytical ideas transfer, but syntax varies. These queries target PostgreSQL 16. Check date-series, filtered-aggregate, interval, and NULL-safe comparison syntax before moving them to another engine.