Data scientist databases interview questions

As asked

Using SQL, how would you compute the 30-day retention rate for cohorts of users grouped by their signup week? Assume a table 'events' with columns user_id, event_date, and event_type.

Sample answer outline

The candidate should write a query that finds each user's first event date (cohort assignment) using MIN(event_date), joins back to events to find users with activity 28 to 35 days later, and groups by cohort week. They should use date arithmetic correctly, discuss the denominator (cohort size), and mention edge cases like users who only have one event.

Expect these follow-ups

• How would you extend this to compute a full 12-week retention curve without repeating the join 12 times?

As asked

You have a table 'events' with columns user_id, event_name, and event_timestamp. The funnel steps are: page_view, add_to_cart, checkout_start, purchase. Write a SQL query that computes the conversion rate from each step to the next.

Sample answer outline

The candidate should write a query that uses conditional aggregation or window functions to find the first occurrence of each event per user, then count users who reached each step (with each step requiring all prior steps in order). The key detail is using MIN(event_timestamp) per user per event type and ensuring the ordering is preserved, not just presence. Funnel conversion = step N count divided by step 1 count.

Expect these follow-ups

• How would you modify the query to require that steps happen in sequence within a 7-day window?

As asked

You have an Adobe Analytics database with a campaigns table (campaign_id, region, impressions, conversions, date). Write a SQL query that, for each region, returns the top 3 campaigns by conversion rate in the last 30 days. Include the campaign rank within its region.

Sample answer outline

Use a CTE to filter the last 30 days and compute conversion_rate = conversions / NULLIF(impressions, 0), then apply RANK() OVER (PARTITION BY region ORDER BY conversion_rate DESC). Filter the outer query WHERE rank <= 3. A strong answer handles the NULLIF for division by zero, considers whether RANK or DENSE_RANK is more appropriate (DENSE_RANK if you want exactly 3 results per region when there are ties), and mentions index hints for the date filter.

Reference implementation (sql)

WITH recent AS (
  SELECT
    campaign_id,
    region,
    conversions * 1.0 / NULLIF(impressions, 0) AS conv_rate
  FROM campaigns
  WHERE date >= CURRENT_DATE - INTERVAL '30 days'
),
ranked AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY region ORDER BY conv_rate DESC) AS rnk
  FROM recent
)
SELECT campaign_id, region, conv_rate, rnk
FROM ranked
WHERE rnk <= 3;

Expect these follow-ups

• Your query runs in 45 seconds on a 200 million row table. What indexes would you add and how do you verify they are being used?
• How do you change this query to use a rolling 30-day window per row rather than a fixed date cutoff?

As asked

You are storing Amazon order records in DynamoDB. Orders arrive at about 100,000 per second during peak. If you use customer_id as the partition key, what problem do you hit, and what strategies do you consider to fix it?

Sample answer outline

The core problem is hot partitions when a small number of customer IDs (or even one, in a test/load scenario) receive a disproportionate share of traffic. Strategies include: adding a suffix to the partition key (key sharding / write sharding), using a composite key like order_date + customer_id to spread writes by time, or using a write-sharding pattern where you append a random number 1-N to the key and fan out reads across all N suffixes. The candidate should weigh each approach against the read pattern. DynamoDB's adaptive capacity helps but does not fully solve the problem for truly hot keys.

Expect these follow-ups

• How does your sharding strategy affect your query pattern for fetching all orders for a customer?
• When would you choose DynamoDB over Aurora for this workload?

As asked

You have a table of user events with columns (user_id, event_name, event_time). The funnel is: app_open -> video_play -> like -> share. Write a SQL query that returns the number of unique users who reached each step of the funnel, in order, within a 7-day window starting from their first app_open.

Sample answer outline

A strong answer uses self-joins or a series of CTEs where each step filters users who completed the previous step within the time window. Using window functions (LAG/LEAD) or conditional aggregation with FILTER is more elegant. The candidate should handle the window constraint correctly (7 days from the user's first app_open, not a fixed calendar window) and explain that the funnel is ordered (a like must come after a video_play for the same user).

Expect these follow-ups

• How would you compute the median time between each funnel step?
• How does your query change if you want the funnel per acquisition channel?

As asked

Given a DoorDash orders table with columns consumer_id, order_id, restaurant_id, order_total, and created_at, write a single SQL query that returns each consumer_id along with their first order date, their most recent order date, their total number of orders, and their average order value. Then add a column showing how many days have passed since their last order.

Sample answer outline

A clean answer uses GROUP BY with MIN, MAX, COUNT, and AVG aggregates, and calculates days since last order as CURRENT_DATE minus MAX(created_at). A stronger answer mentions that if you also need individual order details alongside aggregates, window functions like FIRST_VALUE and LAST_VALUE with PARTITION BY consumer_id are the right tool. They should know when to use GROUP BY vs. window functions.

Reference implementation (sql)

SELECT
  consumer_id,
  MIN(created_at)                                    AS first_order_date,
  MAX(created_at)                                    AS last_order_date,
  COUNT(*)                                           AS total_orders,
  ROUND(AVG(order_total), 2)                         AS avg_order_value,
  CURRENT_DATE - MAX(created_at)::date               AS days_since_last_order
FROM orders
GROUP BY consumer_id;

Expect these follow-ups

• How would you add a column showing the order total of the consumer's most recent order without a subquery?
• The orders table has 2 billion rows. How do you make this query run in under 5 seconds?

As asked

You are starting a new Google internal service and need to pick a storage backend. Walk me through how you would decide between Cloud Bigtable and Cloud Spanner. What workload characteristics push you toward one versus the other?

Sample answer outline

A strong answer focuses on the query pattern and consistency needs: Bigtable is a wide-column NoSQL store with row-key access only, excelling at high-throughput single-row reads and writes with no joins or transactions across rows. Spanner provides full SQL, global strong consistency, and multi-row transactions via TrueTime, at higher cost per query. Time-series, user-activity logs, and event streams favor Bigtable. Financial records, inventory, or anything needing ACID across entities favors Spanner. The candidate should mention Bigtable's HBase compatibility and Spanner's external consistency guarantees.

Expect these follow-ups

• If your read pattern is 80% point lookups and 20% small range scans, how does that affect your row key design in Bigtable?
• How does Spanner's TrueTime mechanism achieve external consistency?

As asked

You have a table connections(user_a INT, user_b INT) where each row means user_a and user_b are direct connections (the relationship is symmetric, so you may see (1,2) but not (2,1)). Write a query to find all second-degree connections for a given member ID, excluding people they are already directly connected to and excluding themselves.

Sample answer outline

The candidate should UNION the connections table in both directions to create a symmetric view, self-join to find friends-of-friends, then LEFT JOIN or NOT EXISTS to exclude direct connections and self. A strong answer discusses index design (index on user_a and user_b separately or a composite), the cost of the NOT IN subquery vs NOT EXISTS, and why this query does not scale to the full LinkedIn graph in SQL.

Reference implementation (sql)

-- Symmetric view of connections
WITH all_connections AS (
  SELECT user_a AS me, user_b AS friend FROM connections
  UNION
  SELECT user_b AS me, user_a AS friend FROM connections
),
direct AS (
  SELECT friend FROM all_connections WHERE me = :member_id
)
SELECT DISTINCT ac2.friend AS second_degree
FROM all_connections ac1
JOIN all_connections ac2 ON ac1.friend = ac2.me
WHERE ac1.me = :member_id
  AND ac2.friend != :member_id
  AND ac2.friend NOT IN (SELECT friend FROM direct);

Expect these follow-ups

• How would you modify this query to also return the count of mutual connections for each second-degree person?
• At what point would you move this logic out of SQL and into a graph database or precomputed store?

As asked

You have a Presto query joining a 10-trillion-row events table with a 50-million-row users table, and it is taking 45 minutes. Walk me through how you would diagnose and optimize it.

Sample answer outline

A strong answer starts with examining the query plan for the join order, broadcast versus partitioned join, and whether the smaller table fits in memory for a broadcast join. Then checks partition pruning on the events table to avoid full scans, data skew on the join key, and whether ORC or Parquet column pruning is happening. The candidate should know that Presto can broadcast small tables to avoid shuffle, and that stats on the Hive metastore drive the cost-based optimizer.

Expect these follow-ups

• The query is partitioned by date but your filter is on a user_id. What happens and how do you fix it?
• How does Raptor (Meta's internal columnar store) change the optimization approach versus Hive-backed tables?

As asked

You have a Products table with columns product_id, category, price, and units_sold. Write a query that returns the top 3 best-selling products within each category, along with their rank. Do not use a subquery with IN or a self-join. Which window function do you reach for and why?

Sample answer outline

The candidate should use ROW_NUMBER(), RANK(), or DENSE_RANK() with PARTITION BY category ORDER BY units_sold DESC, then wrap it in a CTE or subquery and filter WHERE rank <= 3. They should explain the difference between the three: ROW_NUMBER is always unique, RANK skips numbers after ties, DENSE_RANK does not. The choice between them depends on whether ties within the top 3 should both appear. Microsoft SQL Server and Azure SQL both support all three.

Reference implementation (sql)

WITH ranked AS (
  SELECT *,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY units_sold DESC
    ) AS rnk
  FROM Products
)
SELECT * FROM ranked WHERE rnk <= 3;

Expect these follow-ups

• What is the difference between RANK and DENSE_RANK and when would each change your output?
• How would you compute a running total of units_sold over time within each category?

As asked

You have a table with columns: user_id, title_id, play_start (timestamp), play_end (timestamp). Write a query that finds, for each user, the title they watched immediately before they churned (no activity for more than 30 days after that play). The result should include user_id, title_id, and the play_end timestamp.

Sample answer outline

A strong answer uses LEAD() or LAG() window functions to find the next play per user ordered by play_start, then filters for rows where the next play is more than 30 days later or NULL (no next play). The key insight is that users who never returned have no next row, so NULL handling is critical. The candidate should write clean SQL using a CTE to compute the next_play per row, then filter in the outer query.

Reference implementation (sql)

WITH sessions_with_next AS (
  SELECT
    user_id,
    title_id,
    play_end,
    LEAD(play_start) OVER (
      PARTITION BY user_id ORDER BY play_start
    ) AS next_play_start
  FROM viewing_sessions
)
SELECT user_id, title_id, play_end
FROM sessions_with_next
WHERE
  next_play_start IS NULL
  OR next_play_start > play_end + INTERVAL '30 days';

Expect these follow-ups

• How would your query change if you also needed to find the title watched immediately before a subscription upgrade?
• This query runs on 500 million rows. What indexes do you add and why?

As asked

You are reviewing a query against a table with 200 million rows in an Oracle Database. The DBA says there is a B-tree index on the filter column but the query is still doing a full table scan. Walk me through the possible reasons, how you would diagnose which one applies, and when you would consider a bitmap index or function-based index instead.

Sample answer outline

A strong answer covers the common optimizer reasons for index skip (low selectivity, implicit type conversion causing a function on the column, stale statistics, bind peeking issues, or partitioning misalignment), how to use EXPLAIN PLAN and V$SQL to diagnose, and the tradeoffs of bitmap indexes (great for low-cardinality columns in data warehouse queries but very expensive to update under DML) versus function-based indexes for expressions in WHERE clauses.

Expect these follow-ups

• How do you gather fresh statistics without locking the table in a 24x7 production database?
• When would you hint the optimizer versus fixing the root cause?

As asked

You have an Employees table with columns employee_id, name, department_id, and salary. Write a SQL query that returns each employee along with their rank within their department by salary (highest first), and also the difference between their salary and the top salary in their department. Use Oracle SQL syntax.

Sample answer outline

The solution uses RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) for the rank and FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) minus salary for the gap. The candidate should explain the difference between RANK and DENSE_RANK, mention that Oracle SQL uses these ANSI window functions natively, and note that window functions compute per-row without collapsing the result set the way GROUP BY does.

