BI engineer coding interview questions

As asked

Given a table employee(department_id INT, employee_name VARCHAR, salary DECIMAL), write a SQL query to return the second-highest salary in each department. Handle ties correctly: if two employees share the highest salary, neither qualifies as second-highest.

Sample answer outline

Use DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) to assign ranks, then filter WHERE rnk = 2. Distinguish RANK (skips numbers after ties), DENSE_RANK (no gaps), and ROW_NUMBER (assigns arbitrary tiebreaker). The question tests understanding that ROW_NUMBER would give wrong results on ties here.

Reference implementation (sql)

SELECT department_id, employee_name, salary
FROM (
  SELECT
    department_id,
    employee_name,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Expect these follow-ups

• What if you wanted the top 3 salaries per department, including ties at position 3?
• How does this query change if you need to return NULL when a department has fewer than 2 distinct salary levels?

As asked

Given a table monthly_sales(year INT, month INT, category VARCHAR, revenue DECIMAL), write a SQL query that returns, for each category and month, the current year's revenue, the prior year's revenue, and the year-over-year growth percentage. Handle months where prior year data does not exist.

Sample answer outline

Self-join on category and month, with current.year = prior.year + 1. Or use LAG(revenue, 12) OVER (PARTITION BY category ORDER BY year, month) if the data has monthly rows without gaps. Use NULLIF to avoid division by zero. Return NULL growth when prior year revenue is missing rather than 0. The candidate should handle both approaches and explain when LAG is preferable.

Reference implementation (sql)

SELECT
  year,
  month,
  category,
  revenue AS current_revenue,
  LAG(revenue, 12) OVER (
    PARTITION BY category
    ORDER BY year, month
  ) AS prior_year_revenue,
  ROUND(
    (revenue - LAG(revenue, 12) OVER (
      PARTITION BY category ORDER BY year, month
    ))
    / NULLIF(LAG(revenue, 12) OVER (
      PARTITION BY category ORDER BY year, month
    ), 0) * 100,
    2
  ) AS yoy_growth_pct
FROM monthly_sales
ORDER BY category, year, month;

Expect these follow-ups

• How does your LAG approach behave if the data has a missing month (e.g., no February for one category)?
• How would you express this same calculation as a Tableau table calculation vs. in SQL?

As asked

Write a SQL query that produces a daily running total of revenue for each product, including days where no sales occurred (filling in 0 revenue and carrying forward the running total). The table is: sales(sale_date DATE, product_id INT, revenue DECIMAL).

Sample answer outline

Strong answer generates a date spine (calendar table or recursive CTE / GENERATE_DATE_ARRAY in BigQuery), cross joins it with distinct products, left joins the sales table, fills NULLs with 0, then applies SUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING). Must handle both the gap-fill and the ROWS vs RANGE frame subtlety.

Reference implementation (sql)

-- starter skeleton
WITH date_spine AS (
  -- generate one row per day
),
products AS (
  SELECT DISTINCT product_id FROM sales
),
all_days_products AS (
  SELECT d.sale_date, p.product_id
  FROM date_spine d CROSS JOIN products p
)
SELECT
  a.sale_date,
  a.product_id,
  COALESCE(s.revenue, 0) AS daily_revenue,
  SUM(COALESCE(s.revenue, 0))
    OVER (PARTITION BY a.product_id
          ORDER BY a.sale_date
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_total
FROM all_days_products a
LEFT JOIN sales s
  ON a.sale_date = s.sale_date
  AND a.product_id = s.product_id;

Expect these follow-ups

• How would you generate the date spine without a calendar table in Snowflake?
• How does your answer change if the business wants a 7-day rolling sum instead of a cumulative total?

As asked

You have a table user_activity(user_id INT, activity_date DATE). Write a SQL query that shows, for each monthly cohort (first active month), what percentage of users were active in each subsequent month (month 0 through month 12).

Sample answer outline

Step 1: find each user's first active month (cohort). Step 2: for each user and each subsequent month of activity, compute the month offset with DATEDIFF or DATE_DIFF. Step 3: pivot or GROUP BY cohort_month and month_offset to count active users, divide by cohort size. The candidate should handle users who skip months and still appear in later months (they do not count in the intervening months).

Reference implementation (sql)

WITH first_month AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
  FROM user_activity
  GROUP BY user_id
),
monthly_activity AS (
  SELECT ua.user_id,
         fm.cohort_month,
         DATE_TRUNC('month', ua.activity_date) AS active_month,
         DATEDIFF('month', fm.cohort_month,
                  DATE_TRUNC('month', ua.activity_date)) AS month_offset
  FROM user_activity ua
  JOIN first_month fm ON ua.user_id = fm.user_id
)
SELECT
  cohort_month,
  month_offset,
  COUNT(DISTINCT user_id) AS active_users,
  COUNT(DISTINCT user_id) * 1.0 / FIRST_VALUE(COUNT(DISTINCT user_id))
    OVER (PARTITION BY cohort_month ORDER BY month_offset) AS retention_rate
