SQL for Business Scenarios Questions

Ability to read and decompose business questions and translate them into correct, efficient Structured Query Language queries that answer those questions. This includes identifying the required data sources and joins, choosing between inner joins, outer joins, anti joins and existence checks, writing subqueries and common table expressions for clarity, and applying filtering with where clauses, aggregation with group by and having, and window functions for ranking, running totals, and time series calculations. Candidates should demonstrate how to implement common business analyses such as conversion funnels, retention and cohort analysis, churn and lifetime value calculations, and operational metrics by mapping metric definitions to SQL expressions and handling edge cases like null values, duplicates, and late arriving data. The description also covers writing medium complexity queries that combine multiple tables, calculating derived metrics, validating results with sample data, and considering query performance through basic optimization techniques, indexing awareness, and selective projection.

MediumTechnical

54 practiced

Explain differences between GROUP BY and window functions and give a scenario where group-by cannot produce the desired row-level output but a window function can. Provide a SQL example showing why a window function is necessary and discuss performance considerations.

Sample Answer

GROUP BY collapses rows to produce one result row per group using aggregate functions (SUM, COUNT, MAX). Window functions compute aggregates over a defined frame but return the original row-level rows augmented with the aggregate value (e.g., SUM() OVER(...), ROW_NUMBER()).

Why window needed: when you need row-level detail plus group-level metrics. GROUP BY loses row granularity; you cannot, for example, mark each order with the rank of its revenue within its customer while preserving one row per order without joining back.

Example: find each order with its rank by amount within its customer and the customer total.

sql

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS order_rank
FROM orders;

Attempt with GROUP BY fails because GROUP BY customer_id, order_id would aggregate away per-order rows or you'd need subqueries/joins. Window functions compute per-row rank and totals in one pass.

Performance considerations:- Window functions can be expensive (sorting/partitioning) on large tables; they may require memory and spill to disk.- GROUP BY with pre-aggregated tables can be faster for pure aggregated metrics.- Use indexes on partition/order columns, filter early (WHERE), and consider materialized aggregates or incremental aggregation for dashboard-heavy metrics.- Test query plans: sometimes rewriting (e.g., lateral joins or pre-aggregations) improves performance.

HardTechnical

70 practiced

Advanced funnel: Given events(user_id, event_name, occurred_at TIMESTAMP) where users may have duplicate events and out-of-order timestamps, write an efficient SQL to compute conversion rate from event A → B → C within N days between steps. Show how you deduplicate events per user (keeping first occurrence of an event type) and then compute sequential time windows to check B after A within N days and C after B within N days.

Sample Answer

Approach:1. For each user and event_name, keep the first (earliest) occurred_at per user/event — dedupe.2. Pivot or self-join the deduped table to find A → B → C sequences where B occurs after A and within N days, and C occurs after B and within N days.3. Compute counts and conversion rates.

SQL (Postgres-style; adjust date arithmetic for other dialects):

sql

WITH dedup AS (
  -- keep first occurrence per user/event_name
  SELECT user_id, event_name, MIN(occurred_at) AS first_at
  FROM events
  GROUP BY user_id, event_name
),
ab AS (
  -- join A then B where B is after A and within N days
  SELECT dA.user_id,
         dA.first_at AS a_at,
         dB.first_at AS b_at
  FROM dedup dA
  JOIN dedup dB
    ON dA.user_id = dB.user_id
   AND dA.event_name = 'A'
   AND dB.event_name = 'B'
   AND dB.first_at > dA.first_at
   AND dB.first_at <= dA.first_at + INTERVAL 'N days'
),
abc AS (
  -- join B then C where C is after B and within N days
  SELECT ab.user_id, ab.a_at, ab.b_at, dC.first_at AS c_at
  FROM ab
  JOIN dedup dC
    ON ab.user_id = dC.user_id
   AND dC.event_name = 'C'
   AND dC.first_at > ab.b_at
   AND dC.first_at <= ab.b_at + INTERVAL 'N days'
)
-- final metrics
SELECT
  (SELECT COUNT(DISTINCT user_id) FROM dedup WHERE event_name = 'A')     AS users_with_A,
  (SELECT COUNT(DISTINCT user_id) FROM ab)                               AS converted_A_to_B,
  (SELECT COUNT(DISTINCT user_id) FROM abc)                              AS converted_A_to_B_to_C,
  ROUND(100.0 * (SELECT COUNT(DISTINCT user_id) FROM ab) / NULLIF((SELECT COUNT(DISTINCT user_id) FROM dedup WHERE event_name='A'),0), 2) AS pct_A_to_B,
  ROUND(100.0 * (SELECT COUNT(DISTINCT user_id) FROM abc) / NULLIF((SELECT COUNT(DISTINCT user_id) FROM ab),0), 2) AS pct_B_to_C,
  ROUND(100.0 * (SELECT COUNT(DISTINCT user_id) FROM abc) / NULLIF((SELECT COUNT(DISTINCT user_id) FROM dedup WHERE event_name='A'),0), 2) AS pct_A_to_C