Reference implementation (sql)

SELECT
  employee_id,
  name,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) - salary AS gap_from_top
FROM employees
ORDER BY department_id, dept_rank;

Expect these follow-ups

• How would you rewrite this without window functions, and why is that worse?
• What index would you add to make this query faster on a table with 100 million rows?

As asked

You need to migrate a 5-terabyte Oracle Database from on-premise to OCI with zero downtime and a cutover window of less than 30 seconds. The database serves 10,000 concurrent users. Walk me through the migration plan step by step.

Sample answer outline

A strong answer describes using Oracle GoldenGate for continuous replication, the initial load phase (exporting with Data Pump while GoldenGate captures changes), the lag reduction phase (running both in sync until replication lag is under a few seconds), the cutover sequence (pause writes on the source, wait for zero lag, redirect application connection strings, validate, then switch over), and rollback planning if the cutover fails. The candidate should mention pre-migration steps like character set validation and timezone alignment.

Expect these follow-ups

• How do you validate that all data arrived correctly on OCI without rechecking all 5 terabytes row by row?
• What changes to your plan if the application cannot tolerate even a 1-second pause in writes?

As asked

You are handed an EXPLAIN PLAN that shows Oracle choosing a nested loops join between a 50-million-row fact table and a 5-million-row dimension table, with the fact table as the outer loop. The query is taking 8 minutes when it should take under 10 seconds. Walk me through how you read this plan, why the join order is wrong, and how you fix it.

Sample answer outline

Nested loops are efficient when the outer table is small and the inner table has an index on the join column. With the large fact table as outer, Oracle performs 50 million index lookups on the dimension, which is catastrophically slow. The fix is to drive with the smaller dimension table (using a hint like LEADING or fixing statistics so the optimizer chooses correctly), switch to a hash join for this cardinality, and ensure statistics on both tables are fresh. The candidate should read the COST and ROWS columns to spot the estimate error.

Expect these follow-ups

• When is a nested loops join actually faster than a hash join, even for large tables?
• How do you stop the optimizer from reintroducing the bad plan after the next statistics gather?

As asked

You have two tables in a distributed query engine: an events table at 5 TB and a reference entities table at 200 GB. A common analyst query joins them on an entity ID. Query latency is unacceptably high. Diagnose the likely bottlenecks and describe how you would fix them.

Sample answer outline

The candidate should identify broadcast hash join as the right strategy for a 200 GB dimension table, describe the cost of a shuffle join when the table is too large to broadcast, and suggest partitioning the large table by entity ID to co-locate matching data. They should discuss statistics-based query planning, partition pruning, column pruning, and the trade-off between materialising a pre-joined view and computing the join on demand.

Expect these follow-ups

• The entity reference table is updated every 15 minutes. How does that affect your strategy?
• What would you look at in the query plan output to confirm where the time is being spent?

As asked

Given a table called orders with columns shop_id, created_at, and order_id, write a SQL query that identifies any shop where the order volume in the last 24 hours is more than 10 times the average daily order volume over the prior 30 days. Explain your approach and any edge cases.

Sample answer outline

A strong answer uses a CTE or subquery to compute the prior 30-day average per shop and the last-24-hour count per shop, then joins and filters. Edge cases to name: shops that had zero orders in the prior 30 days (divide by zero), shops that were created in the last 30 days (baseline is unreliable), and the time zone of created_at if it matters for the business. The candidate should use COUNT, GROUP BY, HAVING or WHERE on the computed ratio, and note that an index on (shop_id, created_at) is needed for this to be fast.

Reference implementation (sql)

WITH recent AS (
  SELECT shop_id, COUNT(*) AS orders_24h
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '24 hours'
  GROUP BY shop_id
),
baseline AS (
  SELECT shop_id,
         COUNT(*) / NULLIF(30.0, 0) AS avg_daily_orders
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '31 days'
    AND created_at < NOW() - INTERVAL '24 hours'
  GROUP BY shop_id
)
SELECT r.shop_id, r.orders_24h, b.avg_daily_orders,
       r.orders_24h / b.avg_daily_orders AS spike_ratio
FROM recent r
JOIN baseline b ON r.shop_id = b.shop_id
WHERE b.avg_daily_orders > 0
  AND r.orders_24h > 10 * b.avg_daily_orders
