SQL Practice Questions: A Three-Tier Ladder Built From Real Interviews

Good SQL practice isolates one decision at a time. In a data scientist interview, a simple metric can fail because COUNT(column) counts the wrong rows, a join changes the intended grain, or a cohort query treats an incomplete period as zero retention.

This guide uses a three-tier PostgreSQL ladder:

1. One-table aggregation and denominators
2. Joins, row survival, and result grain
3. Window functions and cohort retention

Every walkthrough follows the same order: read the input tables, write the operation query, trace the row flow, and check the output table. The final capstone uses that sequence to audit a broken daily-active-users query.

Choose the right practice tier

Start where your reasoning becomes unreliable, not where the syntax looks impressive.

Use the same practice loop for each problem:

1. Define one output row in plain language.
2. Name the numerator and denominator.
3. Predict the row count and one edge-case result.
4. Write the query.
5. Compare the actual output with your prediction.
6. Change one input row and explain what should change.

The datasets are intentionally small. You should be able to verify the outputs by hand before PostgreSQL confirms them.

Tier 1: one-table aggregation

The first operation asks for pickup attempts, successful pickups, and pickup rate by media type.

Input table

calls

The output grain is one row per media type. All calls belong in the denominator, but only rows with pickup = 'Y' belong in the numerator.

Operation query

SELECT
  media,
  COUNT(*) AS attempts,
  SUM(CASE WHEN pickup = 'Y' THEN 1 ELSE 0 END) AS pickups,
  ROUND(
    SUM(CASE WHEN pickup = 'Y' THEN 1 ELSE 0 END) * 1.0
      / COUNT(*),
    3
  ) AS pickup_rate
FROM calls
GROUP BY media
ORDER BY media;

Row flow

Output table

A common wrong answer uses COUNT(pickup). That counts every non-NULL value, including both Y and N, so it would report four pickups for each media type. PostgreSQL also supports COUNT(*) FILTER (WHERE pickup = 'Y'); the complete SUM(CASE ...) query above is the more portable form.

Change one pickup to NULL and decide whether that row is still an attempt. If it is, COUNT(*) should stay in the denominator. See SQL COUNT and its common traps for more counting edge cases.

Tier 2: joins and result grain

The next operation asks for ride activity by city while keeping registered users who have never taken a ride.

Input tables

users

rides

User 4 has no ride. The output grain is one row per city, not one row per user or ride.

Operation query

SELECT
  u.city,
  COUNT(r.ride_id) AS rides,
  COUNT(DISTINCT u.user_id) AS registered_users,
  COUNT(DISTINCT r.user_id) AS riders,
  ROUND(AVG(r.rating), 2) AS average_rating
FROM users AS u
LEFT JOIN rides AS r
  ON r.user_id = u.user_id
GROUP BY u.city
ORDER BY u.city;

Row flow

Output table

COUNT(*) would report one ride for user 4's placeholder row. Counting the non-nullable right-side key, r.ride_id, correctly contributes zero rides. Review SQL joins with runnable examples if that row-preservation step is not yet predictable.

Change the operation: filter for recent rides

Suppose recent means July 10 or later. Compare where the date predicate appears:

-- Keep all registered users; match only recent rides.
SELECT u.user_id, COUNT(r.ride_id) AS recent_rides
FROM users AS u
LEFT JOIN rides AS r
  ON r.user_id = u.user_id
 AND r.ride_ts >= TIMESTAMP '2025-07-10 00:00'
GROUP BY u.user_id
ORDER BY u.user_id;

-- Keep only users who have a recent ride.
SELECT u.user_id, COUNT(r.ride_id) AS recent_rides
FROM users AS u
LEFT JOIN rides AS r
  ON r.user_id = u.user_id
WHERE r.ride_ts >= TIMESTAMP '2025-07-10 00:00'
GROUP BY u.user_id
ORDER BY u.user_id;

Output with the filter in ON

Output with the filter in WHERE

Neither placement is universally correct. The metric definition determines which population belongs in the output.

Tier 3: windows and observation periods

Tier 3 adds sequencing and time. Two mistakes become especially costly: choosing an arbitrary row from a tie and treating a future cohort period as measured zero.

Latest ride per user

Use the rides input table from Tier 2. The requested output grain is one latest ride per user who has taken a ride.

WITH ranked AS (
  SELECT
    r.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY ride_ts DESC, ride_id DESC
    ) AS rn
  FROM rides AS r
)
SELECT user_id, ride_id, ride_ts, rating
FROM ranked
WHERE rn = 1
ORDER BY user_id;

PostgreSQL's DISTINCT ON can solve the same task. Aggregate-and-join and correlated-subquery forms can also work. The key interview points are output grain and deterministic tie behavior. The window functions interview guide compares ranking functions and frames.

Cohort retention with a declared data horizon

Assume event data is complete through March 31, 2025. We represent that with an exclusive boundary of April 1.

app_users

events

The January cohort has three fully observed calendar months: January, February, and March. The February cohort has two: February and March. April is not zero retention for the February cohort; it is outside the observation window.