;

Key points:- Deduping via MIN keeps first occurrence despite duplicates/out-of-order data.- Using joins enforces strict ordering and N-day window between consecutive steps.- Use DISTINCT user_id to avoid double-counting users who might have multiple valid sequences (if you want only the earliest full funnel per user, dedupe further by taking MIN(a_at) per user).

Complexity:- Runtime dominated by grouping and joins: O(E log E) for aggregation then O(U) joins where E = events rows, U = users × event types. Ensure indexes on (user_id, event_name, occurred_at) for performance.

Edge cases:- Users missing intermediate events are excluded by joins.- If N is large or NULL, adjust logic.- Timezones: ensure occurred_at normalized.- If you prefer allowing B at same timestamp as A, change > to >=.

Alternatives:- Use window functions to rank events per user and then filter rank=1 for each event.- For very large datasets, compute steps in staged materialized tables or use analytic DB features (BigQuery ARRAY_AGG with LIMIT 1, or approximate funnels with HyperLogLog for counts).

MediumTechnical

63 practiced

Write SQL to compute a 7-day rolling active users metric (rolling unique users in the last 7 days) given daily_activity(date::DATE, user_id). Provide a correct approach in SQL; discuss the accuracy/performance trade-offs and how to implement this efficiently at scale (hint: pre-aggregation or approximate counting).

Sample Answer

Approach (brief): for each date compute the number of distinct users seen in that date and the previous 6 days. Exact SQL is straightforward but can be expensive; at scale prefer pre-aggregation and/or approximate distinct (HLL).

Exact (simple, correct but can be slow on large tables) — one row per date using a correlated subquery:

sql

SELECT d.day,
       (SELECT COUNT(DISTINCT user_id)
        FROM daily_activity a
        WHERE a.date BETWEEN d.day - INTERVAL '6 days' AND d.day
       ) AS rolling_7d_active_users
FROM (
  SELECT DISTINCT date::date AS day
  FROM daily_activity
) d
ORDER BY d.day;

More efficient pre-aggregation + union of daily unique sets (example using PostgreSQL + intarray/bitmap extension idea):1) Pre-aggregate daily uniques into a deduplicated table:

sql

CREATE MATERIALIZED VIEW daily_uniques AS
SELECT date::date AS day, user_id
FROM daily_activity
GROUP BY 1, user_id;

2) Then compute rolling by joining 7 days of pre-aggregated rows:

sql

SELECT d.day,
       COUNT(DISTINCT du.user_id) AS rolling_7d_active_users
FROM (SELECT DISTINCT day FROM daily_uniques) d
JOIN daily_uniques du
  ON du.day BETWEEN d.day - INTERVAL '6 days' AND d.day
GROUP BY d.day
ORDER BY d.day;

Approximate / large scale (recommended for dashboards with many dates): use HyperLogLog or native approximate functions (BigQuery, Redshift, ClickHouse).BigQuery example (fast, memory-friendly):

sql