ORDER BY spike_ratio DESC;

Expect these follow-ups

• How would this query change if you needed to run it on a partitioned table where each partition is one day?
• This query will be slow on a table with 10 billion rows. What would you do?

As asked

A customer is complaining that their Snowflake queries run slowly and cost too much. Walk me through how Snowflake virtual warehouses, micro-partitioning, and query pruning actually work, and what you would check first to diagnose the problem.

Sample answer outline

A strong answer explains that Snowflake separates compute (virtual warehouses, MPP clusters of cloud compute nodes on AWS, Azure, or GCP) from storage (object-storage-backed columnar micro-partitions of 50 to 500 MB uncompressed each), that each micro-partition stores min/max and null-count metadata, and that the query optimizer uses that metadata to skip irrelevant partitions without reading them. The candidate should then mention checking QUERY_HISTORY for partition scans, looking at whether the clustering key matches the filter predicates, and considering warehouse size relative to concurrency.

Expect these follow-ups

• When would you recommend adding a clustering key and when would you leave the table unclustered?
• How does automatic clustering differ from manual CLUSTER BY, and what does it cost?

As asked

A data engineer accidentally ran a DELETE with no WHERE clause on a production table. What options do they have to recover, and how exactly do Snowflake's Time Travel and Fail-Safe features work under the hood?

Sample answer outline

Time Travel lets you query or restore data up to 90 days back (default 1 day for standard, up to 90 for Enterprise) using AT or BEFORE clauses referencing a timestamp, offset, or query ID. Fail-Safe is a 7-day non-customer-accessible recovery window that kicks in after Time Travel expires, requiring Snowflake Support to restore. A strong answer clarifies that Time Travel storage counts toward billing and that UNDROP TABLE restores a dropped table within the Time Travel window.

Expect these follow-ups

• If Time Travel is set to 1 day and the mistake was discovered 2 days later, what are the options?
• How would you use CLONE to create a safe testing copy without doubling storage costs?

As asked

Given an events table with columns (user_id, event_type, created_at), write a SQL query that returns, for each user, only their most recent event of each event_type. If there is a tie on created_at, keep the row with the smaller event_id.

Sample answer outline

A strong answer uses ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY created_at DESC, event_id ASC) and then filters WHERE rn = 1 in a CTE or subquery. The candidate should explain the PARTITION BY and ORDER BY semantics clearly, mention that RANK() would not deduplicate ties and DENSE_RANK() would have the same issue, and discuss whether the CTE or a derived table is preferred for readability in Snowflake.

Reference implementation (sql)

-- events table schema
-- user_id     INT
-- event_id    INT
-- event_type  VARCHAR
-- created_at  TIMESTAMP

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type
      ORDER BY created_at DESC, event_id ASC
    ) AS rn
  FROM events
)
SELECT user_id, event_type, created_at, event_id
FROM ranked
WHERE rn = 1;

Expect these follow-ups

• How would you rewrite this using QUALIFY instead of a subquery, and why might that be cleaner in Snowflake?
• If this events table has billions of rows, what performance considerations matter for this query?

As asked

Snowflake supports a QUALIFY clause that is not in standard SQL. Rewrite this query using QUALIFY, and explain when you would reach for it versus a CTE-based approach: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees) SELECT * FROM r WHERE rn = 1;

Sample answer outline

QUALIFY filters on the result of a window function in the same SELECT, similar to how HAVING filters on aggregates. The rewrite is: SELECT * FROM employees QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1. Use it for conciseness when the query is otherwise readable, but prefer CTEs when the window function result is reused or when the logic is complex enough that naming it helps.

Reference implementation (sql)

-- Original CTE approach
WITH r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
  FROM employees
)
SELECT * FROM r WHERE rn = 1;

-- QUALIFY rewrite
SELECT *
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1;

Expect these follow-ups

• Can you combine QUALIFY with WHERE in the same query, and in what order does Snowflake evaluate them?
• What happens if you use QUALIFY with a RANK() function and there are tied salaries?

As asked

Your company stores Social Security numbers in Snowflake and must ensure that analysts can see only the last 4 digits while data engineers see the full value. Walk me through how you would implement this using Dynamic Data Masking, and how the masking policy is enforced at query time.

Sample answer outline

You create a masking policy using CREATE MASKING POLICY, define the condition using CURRENT_ROLE() or IS_ROLE_IN_SESSION() to check whether the querying role has the privileged role, and return the full value for privileged roles and a masked version (e.g., '***-**-' || RIGHT(val, 4)) otherwise. You then apply the policy to the column with ALTER TABLE ... ALTER COLUMN ... SET MASKING POLICY. The policy runs at query time so there is no masked copy of the data stored anywhere.

Reference implementation (sql)

CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('DATA_ENGINEER') THEN val
    ELSE '***-**-' || RIGHT(val, 4)
  END;

ALTER TABLE customers
  ALTER COLUMN ssn
  SET MASKING POLICY ssn_mask;

Expect these follow-ups

• Can a user with ACCOUNTADMIN bypass a masking policy, and how would you prevent that?
• How would you handle masking when the same column appears in a Snowflake Share consumed by a partner account?

As asked

You are maintaining a dimension table that tracks the history of customer address changes as a Type 2 slowly changing dimension. Every night you receive a batch of changed records. Write the SQL logic using Snowflake's MERGE statement to expire the old row and insert the new row, and explain any edge cases you need to handle.

Sample answer outline

A Type 2 MERGE cannot do an expire-and-insert in a single MERGE because standard MERGE does not support multiple actions on the same source row. The correct pattern is two steps: first UPDATE the matched rows to set is_current = FALSE and end_date = CURRENT_DATE where the row has changed, then INSERT new rows for those changed records plus any net-new records. The candidate should mention handling soft deletes, surrogate key generation (using SEQ or UUID), and the importance of deduplicating the source before the MERGE.

Expect these follow-ups

• How would you use Snowflake Streams to make this pipeline incremental rather than full-table each night?
• What is the performance impact of updating many rows in Snowflake versus in a row-store database like Postgres?

As asked

A query that normally finishes in 30 seconds is now taking 15 minutes. You open the Snowflake Query Profile. Walk me through what you look at, in what order, and what each bottleneck indicator tells you.

Sample answer outline

Start with the most expensive node by execution time in the operator tree. Check for a large Bytes Spilled to Local Storage or Bytes Spilled to Remote Storage value, which means the warehouse ran out of memory and spilled to disk or S3. Look at Partitions Scanned vs Total to see whether pruning is effective. A high percentage of time in Remote Disk IO usually means spilling or reading many micro-partitions. Cartesian join explosion shows up as a very high output-to-input row ratio. Skewed execution shows as a wide gap between max and average time per worker node.

Expect these follow-ups

• You see 90% of execution time is in a single join node with a high spill. What are your options?
• How do you tell the difference between a query that is slow because the warehouse is too small and one that is slow because the SQL is poorly written?

As asked

You have a 10 TB fact table that is constantly queried by date range and region. Response time must be under 5 seconds for dashboard queries. Your options include adding a Snowflake clustering key, creating a materialized view, or pre-aggregating into a summary table. Walk through when you would choose each and why.

Sample answer outline

Clustering keys help when queries filter on a high-cardinality column that is not already ordered in the micro-partitions (e.g., a date column on an event table loaded out of order). Clustering reduces partitions scanned but adds reclustering credit costs. Materialized views pre-compute results and are refreshed automatically, which is good for aggregate queries but limited in what SQL they support (no UDFs, limited joins). A pre-aggregated summary table gives the most control and lowest query latency but requires custom refresh logic. For a date-filtered fact table, clustering is often the right first move; materialized views fit pre-defined aggregations; and summary tables fit tightly scoped dashboard use cases.

Expect these follow-ups

• How do you monitor whether a clustering key is still effective as new data loads?
• If automatic clustering is enabled, when does Snowflake actually do the reclustering and what does it cost?

As asked

Two sessions are running simultaneously. Session A is loading data into a table using COPY INTO. Session B is querying the same table. What does Session B see, and how does Snowflake's isolation model work? How does this differ from a traditional row-locking database?

