Cracking ML Interviews

Open in the interactive study hub

SQL Coding Challenges for Interviews

This guide focuses on SQL interview rounds for ML, data, analytics, and platform roles.

Most real interview SQL is not about obscure syntax. It is about whether you can turn a business question into a correct query with clean logic.

What Interviewers Usually Test

1. Core Aggregations

Concepts to know

Typical prompts

Example

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

What to watch

2. Joins and Null Handling

Concepts to know

Typical prompts

Example

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.amount), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
   AND o.status = 'completed'
GROUP BY c.customer_id, c.customer_name;

What to say in the interview

Mention the join grain explicitly:

3. Deduplication and Latest-Row Logic

Concepts to know

Typical prompts

Example

WITH ranked AS (
    SELECT
        user_id,
        status,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM user_status_history
)
SELECT
    user_id,
    status,
    updated_at
FROM ranked
WHERE rn = 1;

If two rows share the same updated_at, ROW_NUMBER() picks one arbitrarily and the result can change between runs. Add a tie-breaker to the ORDER BY (for example a unique event_id or load sequence) so the dedup is deterministic.

Why it matters

This is one of the most common warehouse and dbt patterns.

Related repo reading:

4. Ranking and Top-N Per Group

Concepts to know

Typical prompts

Example

WITH ranked_products AS (
    SELECT
        category,
        product_id,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rn
    FROM product_revenue
)
SELECT
    category,
    product_id,
    revenue
FROM ranked_products
WHERE rn <= 3;

ROW_NUMBER() returns exactly 3 rows per category and breaks revenue ties arbitrarily. If tied products should all be kept, use RANK() or DENSE_RANK() and say which one the business question wants.

5. Window Functions

Concepts to know

Typical prompts

Example

SELECT
    event_date,
    daily_signups,
    SUM(daily_signups) OVER (
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_signups,
    AVG(daily_signups) OVER (
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_signup_metrics;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means the last 7 rows, which equals the last 7 days only if the table has exactly one row per day with no missing dates. If days can be missing, join to a date spine first, or use a time-based frame such as RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW where supported (PostgreSQL 11+, DuckDB; support varies elsewhere; SQLite only accepts numeric RANGE offsets, for example over julianday(event_date)).

6. Time Bucketing and Cohort Logic

Concepts to know

Typical prompts

Example: monthly active users

DATE_TRUNC('month', ts) is PostgreSQL, DuckDB, Snowflake, and Redshift syntax. BigQuery uses DATE_TRUNC(ts_date, MONTH) / TIMESTAMP_TRUNC(ts, MONTH), MySQL has no DATE_TRUNC (use DATE_FORMAT(ts, '%Y-%m-01')), and SQLite uses strftime('%Y-%m-01', ts).

SELECT
    DATE_TRUNC('month', event_time) AS month_start,
    COUNT(DISTINCT user_id) AS monthly_active_users
FROM user_events
WHERE event_name = 'session_start'
GROUP BY DATE_TRUNC('month', event_time)
ORDER BY month_start;

Example: previous event per user (the input to a gap calculation)

SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) AS previous_event_time
FROM user_events;

The gap itself is event_time - previous_event_time, and timestamp subtraction is dialect-specific: an INTERVAL in PostgreSQL and DuckDB, DATEDIFF(second, prev, cur) in Snowflake, TIMESTAMP_DIFF(cur, prev, SECOND) in BigQuery, and (julianday(cur) - julianday(prev)) * 86400 in SQLite. The first event per user has a NULL previous time. Use LEAD() instead of LAG() for the time until the next event.

7. Funnel and Conversion Queries

Concepts to know

Typical prompts

Example

WITH user_steps AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS viewed,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    GROUP BY user_id
)
SELECT
    SUM(viewed) AS users_viewed,
    SUM(added_to_cart) AS users_added_to_cart,
    SUM(purchased) AS users_purchased
FROM user_steps;

This counts users who ever did each step, in any order: a user who purchased without viewing still counts toward users_purchased. For a strict funnel, require the earlier steps (for example SUM(viewed * added_to_cart * purchased)), and if order matters, compare the first timestamp of each step per user.

8. Clean Query Design with CTEs

Concepts to know

Typical prompts

Example structure

WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'completed'
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS revenue
    FROM filtered_orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    revenue
FROM customer_revenue
ORDER BY revenue DESC;

What interviewers like

Practice Checklist

Easy

Medium

Harder but high value

Common Mistakes

Good Interview Habits

  1. State the table grain first.
  2. State the output grain next.
  3. Say whether you need a join, a dedup step, a window, or a CTE pipeline.
  4. Write the query in layers.
  5. Validate it with one small example in words.

Repo Concepts to Pair With Practice

If you can solve medium SQL questions quickly and explain the underlying grain, windows, and business logic, you are already strong for many ML-adjacent interview loops.

View or edit this guide on GitHub