WITH params AS (
  SELECT DATE '2025-04-01' AS data_through_exclusive
),
cohort_members AS (
  SELECT
    user_id,
    signup_ts,
    DATE_TRUNC('month', signup_ts)::date AS cohort_month
  FROM app_users
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohort_members
  GROUP BY cohort_month
),
periods AS (
  SELECT
    cs.cohort_month,
    cs.cohort_size,
    gs.month_n,
    (cs.cohort_month + gs.month_n * INTERVAL '1 month')::date
      AS activity_month
  FROM cohort_sizes AS cs
  CROSS JOIN params AS p
  CROSS JOIN LATERAL GENERATE_SERIES(
    0,
    (
      EXTRACT(YEAR FROM AGE(
        p.data_through_exclusive - INTERVAL '1 month',
        cs.cohort_month
      )) * 12
      + EXTRACT(MONTH FROM AGE(
        p.data_through_exclusive - INTERVAL '1 month',
        cs.cohort_month
      ))
    )::int
  ) AS gs(month_n)
),
activity AS (
  SELECT
    cm.cohort_month,
    DATE_TRUNC('month', e.event_ts)::date AS activity_month,
    COUNT(DISTINCT cm.user_id) AS active_users
  FROM cohort_members AS cm
  JOIN events AS e
    ON e.user_id = cm.user_id
   AND e.event_ts >= cm.signup_ts
  CROSS JOIN params AS p
  WHERE e.event_ts < p.data_through_exclusive
  GROUP BY cm.cohort_month, DATE_TRUNC('month', e.event_ts)::date
)
SELECT
  p.cohort_month,
  p.month_n,
  p.cohort_size,
  COALESCE(a.active_users, 0) AS active_users,
  ROUND(COALESCE(a.active_users, 0) * 100.0 / p.cohort_size, 1)
    AS retention_pct
FROM periods AS p
LEFT JOIN activity AS a
  ON a.cohort_month = p.cohort_month
 AND a.activity_month = p.activity_month
ORDER BY p.cohort_month, p.month_n;

The event join requires e.event_ts >= cm.signup_ts, so user 3's February 1 event does not count before the February 3 signup. In a real analysis, also document whether the denominator is the original cohort, eligible users, or users observable in each period.

Capstone: audit a broken DAU query

The requested metric is registered daily active users on August 15, 2025.

Input tables

site_users

events

User 9 is an orphan event identity and is not a registered user.

Broken and corrected operations

-- Broken: groups at user grain instead of returning one site-wide row.
SELECT
  u.user_id,
  COUNT(DISTINCT e.user_id) AS dau
FROM site_users AS u
LEFT JOIN events AS e
  ON e.user_id = u.user_id
WHERE DATE(e.event_ts) = DATE '2025-08-15'
GROUP BY u.user_id
ORDER BY u.user_id;

-- Correct for registered DAU.
SELECT COUNT(DISTINCT e.user_id) AS registered_dau
FROM events AS e
JOIN site_users AS u
  ON u.user_id = e.user_id
WHERE e.event_ts >= TIMESTAMP '2025-08-15 00:00'
  AND e.event_ts <  TIMESTAMP '2025-08-16 00:00';

Row flow

Output tables

Broken output

Correct output

If the business intentionally counted every event identity, including identities missing from site_users, the answer would be 3 and the user table should not define the population. That is a metric-definition decision, not a syntax decision.

The half-open timestamp range also allows PostgreSQL to consider an ordinary index on the raw timestamp or prune time-based partitions. Wrapping the column in DATE(...) commonly prevents those optimizations, although an expression index can support the wrapped form. The SQL order of operations guide explains why the original LEFT JOIN and WHERE combination removes unmatched rows.

For a focused 45-minute practice session:

• 5 minutes: choose one tier and restate the prompt.
• 15 minutes: solve one operation without notes.
• 10 minutes: trace the row flow and compare the output.
• 10 minutes: change an edge-case row and explain the effect.
• 5 minutes: record the mistake to revisit.

Use PracHub's SQL interview question bank to choose a prompt, but keep each session focused on one failure mode.

FAQ

Should I use COUNT(*) or COUNT(column)?

Use COUNT(*) when every result row belongs in the count. Use COUNT(column) when rows with NULL in that column should not count. After a LEFT JOIN, counting a non-nullable key from the right table counts matches without counting the placeholder row.

Does a right-table filter always belong in ON for a LEFT JOIN?

No. Put it in ON when unmatched left-side rows must remain in the population. Put it in WHERE when the requested population requires a matching right-side row. Define the population first.

Is a missing cohort row the same as zero retention?

Not until the period is known to be complete and the query has generated the expected cohort-period row. A missing row can mean zero activity, an incomplete future period, or a bug in the date grid. Declare the data horizon before applying COALESCE(..., 0).

Why add a second column to a window ORDER BY?

The first sort key can tie. A stable unique key such as ride_id makes the selected row deterministic. Without it, two executions are allowed to choose different tied rows.

When should I move to the next tier?

Move up when you can predict the current tier's output, explain its edge cases, and reproduce the operation later without copying. If you only discover the metric definition after seeing the result, stay at that tier and vary the input table.

Is this guide only for data scientists?

Data scientists are the primary audience because the examples emphasize metric definitions, cohorts, and analytical judgment. The same core skills apply to data analysts and data engineers, with different emphasis in later interview rounds.