Sample answer outline

Snowflake uses read-committed isolation: each statement sees a consistent snapshot of data as of the moment the statement began, achieved via MVCC (copy-on-write micro-partitions rather than in-place edits). Session B sees the table as it was when its query started, regardless of what Session A commits during that query. There is no row-locking because Snowflake writes new micro-partitions rather than modifying existing ones. DML is serialized at the table level: if two transactions attempt to modify the same table concurrently, Snowflake detects the write-write conflict and fails the second transaction. This differs from MVCC in Postgres, where row-level locks allow higher write concurrency but create vacuum overhead.

Expect these follow-ups

• If two concurrent MERGE statements target the same table, what happens?
• How would you design around Snowflake's table-level write serialization if you have a high-frequency insert workload?

As asked

You have a table with a VARIANT column called payload that stores JSON like {"user_id": 123, "events": [{"type": "click", "ts": 1700000000}, {"type": "view", "ts": 1700000010}]}. Write a query that produces one row per event with columns user_id, event_type, and event_ts.

Sample answer outline

The answer uses LATERAL FLATTEN on the events array to unnest it, then extracts fields using colon dot notation (f.value:type::STRING, f.value:ts::TIMESTAMP). The key points are understanding that FLATTEN returns a row per array element, that the colon is Snowflake's VARIANT path accessor, and that explicit casting (::STRING, ::INT) is needed because VARIANT values are typed at runtime. The candidate should also mention handling NULLs if the events key is absent.

Reference implementation (sql)

SELECT
  payload:user_id::INT          AS user_id,
  f.value:type::STRING          AS event_type,
  f.value:ts::TIMESTAMP         AS event_ts
FROM raw_events,
  LATERAL FLATTEN(INPUT => payload:events) AS f;

Expect these follow-ups

• What does the RECURSIVE option to FLATTEN do, and when would you need it?
• If the events array can be deeply nested (events inside events), how would you approach that?

As asked

A query against a 500 million row tracks table that joins to a 2 billion row user_plays table is taking 12 seconds. EXPLAIN ANALYZE shows a sequential scan on user_plays despite an index on (user_id, track_id). What are the five things you check first, and what is the most likely fix?

Sample answer outline

A strong answer checks: whether the planner chose a seq scan because the estimated row count is way off (stale statistics, run ANALYZE), whether the index is bloated or partially dead (VACUUM ANALYZE), whether the query is selecting too many columns forcing a heap fetch that kills index-only scan efficiency, whether the join order is wrong (forcing a nested loop on the large table), and whether connection pooling is holding locks. Most likely fix for this scenario: stale statistics causing a bad plan, or the index column order does not match the query's filter.

Expect these follow-ups

• When would a partial index on user_plays help and how would you create one?
• How do you test a query plan change without running it on production first?

As asked

You have two tables: plays(user_id, track_id, played_at) and tracks(track_id, genre). Write a SQL query that returns, for each user and each calendar month, the genre they played the most times. If two genres are tied, return the one that comes first alphabetically.

Sample answer outline

A strong answer uses a CTE to count plays per (user_id, month, genre), then uses ROW_NUMBER() with PARTITION BY (user_id, month) ORDER BY (play_count DESC, genre ASC) to rank genres per user per month, then filters to rank = 1. The candidate distinguishes between ROW_NUMBER (handles ties by picking one deterministically) versus RANK or DENSE_RANK (which would return multiple rows on a tie), and explains why ROW_NUMBER is correct given the alphabetical tiebreaker.

Reference implementation (sql)

-- plays(user_id BIGINT, track_id BIGINT, played_at TIMESTAMP)
-- tracks(track_id BIGINT, genre VARCHAR)

WITH monthly_genre_counts AS (
    SELECT
        p.user_id,
        DATE_TRUNC('month', p.played_at) AS month,
        t.genre,
        COUNT(*) AS play_count
    FROM plays p
    JOIN tracks t ON p.track_id = t.track_id
    GROUP BY 1, 2, 3
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, month
            ORDER BY play_count DESC, genre ASC
        ) AS rn
    FROM monthly_genre_counts
)
SELECT user_id, month, genre, play_count
FROM ranked
WHERE rn = 1;

Expect these follow-ups

• How would you change this query to return the top 3 genres per user per month instead of just the top 1?
• This query over 2 billion play rows is slow. What index would you add and why?

As asked

You have a PostgreSQL table with 2 billion rows of payment transactions: transaction_id, merchant_id, customer_id, amount_cents, currency, created_at, status. A merchant dashboard query that groups by date and status for a single merchant is taking 30 seconds. Walk me through how you diagnose and fix it.

Sample answer outline

A strong answer starts with EXPLAIN ANALYZE to identify whether the query is doing a sequential scan or an inefficient index scan. The fix is typically a composite index on (merchant_id, created_at) or a partial index if only certain statuses are queried. The candidate should discuss cardinality of the indexed columns, the cost of index maintenance on write-heavy tables, and whether a denormalized summary table or a materialized view updated incrementally would be a better long-term solution for reporting queries.

Reference implementation (sql)

-- Original slow query
SELECT DATE(created_at) AS day, status, SUM(amount_cents)
FROM transactions
WHERE merchant_id = $1
  AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY day, status
ORDER BY day;

-- Suggested index (create concurrently in production)
CREATE INDEX CONCURRENTLY idx_txn_merchant_created
  ON transactions(merchant_id, created_at);

Expect these follow-ups

• How do you add the index without locking the table for 30 minutes in production?
• What would change if this were a multi-tenant table and hundreds of merchants ran this query concurrently?

As asked

Given a table `tweet_events(tweet_id BIGINT, event_type VARCHAR, user_id BIGINT, created_at TIMESTAMP)` where event_type can be 'tweet', 'retweet', or 'like', write a SQL query that returns the top 10 most retweeted original tweets for each hour in the last 7 days, along with the retweet count.

Sample answer outline

A strong answer uses DATE_TRUNC('hour', created_at) to bucket by hour, filters on event_type = 'retweet', groups by (hour_bucket, tweet_id), applies a ROW_NUMBER() or RANK() window function partitioned by hour to pick the top 10 per hour, and adds a WHERE clause filtering to the last 7 days on created_at. The candidate should address index design (composite index on (event_type, created_at) or a partial index) and explain why a subquery or CTE is cleaner than a derived table for this pattern.

Reference implementation (sql)

WITH hourly_retweets AS (
  SELECT
    DATE_TRUNC('hour', created_at) AS hour_bucket,
    tweet_id,
    COUNT(*) AS retweet_count
  FROM tweet_events
  WHERE event_type = 'retweet'
    AND created_at >= NOW() - INTERVAL '7 days'
  GROUP BY 1, 2
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY hour_bucket ORDER BY retweet_count DESC) AS rn
  FROM hourly_retweets
)
SELECT hour_bucket, tweet_id, retweet_count
FROM ranked
WHERE rn <= 10
ORDER BY hour_bucket DESC, retweet_count DESC;

Expect these follow-ups

• This query runs on a table with 50 billion rows. What partitioning strategy would you add to the table and what index would make this query fast?
• The product team wants the same report but filtered by geographic region. How does that change the query and the index?

As asked

You are using Presto or Hive on top of Uber's data lake. You want to understand whether average driver wait time at pickup has been increasing over the past 90 days, broken down by city and time of day. Walk me through the SQL you would write and any data quality issues you would watch for.

Sample answer outline

A strong answer writes a query that joins trips to a city lookup, computes wait time as the difference between driver_arrived and rider_requested timestamps, buckets by city and hour of day, filters to the 90-day window, and aggregates with AVG and COUNT. The candidate should flag nulls in arrival timestamps for cancelled trips, timezone handling when bucketing by hour, and the difference between median and mean for a skewed distribution.

Expect these follow-ups

• Your query scans 90 days of trip data partitioned by date. How do you ensure Presto uses partition pruning and does not do a full table scan?
• The average wait time looks fine globally but one city's p99 wait time is 40 minutes. How would you investigate whether it is a data quality issue or a real operational problem?