FROM monthly_activity
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Expect these follow-ups

• How would you turn this into a Tableau chart that shows retention curves?
• How does your query change if 'active' means at least 3 activities in a month rather than any activity?

As asked

An event table receives duplicate rows from the source system. The table is events(event_id VARCHAR, user_id INT, event_type VARCHAR, event_timestamp TIMESTAMP, payload JSONB). Write a SQL query that returns exactly one row per event_id, keeping the row with the latest event_timestamp. If timestamps are equal, keep the row with the highest row-level CTID (or any stable tiebreaker).

Sample answer outline

Use ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_timestamp DESC, ctid DESC) AS rn and filter WHERE rn = 1. Alternatively, use a DISTINCT ON (event_id) ORDER BY event_id, event_timestamp DESC in Postgres. The candidate should know both patterns and their performance implications.

Reference implementation (sql)

-- ROW_NUMBER approach (portable)
SELECT event_id, user_id, event_type, event_timestamp, payload
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY event_timestamp DESC, ctid DESC
    ) AS rn
  FROM events
) deduped
WHERE rn = 1;

-- Postgres-specific alternative
SELECT DISTINCT ON (event_id)
  event_id, user_id, event_type, event_timestamp, payload
FROM events
ORDER BY event_id, event_timestamp DESC;

Expect these follow-ups

• How would you implement this deduplication in dbt as a reusable macro?
• What is the performance difference between ROW_NUMBER filtering and a DISTINCT ON in Postgres?

As asked

You have a clickstream table page_views(user_id INT, view_timestamp TIMESTAMP). Two events belong to the same session if they are within 30 minutes of each other for the same user. Write a SQL query to assign a session_id to each page view.

Sample answer outline

Use LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) to get the previous event time. If the gap is more than 30 minutes, mark as a new session start (1, else 0). Then use SUM() OVER (PARTITION BY user_id ORDER BY view_timestamp) on the start flag to assign incrementing session numbers. Combine user_id and session number for a unique session_id.

Reference implementation (sql)

WITH session_starts AS (
  SELECT
    user_id,
    view_timestamp,
    CASE
      WHEN DATEDIFF(
        'minute',
        LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp),
        view_timestamp
      ) > 30
      OR LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) IS NULL
      THEN 1 ELSE 0
    END AS is_new_session
  FROM page_views
)
SELECT
  user_id,
  view_timestamp,
  user_id || '-' || SUM(is_new_session)
    OVER (PARTITION BY user_id ORDER BY view_timestamp) AS session_id
FROM session_starts;

Expect these follow-ups

• How does your query handle a user who is inactive for exactly 30 minutes to the millisecond?
• How would you handle timezone-aware timestamps where the user crosses a DST boundary?

As asked

A metrics table has rows: metrics(date DATE, metric_name VARCHAR, value DECIMAL). You need to produce a wide table with columns: date, revenue, cost, profit. Write this without using a PIVOT or CASE WHEN ... END workaround that lists every metric name as a literal. Then write the CASE WHEN version and explain the tradeoff.

Sample answer outline

CASE WHEN version: GROUP BY date, with MAX(CASE WHEN metric_name = 'revenue' THEN value END) AS revenue. Dynamic pivot (without hardcoding names) requires dynamic SQL in most warehouses (Snowflake supports PIVOT; BigQuery requires a scripting block). The tradeoff is that CASE WHEN is readable and portable but breaks when metric names change, while dynamic SQL is flexible but harder to debug and not supported in all warehouse query interfaces.

Reference implementation (sql)

-- CASE WHEN approach
SELECT
  date,
  MAX(CASE WHEN metric_name = 'revenue' THEN value END) AS revenue,
  MAX(CASE WHEN metric_name = 'cost'    THEN value END) AS cost,
  MAX(CASE WHEN metric_name = 'profit'  THEN value END) AS profit
FROM metrics
GROUP BY date
ORDER BY date;

-- Snowflake native PIVOT
SELECT *
FROM metrics
PIVOT (
  MAX(value)
  FOR metric_name IN ('revenue', 'cost', 'profit')
) AS p (date, revenue, cost, profit);

Expect these follow-ups

• How would you implement a dynamic pivot in Snowflake using its native PIVOT syntax?
• How would you handle this in dbt where you cannot write dynamic SQL directly?

As asked

You have an events table events(user_id INT, event_name VARCHAR, event_time TIMESTAMP). The funnel is: 'view_product' then 'add_to_cart' then 'purchase', and each step must happen after the previous one. Write a SQL query that counts how many users completed each step in order.

Sample answer outline

Use conditional aggregation: for each user, find the min timestamp of each funnel step. A user completes step 2 only if their add_to_cart timestamp is after their view_product timestamp. Count users who have step 1, then users who have step 2 after step 1, then step 3 after step 2. Alternatively use nested subqueries or EXISTS clauses. The key distinction from unordered funnel is the timestamp ordering constraint.