SELECT day,
       APPROX_COUNT_DISTINCT(user_id)                 /* single-day approx */
         OVER (PARTITION BY 1 ORDER BY day
               RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS rolling_7d_hll
FROM (
  SELECT DATE(date) AS day, user_id FROM daily_activity
)
GROUP BY day, user_id
ORDER BY day;

Alternatively build an HLL sketch per day and merge sketches for each 7-day window:

sql

-- pseudo BigQuery-style with HLL functions
WITH daily_hll AS (
  SELECT day, HLL_AGG(user_id) AS hll_sketch
  FROM (SELECT DATE(date) day, user_id FROM daily_activity)
  GROUP BY day
)
SELECT d.day,
       HLL_COUNT(HLL_UNION_AGG(hll_sketch)) AS rolling_7d_approx
FROM daily_hll d
JOIN daily_hll h ON h.day BETWEEN d.day - 6 AND d.day
GROUP BY d.day
ORDER BY d.day;

Trade-offs and performance guidance:- Exact COUNT(DISTINCT) over sliding windows is correct but expensive; it requires scanning or joining many rows and heavy distinct aggregation.- Pre-aggregate daily uniques (materialized views, partitioned tables) to reduce input size by deduplicating per-day.- Use sketches (HLL) / APPROX_COUNT_DISTINCT for speed and low memory; error ~1-2% acceptable for dashboards.- Use partitioning on date, clustering on user_id, and proper indexes to accelerate joins.- Maintain incremental pipelines: update daily sketches or daily_uniques each day and compute rolling metrics incrementally rather than scanning full history.- If business requires exact counts for short windows, consider bitmaps (Roaring/bitmap indexes) to union efficiently per window.

Edge cases:- Days with no activity should appear as zero (ensure calendar table).- Timezones: normalize timestamps to a date.- Late-arriving events: decide retention and backfill policy; recompute affected windows when updating historical partitions.

EasyTechnical

57 practiced

Given sales(product_id, category, amount, sale_date), write a SQL query to pivot total revenue by category into monthly columns for a single calendar year. Produce output: category, jan_revenue, feb_revenue, ..., dec_revenue. Use standard SQL (conditional aggregation) and show how to handle categories with no sales in a month.

Sample Answer

To pivot total revenue by category into monthly columns for a single calendar year using standard SQL, use conditional aggregation with CASE expressions and COALESCE to ensure months with no sales show 0.

sql

-- Replace :year with the desired year (e.g., 2024)
SELECT
  category,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS jan_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS feb_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS mar_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 4  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS apr_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 5  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS may_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 6  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS jun_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 7  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS jul_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 8  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS aug_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 9  AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS sep_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 10 AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS oct_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 11 AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS nov_revenue,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 12 AND EXTRACT(YEAR FROM sale_date) = :year THEN amount END), 0) AS dec_revenue
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = :year
GROUP BY category
ORDER BY category;

Notes:- COALESCE ensures months with no rows return 0.- If your SQL dialect supports DATE_TRUNC or TO_CHAR, you can adapt the month extraction accordingly.- For performance, keep the WHERE year filter so the engine can prune rows before aggregation.

MediumTechnical

57 practiced

Anomaly detection: Given daily_revenue(day DATE, revenue NUMERIC), write SQL to compute a rolling 30-day mean and stddev and flag days where revenue > mean + 3 * stddev. Make sure to handle the first 29 days (insufficient history) and explain how you would reduce false positives caused by seasonality.

Sample Answer

Approach: use a 30-day rolling window (previous 30 days, excluding current day) to compute mean and stddev; only flag when we have full 30-day history. To reduce seasonality-driven false positives, compare against seasonally-matched baseline (e.g., same weekday) or use robust stats (median + MAD) or EWMA / STL decomposition.

sql

-- Postgres syntax: rolling 30 previous days (exclude current day)
SELECT
  day,
  revenue,
  rolling_count,
  rolling_mean,
  rolling_stddev,
  CASE
    WHEN rolling_count < 30 THEN NULL                           -- insufficient history
    WHEN revenue > rolling_mean + 3 * rolling_stddev THEN TRUE  -- anomaly
    ELSE FALSE
  END AS is_anomaly
FROM (
  SELECT
    day,
    revenue,
    COUNT(*)  OVER (ORDER BY day
                   ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)     AS rolling_count,
    AVG(revenue) OVER (ORDER BY day
                       ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)   AS rolling_mean,
    STDDEV_SAMP(revenue) OVER (ORDER BY day
                               ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS rolling_stddev
  FROM daily_revenue
  ORDER BY day
) t;

Key points:- We use ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING so the window contains the 30 days immediately before the current day.- We check rolling_count >= 30 to avoid spurious flags for the first 29 days.- STDDEV_SAMP gives sample stddev; if revenue distribution is heavy-tailed, consider MEDIAN + k * MAD for robustness.

Reducing seasonality false positives (practical options):- Weekly seasonality: compute baseline using same weekday (partition by extract(dow from day)) and compare revenue to weekday-specific mean/stddev.- Monthly/seasonal trends: decompose time series offline (STL/Prophet) and run anomaly detection on residuals.- Use EWMA or rolling median + MAD to be robust to outliers.- Add a minimum absolute delta threshold (e.g., also require revenue > mean + 3*stddev AND revenue > mean * 1.2) to avoid flagging tiny fluctuations at low volume.

Unlock Full Question Bank

Get access to hundreds of SQL for Business Scenarios interview questions and detailed answers.

Join thousands of developers preparing for their dream job.