As asked

Using SQL, how would you compute the 30-day retention rate for cohorts of users grouped by their signup week? Assume a table 'events' with columns user_id, event_date, and event_type.

Sample answer outline

The candidate should write a query that finds each user's first event date (cohort assignment) using MIN(event_date), joins back to events to find users with activity 28 to 35 days later, and groups by cohort week. They should use date arithmetic correctly, discuss the denominator (cohort size), and mention edge cases like users who only have one event.

Expect these follow-ups

• How would you extend this to compute a full 12-week retention curve without repeating the join 12 times?

As asked

You have a table 'events' with columns user_id, event_name, and event_timestamp. The funnel steps are: page_view, add_to_cart, checkout_start, purchase. Write a SQL query that computes the conversion rate from each step to the next.

Sample answer outline

The candidate should write a query that uses conditional aggregation or window functions to find the first occurrence of each event per user, then count users who reached each step (with each step requiring all prior steps in order). The key detail is using MIN(event_timestamp) per user per event type and ensuring the ordering is preserved, not just presence. Funnel conversion = step N count divided by step 1 count.

Expect these follow-ups

• How would you modify the query to require that steps happen in sequence within a 7-day window?

As asked

You have an Adobe Analytics database with a campaigns table (campaign_id, region, impressions, conversions, date). Write a SQL query that, for each region, returns the top 3 campaigns by conversion rate in the last 30 days. Include the campaign rank within its region.

Sample answer outline

Use a CTE to filter the last 30 days and compute conversion_rate = conversions / NULLIF(impressions, 0), then apply RANK() OVER (PARTITION BY region ORDER BY conversion_rate DESC). Filter the outer query WHERE rank <= 3. A strong answer handles the NULLIF for division by zero, considers whether RANK or DENSE_RANK is more appropriate (DENSE_RANK if you want exactly 3 results per region when there are ties), and mentions index hints for the date filter.

Reference implementation (sql)

WITH recent AS (
  SELECT
    campaign_id,
    region,
    conversions * 1.0 / NULLIF(impressions, 0) AS conv_rate
  FROM campaigns
  WHERE date >= CURRENT_DATE - INTERVAL '30 days'
),
ranked AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY region ORDER BY conv_rate DESC) AS rnk
  FROM recent
)
SELECT campaign_id, region, conv_rate, rnk
FROM ranked
WHERE rnk <= 3;

Expect these follow-ups

• Your query runs in 45 seconds on a 200 million row table. What indexes would you add and how do you verify they are being used?
• How do you change this query to use a rolling 30-day window per row rather than a fixed date cutoff?

As asked

You are storing Amazon order records in DynamoDB. Orders arrive at about 100,000 per second during peak. If you use customer_id as the partition key, what problem do you hit, and what strategies do you consider to fix it?

Sample answer outline

The core problem is hot partitions when a small number of customer IDs (or even one, in a test/load scenario) receive a disproportionate share of traffic. Strategies include: adding a suffix to the partition key (key sharding / write sharding), using a composite key like order_date + customer_id to spread writes by time, or using a write-sharding pattern where you append a random number 1-N to the key and fan out reads across all N suffixes. The candidate should weigh each approach against the read pattern. DynamoDB's adaptive capacity helps but does not fully solve the problem for truly hot keys.

Expect these follow-ups

• How does your sharding strategy affect your query pattern for fetching all orders for a customer?
• When would you choose DynamoDB over Aurora for this workload?

As asked

You have a table of user events with columns (user_id, event_name, event_time). The funnel is: app_open -> video_play -> like -> share. Write a SQL query that returns the number of unique users who reached each step of the funnel, in order, within a 7-day window starting from their first app_open.

Sample answer outline

A strong answer uses self-joins or a series of CTEs where each step filters users who completed the previous step within the time window. Using window functions (LAG/LEAD) or conditional aggregation with FILTER is more elegant. The candidate should handle the window constraint correctly (7 days from the user's first app_open, not a fixed calendar window) and explain that the funnel is ordered (a like must come after a video_play for the same user).

Expect these follow-ups

• How would you compute the median time between each funnel step?
• How does your query change if you want the funnel per acquisition channel?

As asked

Given a DoorDash orders table with columns consumer_id, order_id, restaurant_id, order_total, and created_at, write a single SQL query that returns each consumer_id along with their first order date, their most recent order date, their total number of orders, and their average order value. Then add a column showing how many days have passed since their last order.

Sample answer outline

A clean answer uses GROUP BY with MIN, MAX, COUNT, and AVG aggregates, and calculates days since last order as CURRENT_DATE minus MAX(created_at). A stronger answer mentions that if you also need individual order details alongside aggregates, window functions like FIRST_VALUE and LAST_VALUE with PARTITION BY consumer_id are the right tool. They should know when to use GROUP BY vs. window functions.

Reference implementation (sql)

SELECT
  consumer_id,
  MIN(created_at)                                    AS first_order_date,
  MAX(created_at)                                    AS last_order_date,
  COUNT(*)                                           AS total_orders,
  ROUND(AVG(order_total), 2)                         AS avg_order_value,
  CURRENT_DATE - MAX(created_at)::date               AS days_since_last_order
FROM orders
GROUP BY consumer_id;

Expect these follow-ups

• How would you add a column showing the order total of the consumer's most recent order without a subquery?
• The orders table has 2 billion rows. How do you make this query run in under 5 seconds?

As asked

You are starting a new Google internal service and need to pick a storage backend. Walk me through how you would decide between Cloud Bigtable and Cloud Spanner. What workload characteristics push you toward one versus the other?

Sample answer outline

A strong answer focuses on the query pattern and consistency needs: Bigtable is a wide-column NoSQL store with row-key access only, excelling at high-throughput single-row reads and writes with no joins or transactions across rows. Spanner provides full SQL, global strong consistency, and multi-row transactions via TrueTime, at higher cost per query. Time-series, user-activity logs, and event streams favor Bigtable. Financial records, inventory, or anything needing ACID across entities favors Spanner. The candidate should mention Bigtable's HBase compatibility and Spanner's external consistency guarantees.

Expect these follow-ups

• If your read pattern is 80% point lookups and 20% small range scans, how does that affect your row key design in Bigtable?
• How does Spanner's TrueTime mechanism achieve external consistency?

As asked

You have a table connections(user_a INT, user_b INT) where each row means user_a and user_b are direct connections (the relationship is symmetric, so you may see (1,2) but not (2,1)). Write a query to find all second-degree connections for a given member ID, excluding people they are already directly connected to and excluding themselves.

Sample answer outline

The candidate should UNION the connections table in both directions to create a symmetric view, self-join to find friends-of-friends, then LEFT JOIN or NOT EXISTS to exclude direct connections and self. A strong answer discusses index design (index on user_a and user_b separately or a composite), the cost of the NOT IN subquery vs NOT EXISTS, and why this query does not scale to the full LinkedIn graph in SQL.

Reference implementation (sql)

-- Symmetric view of connections
WITH all_connections AS (
  SELECT user_a AS me, user_b AS friend FROM connections
  UNION
  SELECT user_b AS me, user_a AS friend FROM connections
),
direct AS (
  SELECT friend FROM all_connections WHERE me = :member_id
)
SELECT DISTINCT ac2.friend AS second_degree
FROM all_connections ac1
JOIN all_connections ac2 ON ac1.friend = ac2.me
WHERE ac1.me = :member_id
  AND ac2.friend != :member_id
  AND ac2.friend NOT IN (SELECT friend FROM direct);

Expect these follow-ups

• How would you modify this query to also return the count of mutual connections for each second-degree person?
• At what point would you move this logic out of SQL and into a graph database or precomputed store?

As asked

You have a Presto query joining a 10-trillion-row events table with a 50-million-row users table, and it is taking 45 minutes. Walk me through how you would diagnose and optimize it.

Sample answer outline

A strong answer starts with examining the query plan for the join order, broadcast versus partitioned join, and whether the smaller table fits in memory for a broadcast join. Then checks partition pruning on the events table to avoid full scans, data skew on the join key, and whether ORC or Parquet column pruning is happening. The candidate should know that Presto can broadcast small tables to avoid shuffle, and that stats on the Hive metastore drive the cost-based optimizer.

Expect these follow-ups

• The query is partitioned by date but your filter is on a user_id. What happens and how do you fix it?
• How does Raptor (Meta's internal columnar store) change the optimization approach versus Hive-backed tables?

As asked

You have a Products table with columns product_id, category, price, and units_sold. Write a query that returns the top 3 best-selling products within each category, along with their rank. Do not use a subquery with IN or a self-join. Which window function do you reach for and why?

Sample answer outline

The candidate should use ROW_NUMBER(), RANK(), or DENSE_RANK() with PARTITION BY category ORDER BY units_sold DESC, then wrap it in a CTE or subquery and filter WHERE rank <= 3. They should explain the difference between the three: ROW_NUMBER is always unique, RANK skips numbers after ties, DENSE_RANK does not. The choice between them depends on whether ties within the top 3 should both appear. Microsoft SQL Server and Azure SQL both support all three.

Reference implementation (sql)

WITH ranked AS (
  SELECT *,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY units_sold DESC
    ) AS rnk
  FROM Products
)
SELECT * FROM ranked WHERE rnk <= 3;

Expect these follow-ups

• What is the difference between RANK and DENSE_RANK and when would each change your output?
• How would you compute a running total of units_sold over time within each category?

As asked

You have a table with columns: user_id, title_id, play_start (timestamp), play_end (timestamp). Write a query that finds, for each user, the title they watched immediately before they churned (no activity for more than 30 days after that play). The result should include user_id, title_id, and the play_end timestamp.

Sample answer outline

A strong answer uses LEAD() or LAG() window functions to find the next play per user ordered by play_start, then filters for rows where the next play is more than 30 days later or NULL (no next play). The key insight is that users who never returned have no next row, so NULL handling is critical. The candidate should write clean SQL using a CTE to compute the next_play per row, then filter in the outer query.

Reference implementation (sql)

WITH sessions_with_next AS (
  SELECT
    user_id,
    title_id,
    play_end,
    LEAD(play_start) OVER (
      PARTITION BY user_id ORDER BY play_start
    ) AS next_play_start
  FROM viewing_sessions
)
SELECT user_id, title_id, play_end
FROM sessions_with_next
WHERE
  next_play_start IS NULL
  OR next_play_start > play_end + INTERVAL '30 days';

Expect these follow-ups

• How would your query change if you also needed to find the title watched immediately before a subscription upgrade?
• This query runs on 500 million rows. What indexes do you add and why?

As asked

You are reviewing a query against a table with 200 million rows in an Oracle Database. The DBA says there is a B-tree index on the filter column but the query is still doing a full table scan. Walk me through the possible reasons, how you would diagnose which one applies, and when you would consider a bitmap index or function-based index instead.

Sample answer outline

A strong answer covers the common optimizer reasons for index skip (low selectivity, implicit type conversion causing a function on the column, stale statistics, bind peeking issues, or partitioning misalignment), how to use EXPLAIN PLAN and V$SQL to diagnose, and the tradeoffs of bitmap indexes (great for low-cardinality columns in data warehouse queries but very expensive to update under DML) versus function-based indexes for expressions in WHERE clauses.

Expect these follow-ups

• How do you gather fresh statistics without locking the table in a 24x7 production database?
• When would you hint the optimizer versus fixing the root cause?

As asked

You have an Employees table with columns employee_id, name, department_id, and salary. Write a SQL query that returns each employee along with their rank within their department by salary (highest first), and also the difference between their salary and the top salary in their department. Use Oracle SQL syntax.

Sample answer outline

The solution uses RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) for the rank and FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) minus salary for the gap. The candidate should explain the difference between RANK and DENSE_RANK, mention that Oracle SQL uses these ANSI window functions natively, and note that window functions compute per-row without collapsing the result set the way GROUP BY does.