Reference implementation (sql)

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS t1,
    MIN(CASE WHEN event_name = 'add_to_cart'  THEN event_time END) AS t2,
    MIN(CASE WHEN event_name = 'purchase'      THEN event_time END) AS t3
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(CASE WHEN t1 IS NOT NULL THEN 1 END)                         AS step1_users,
  COUNT(CASE WHEN t2 > t1       THEN 1 END)                         AS step2_users,
  COUNT(CASE WHEN t3 > t2 AND t2 > t1 THEN 1 END)                   AS step3_users
FROM user_steps;

Expect these follow-ups

• How would you limit the funnel window to 24 hours from the first step?
• How does this translate to a Tableau funnel chart using only Tableau calculations?

As asked

In BigQuery, there is no MEDIAN() aggregate function. Write a SQL query to compute the median, P90, and P99 of a column order_value from an orders table, and explain the difference between PERCENTILE_CONT and PERCENTILE_DISC.

Sample answer outline

BigQuery does not have a MEDIAN() aggregate. For exact percentiles use the analytic function syntax: PERCENTILE_CONT(order_value, 0.5) OVER () for an interpolated value (weighted between the two middle values) or PERCENTILE_DISC(order_value, 0.5) OVER () for the actual nearest value in the dataset. Both are window functions in BigQuery, not aggregate functions, so they require an OVER clause. For large tables, APPROX_QUANTILES(order_value, 100) gives approximate percentiles efficiently by computing a 100-bucket histogram in a single pass. The candidate should know APPROX functions trade a small accuracy margin (typically under 1%) for much better performance and should be the default choice on billion-row tables.

Reference implementation (sql)

-- Exact percentiles using PERCENTILE_CONT (standard SQL)
SELECT
  PERCENTILE_CONT(order_value, 0.50) OVER () AS median,
  PERCENTILE_CONT(order_value, 0.90) OVER () AS p90,
  PERCENTILE_CONT(order_value, 0.99) OVER () AS p99
FROM orders
LIMIT 1;

-- Approximate (more scalable for large tables)
SELECT
  approx_quantiles[OFFSET(50)] AS approx_median,
  approx_quantiles[OFFSET(90)] AS approx_p90,
  approx_quantiles[OFFSET(99)] AS approx_p99
FROM (
  SELECT APPROX_QUANTILES(order_value, 100) AS approx_quantiles
  FROM orders
);

Expect these follow-ups

• When would you use APPROX_QUANTILES over exact PERCENTILE_CONT on a 1-billion-row table?
• How would you compute percentiles grouped by product category?

As asked

In dbt, you want every dimension model to produce a consistent surrogate key by hashing a combination of source columns. Write a dbt macro called generate_surrogate_key that accepts a list of column names and returns a SQL expression using MD5 or SHA256, handling NULLs correctly.

Sample answer outline

The macro should COALESCE each column to a placeholder string (e.g., empty string or a sentinel like 'NULL') before concatenation, then hash the concatenated string. Without COALESCE, any NULL makes the whole hash NULL. The candidate should know dbt ships a generate_surrogate_key macro in dbt-utils that uses MD5, and they should know the tradeoffs of MD5 (collision risk in theory, sufficient in practice for BI keys) vs SHA256.

Reference implementation (sql)

{% macro generate_surrogate_key(column_names) %}
  MD5(
    CONCAT(
      {% for col in column_names %}
        COALESCE(CAST({{ col }} AS VARCHAR), '')
        {%- if not loop.last %} || '|' || {% endif %}
      {% endfor %}
    )
  )
{% endmacro %}

-- Usage in a model:
SELECT
  {{ generate_surrogate_key(['customer_id', 'source_system']) }} AS customer_key,
  customer_id,
  source_system,
  customer_name
FROM {{ source('crm', 'customers') }}

Expect these follow-ups

• Why does dbt-utils coalesce to an empty string rather than 0 before hashing?
• How would you handle a case where two rows have different column sets that hash to the same surrogate key?

As asked

Your employees table has columns employee_id, name, and manager_id (self-referencing). Write a SQL recursive CTE that returns every employee, their level in the hierarchy (CEO = 1), and the full management chain as a string path.

Sample answer outline

Anchor member: SELECT where manager_id IS NULL (top-level executives). Recursive member: JOIN employees on manager_id = previous level's employee_id, incrementing level and concatenating the path. Guard against infinite loops (most databases handle this with a MAX RECURSION limit). In Snowflake, use CONNECT BY as an alternative to recursive CTEs.

Reference implementation (sql)

WITH RECURSIVE org_hierarchy AS (
  -- Anchor: top-level employees (no manager)
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS level,
    CAST(name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees with a manager
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    h.level + 1,
    h.path || ' > ' || e.name
  FROM employees e
  JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, level, path
FROM org_hierarchy
ORDER BY path;

Expect these follow-ups

• How does a recursive CTE perform on a 100,000-node hierarchy and what are the alternatives?
• How would you aggregate total headcount under each manager using this CTE?