Reference implementation (sql)

SELECT
  employee_id,
  name,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) - salary AS gap_from_top
FROM employees
ORDER BY department_id, dept_rank;

Expect these follow-ups

• How would you rewrite this without window functions, and why is that worse?
• What index would you add to make this query faster on a table with 100 million rows?

As asked

You need to migrate a 5-terabyte Oracle Database from on-premise to OCI with zero downtime and a cutover window of less than 30 seconds. The database serves 10,000 concurrent users. Walk me through the migration plan step by step.

Sample answer outline

A strong answer describes using Oracle GoldenGate for continuous replication, the initial load phase (exporting with Data Pump while GoldenGate captures changes), the lag reduction phase (running both in sync until replication lag is under a few seconds), the cutover sequence (pause writes on the source, wait for zero lag, redirect application connection strings, validate, then switch over), and rollback planning if the cutover fails. The candidate should mention pre-migration steps like character set validation and timezone alignment.

Expect these follow-ups

• How do you validate that all data arrived correctly on OCI without rechecking all 5 terabytes row by row?
• What changes to your plan if the application cannot tolerate even a 1-second pause in writes?

As asked

You are handed an EXPLAIN PLAN that shows Oracle choosing a nested loops join between a 50-million-row fact table and a 5-million-row dimension table, with the fact table as the outer loop. The query is taking 8 minutes when it should take under 10 seconds. Walk me through how you read this plan, why the join order is wrong, and how you fix it.

Sample answer outline

Nested loops are efficient when the outer table is small and the inner table has an index on the join column. With the large fact table as outer, Oracle performs 50 million index lookups on the dimension, which is catastrophically slow. The fix is to drive with the smaller dimension table (using a hint like LEADING or fixing statistics so the optimizer chooses correctly), switch to a hash join for this cardinality, and ensure statistics on both tables are fresh. The candidate should read the COST and ROWS columns to spot the estimate error.

Expect these follow-ups

• When is a nested loops join actually faster than a hash join, even for large tables?
• How do you stop the optimizer from reintroducing the bad plan after the next statistics gather?

As asked

You have two tables in a distributed query engine: an events table at 5 TB and a reference entities table at 200 GB. A common analyst query joins them on an entity ID. Query latency is unacceptably high. Diagnose the likely bottlenecks and describe how you would fix them.

Sample answer outline

The candidate should identify broadcast hash join as the right strategy for a 200 GB dimension table, describe the cost of a shuffle join when the table is too large to broadcast, and suggest partitioning the large table by entity ID to co-locate matching data. They should discuss statistics-based query planning, partition pruning, column pruning, and the trade-off between materialising a pre-joined view and computing the join on demand.

Expect these follow-ups

• The entity reference table is updated every 15 minutes. How does that affect your strategy?
• What would you look at in the query plan output to confirm where the time is being spent?

As asked

Given a table called orders with columns shop_id, created_at, and order_id, write a SQL query that identifies any shop where the order volume in the last 24 hours is more than 10 times the average daily order volume over the prior 30 days. Explain your approach and any edge cases.

Sample answer outline

A strong answer uses a CTE or subquery to compute the prior 30-day average per shop and the last-24-hour count per shop, then joins and filters. Edge cases to name: shops that had zero orders in the prior 30 days (divide by zero), shops that were created in the last 30 days (baseline is unreliable), and the time zone of created_at if it matters for the business. The candidate should use COUNT, GROUP BY, HAVING or WHERE on the computed ratio, and note that an index on (shop_id, created_at) is needed for this to be fast.

Reference implementation (sql)

WITH recent AS (
  SELECT shop_id, COUNT(*) AS orders_24h
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '24 hours'
  GROUP BY shop_id
),
baseline AS (
  SELECT shop_id,
         COUNT(*) / NULLIF(30.0, 0) AS avg_daily_orders
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '31 days'
    AND created_at < NOW() - INTERVAL '24 hours'
  GROUP BY shop_id
)
SELECT r.shop_id, r.orders_24h, b.avg_daily_orders,
       r.orders_24h / b.avg_daily_orders AS spike_ratio
FROM recent r
JOIN baseline b ON r.shop_id = b.shop_id
WHERE b.avg_daily_orders > 0
  AND r.orders_24h > 10 * b.avg_daily_orders
ORDER BY spike_ratio DESC;

Expect these follow-ups

• How would this query change if you needed to run it on a partitioned table where each partition is one day?
• This query will be slow on a table with 10 billion rows. What would you do?

As asked

A customer is complaining that their Snowflake queries run slowly and cost too much. Walk me through how Snowflake virtual warehouses, micro-partitioning, and query pruning actually work, and what you would check first to diagnose the problem.

Sample answer outline

A strong answer explains that Snowflake separates compute (virtual warehouses, MPP clusters of cloud compute nodes on AWS, Azure, or GCP) from storage (object-storage-backed columnar micro-partitions of 50 to 500 MB uncompressed each), that each micro-partition stores min/max and null-count metadata, and that the query optimizer uses that metadata to skip irrelevant partitions without reading them. The candidate should then mention checking QUERY_HISTORY for partition scans, looking at whether the clustering key matches the filter predicates, and considering warehouse size relative to concurrency.

Expect these follow-ups

• When would you recommend adding a clustering key and when would you leave the table unclustered?
• How does automatic clustering differ from manual CLUSTER BY, and what does it cost?

As asked

A data engineer accidentally ran a DELETE with no WHERE clause on a production table. What options do they have to recover, and how exactly do Snowflake's Time Travel and Fail-Safe features work under the hood?

Sample answer outline

Time Travel lets you query or restore data up to 90 days back (default 1 day for standard, up to 90 for Enterprise) using AT or BEFORE clauses referencing a timestamp, offset, or query ID. Fail-Safe is a 7-day non-customer-accessible recovery window that kicks in after Time Travel expires, requiring Snowflake Support to restore. A strong answer clarifies that Time Travel storage counts toward billing and that UNDROP TABLE restores a dropped table within the Time Travel window.

Expect these follow-ups

• If Time Travel is set to 1 day and the mistake was discovered 2 days later, what are the options?
• How would you use CLONE to create a safe testing copy without doubling storage costs?

As asked

Given an events table with columns (user_id, event_type, created_at), write a SQL query that returns, for each user, only their most recent event of each event_type. If there is a tie on created_at, keep the row with the smaller event_id.

Sample answer outline

A strong answer uses ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY created_at DESC, event_id ASC) and then filters WHERE rn = 1 in a CTE or subquery. The candidate should explain the PARTITION BY and ORDER BY semantics clearly, mention that RANK() would not deduplicate ties and DENSE_RANK() would have the same issue, and discuss whether the CTE or a derived table is preferred for readability in Snowflake.

Reference implementation (sql)

-- events table schema
-- user_id     INT
-- event_id    INT
-- event_type  VARCHAR
-- created_at  TIMESTAMP

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type
      ORDER BY created_at DESC, event_id ASC
    ) AS rn
  FROM events
)
SELECT user_id, event_type, created_at, event_id
FROM ranked
WHERE rn = 1;

Expect these follow-ups

• How would you rewrite this using QUALIFY instead of a subquery, and why might that be cleaner in Snowflake?
• If this events table has billions of rows, what performance considerations matter for this query?

As asked

Snowflake supports a QUALIFY clause that is not in standard SQL. Rewrite this query using QUALIFY, and explain when you would reach for it versus a CTE-based approach: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees) SELECT * FROM r WHERE rn = 1;

Sample answer outline

QUALIFY filters on the result of a window function in the same SELECT, similar to how HAVING filters on aggregates. The rewrite is: SELECT * FROM employees QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1. Use it for conciseness when the query is otherwise readable, but prefer CTEs when the window function result is reused or when the logic is complex enough that naming it helps.

Reference implementation (sql)

-- Original CTE approach
WITH r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
  FROM employees
)
SELECT * FROM r WHERE rn = 1;

-- QUALIFY rewrite
SELECT *
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1;

Expect these follow-ups

• Can you combine QUALIFY with WHERE in the same query, and in what order does Snowflake evaluate them?
• What happens if you use QUALIFY with a RANK() function and there are tied salaries?

As asked

Your company stores Social Security numbers in Snowflake and must ensure that analysts can see only the last 4 digits while data engineers see the full value. Walk me through how you would implement this using Dynamic Data Masking, and how the masking policy is enforced at query time.

Sample answer outline

You create a masking policy using CREATE MASKING POLICY, define the condition using CURRENT_ROLE() or IS_ROLE_IN_SESSION() to check whether the querying role has the privileged role, and return the full value for privileged roles and a masked version (e.g., '***-**-' || RIGHT(val, 4)) otherwise. You then apply the policy to the column with ALTER TABLE ... ALTER COLUMN ... SET MASKING POLICY. The policy runs at query time so there is no masked copy of the data stored anywhere.

Reference implementation (sql)

CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('DATA_ENGINEER') THEN val
    ELSE '***-**-' || RIGHT(val, 4)
  END;

ALTER TABLE customers
  ALTER COLUMN ssn
  SET MASKING POLICY ssn_mask;

Expect these follow-ups

• Can a user with ACCOUNTADMIN bypass a masking policy, and how would you prevent that?
• How would you handle masking when the same column appears in a Snowflake Share consumed by a partner account?

As asked

You are maintaining a dimension table that tracks the history of customer address changes as a Type 2 slowly changing dimension. Every night you receive a batch of changed records. Write the SQL logic using Snowflake's MERGE statement to expire the old row and insert the new row, and explain any edge cases you need to handle.

Sample answer outline

A Type 2 MERGE cannot do an expire-and-insert in a single MERGE because standard MERGE does not support multiple actions on the same source row. The correct pattern is two steps: first UPDATE the matched rows to set is_current = FALSE and end_date = CURRENT_DATE where the row has changed, then INSERT new rows for those changed records plus any net-new records. The candidate should mention handling soft deletes, surrogate key generation (using SEQ or UUID), and the importance of deduplicating the source before the MERGE.

Expect these follow-ups

• How would you use Snowflake Streams to make this pipeline incremental rather than full-table each night?
• What is the performance impact of updating many rows in Snowflake versus in a row-store database like Postgres?

As asked

A query that normally finishes in 30 seconds is now taking 15 minutes. You open the Snowflake Query Profile. Walk me through what you look at, in what order, and what each bottleneck indicator tells you.

Sample answer outline

Start with the most expensive node by execution time in the operator tree. Check for a large Bytes Spilled to Local Storage or Bytes Spilled to Remote Storage value, which means the warehouse ran out of memory and spilled to disk or S3. Look at Partitions Scanned vs Total to see whether pruning is effective. A high percentage of time in Remote Disk IO usually means spilling or reading many micro-partitions. Cartesian join explosion shows up as a very high output-to-input row ratio. Skewed execution shows as a wide gap between max and average time per worker node.

Expect these follow-ups

• You see 90% of execution time is in a single join node with a high spill. What are your options?
• How do you tell the difference between a query that is slow because the warehouse is too small and one that is slow because the SQL is poorly written?

As asked

You have a 10 TB fact table that is constantly queried by date range and region. Response time must be under 5 seconds for dashboard queries. Your options include adding a Snowflake clustering key, creating a materialized view, or pre-aggregating into a summary table. Walk through when you would choose each and why.

Sample answer outline

Clustering keys help when queries filter on a high-cardinality column that is not already ordered in the micro-partitions (e.g., a date column on an event table loaded out of order). Clustering reduces partitions scanned but adds reclustering credit costs. Materialized views pre-compute results and are refreshed automatically, which is good for aggregate queries but limited in what SQL they support (no UDFs, limited joins). A pre-aggregated summary table gives the most control and lowest query latency but requires custom refresh logic. For a date-filtered fact table, clustering is often the right first move; materialized views fit pre-defined aggregations; and summary tables fit tightly scoped dashboard use cases.

Expect these follow-ups

• How do you monitor whether a clustering key is still effective as new data loads?
• If automatic clustering is enabled, when does Snowflake actually do the reclustering and what does it cost?

As asked

Two sessions are running simultaneously. Session A is loading data into a table using COPY INTO. Session B is querying the same table. What does Session B see, and how does Snowflake's isolation model work? How does this differ from a traditional row-locking database?

Sample answer outline

Snowflake uses read-committed isolation: each statement sees a consistent snapshot of data as of the moment the statement began, achieved via MVCC (copy-on-write micro-partitions rather than in-place edits). Session B sees the table as it was when its query started, regardless of what Session A commits during that query. There is no row-locking because Snowflake writes new micro-partitions rather than modifying existing ones. DML is serialized at the table level: if two transactions attempt to modify the same table concurrently, Snowflake detects the write-write conflict and fails the second transaction. This differs from MVCC in Postgres, where row-level locks allow higher write concurrency but create vacuum overhead.

Expect these follow-ups

• If two concurrent MERGE statements target the same table, what happens?
• How would you design around Snowflake's table-level write serialization if you have a high-frequency insert workload?

As asked

You have a table with a VARIANT column called payload that stores JSON like {"user_id": 123, "events": [{"type": "click", "ts": 1700000000}, {"type": "view", "ts": 1700000010}]}. Write a query that produces one row per event with columns user_id, event_type, and event_ts.

Sample answer outline

The answer uses LATERAL FLATTEN on the events array to unnest it, then extracts fields using colon dot notation (f.value:type::STRING, f.value:ts::TIMESTAMP). The key points are understanding that FLATTEN returns a row per array element, that the colon is Snowflake's VARIANT path accessor, and that explicit casting (::STRING, ::INT) is needed because VARIANT values are typed at runtime. The candidate should also mention handling NULLs if the events key is absent.

Reference implementation (sql)

SELECT
  payload:user_id::INT          AS user_id,
  f.value:type::STRING          AS event_type,
  f.value:ts::TIMESTAMP         AS event_ts
FROM raw_events,
  LATERAL FLATTEN(INPUT => payload:events) AS f;

Expect these follow-ups

• What does the RECURSIVE option to FLATTEN do, and when would you need it?
• If the events array can be deeply nested (events inside events), how would you approach that?

As asked

A query against a 500 million row tracks table that joins to a 2 billion row user_plays table is taking 12 seconds. EXPLAIN ANALYZE shows a sequential scan on user_plays despite an index on (user_id, track_id). What are the five things you check first, and what is the most likely fix?

Sample answer outline

A strong answer checks: whether the planner chose a seq scan because the estimated row count is way off (stale statistics, run ANALYZE), whether the index is bloated or partially dead (VACUUM ANALYZE), whether the query is selecting too many columns forcing a heap fetch that kills index-only scan efficiency, whether the join order is wrong (forcing a nested loop on the large table), and whether connection pooling is holding locks. Most likely fix for this scenario: stale statistics causing a bad plan, or the index column order does not match the query's filter.

Expect these follow-ups

• When would a partial index on user_plays help and how would you create one?
• How do you test a query plan change without running it on production first?

As asked

You have two tables: plays(user_id, track_id, played_at) and tracks(track_id, genre). Write a SQL query that returns, for each user and each calendar month, the genre they played the most times. If two genres are tied, return the one that comes first alphabetically.

Sample answer outline

A strong answer uses a CTE to count plays per (user_id, month, genre), then uses ROW_NUMBER() with PARTITION BY (user_id, month) ORDER BY (play_count DESC, genre ASC) to rank genres per user per month, then filters to rank = 1. The candidate distinguishes between ROW_NUMBER (handles ties by picking one deterministically) versus RANK or DENSE_RANK (which would return multiple rows on a tie), and explains why ROW_NUMBER is correct given the alphabetical tiebreaker.

Reference implementation (sql)

-- plays(user_id BIGINT, track_id BIGINT, played_at TIMESTAMP)
-- tracks(track_id BIGINT, genre VARCHAR)

WITH monthly_genre_counts AS (
    SELECT
        p.user_id,
        DATE_TRUNC('month', p.played_at) AS month,
        t.genre,
        COUNT(*) AS play_count
    FROM plays p
    JOIN tracks t ON p.track_id = t.track_id
    GROUP BY 1, 2, 3
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, month
            ORDER BY play_count DESC, genre ASC
        ) AS rn
    FROM monthly_genre_counts
)
SELECT user_id, month, genre, play_count
FROM ranked
WHERE rn = 1;

Expect these follow-ups

• How would you change this query to return the top 3 genres per user per month instead of just the top 1?
• This query over 2 billion play rows is slow. What index would you add and why?

As asked

You have a PostgreSQL table with 2 billion rows of payment transactions: transaction_id, merchant_id, customer_id, amount_cents, currency, created_at, status. A merchant dashboard query that groups by date and status for a single merchant is taking 30 seconds. Walk me through how you diagnose and fix it.

Sample answer outline

A strong answer starts with EXPLAIN ANALYZE to identify whether the query is doing a sequential scan or an inefficient index scan. The fix is typically a composite index on (merchant_id, created_at) or a partial index if only certain statuses are queried. The candidate should discuss cardinality of the indexed columns, the cost of index maintenance on write-heavy tables, and whether a denormalized summary table or a materialized view updated incrementally would be a better long-term solution for reporting queries.

Reference implementation (sql)

-- Original slow query
SELECT DATE(created_at) AS day, status, SUM(amount_cents)
FROM transactions
WHERE merchant_id = $1
  AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY day, status
ORDER BY day;

-- Suggested index (create concurrently in production)
CREATE INDEX CONCURRENTLY idx_txn_merchant_created
  ON transactions(merchant_id, created_at);

Expect these follow-ups

• How do you add the index without locking the table for 30 minutes in production?
• What would change if this were a multi-tenant table and hundreds of merchants ran this query concurrently?

As asked

Given a table `tweet_events(tweet_id BIGINT, event_type VARCHAR, user_id BIGINT, created_at TIMESTAMP)` where event_type can be 'tweet', 'retweet', or 'like', write a SQL query that returns the top 10 most retweeted original tweets for each hour in the last 7 days, along with the retweet count.

Sample answer outline

A strong answer uses DATE_TRUNC('hour', created_at) to bucket by hour, filters on event_type = 'retweet', groups by (hour_bucket, tweet_id), applies a ROW_NUMBER() or RANK() window function partitioned by hour to pick the top 10 per hour, and adds a WHERE clause filtering to the last 7 days on created_at. The candidate should address index design (composite index on (event_type, created_at) or a partial index) and explain why a subquery or CTE is cleaner than a derived table for this pattern.

Reference implementation (sql)

WITH hourly_retweets AS (
  SELECT
    DATE_TRUNC('hour', created_at) AS hour_bucket,
    tweet_id,
    COUNT(*) AS retweet_count
  FROM tweet_events
  WHERE event_type = 'retweet'
    AND created_at >= NOW() - INTERVAL '7 days'
  GROUP BY 1, 2
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY hour_bucket ORDER BY retweet_count DESC) AS rn
  FROM hourly_retweets
)
SELECT hour_bucket, tweet_id, retweet_count
FROM ranked
WHERE rn <= 10
ORDER BY hour_bucket DESC, retweet_count DESC;

Expect these follow-ups

• This query runs on a table with 50 billion rows. What partitioning strategy would you add to the table and what index would make this query fast?
• The product team wants the same report but filtered by geographic region. How does that change the query and the index?

As asked

You are using Presto or Hive on top of Uber's data lake. You want to understand whether average driver wait time at pickup has been increasing over the past 90 days, broken down by city and time of day. Walk me through the SQL you would write and any data quality issues you would watch for.

Sample answer outline

A strong answer writes a query that joins trips to a city lookup, computes wait time as the difference between driver_arrived and rider_requested timestamps, buckets by city and hour of day, filters to the 90-day window, and aggregates with AVG and COUNT. The candidate should flag nulls in arrival timestamps for cancelled trips, timezone handling when bucketing by hour, and the difference between median and mean for a skewed distribution.

Expect these follow-ups

• Your query scans 90 days of trip data partitioned by date. How do you ensure Presto uses partition pruning and does not do a full table scan?
• The average wait time looks fine globally but one city's p99 wait time is 40 minutes. How would you investigate whether it is a data quality issue or a real operational problem?