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.

HardTechnical

72 practiced

Event streams sometimes contain duplicate event_ids and late-arriving corrections. Design an SQL-based ingestion strategy to compute daily deduplicated revenue that: (1) deduplicates by event_id keeping the latest version, (2) handles late-arriving corrections without double-counting, and (3) supports an idempotent daily aggregate upsert. Provide the SQL pattern (CTE) you would use to produce the deduped daily revenue for a given day.

Sample Answer

Approach (brief): use windowing to pick the latest version per event_id (by version_ts or ingestion_ts), restrict to events that belong to the target day (event_date), compute daily sum from that deduped set, and MERGE into a daily_aggregates table to make the operation idempotent (overwrite previous value if a later correction arrives).

SQL pattern (replace table/column names to match your schema):

sql

WITH params AS (
  SELECT DATE '2025-01-15' AS target_day  -- change per run
),

-- 1) consider all raw events whose event_date = target_day OR whose correction version points to that day
candidates AS (
  SELECT r.*
  FROM events_raw r
  JOIN params p ON r.event_date = p.target_day
),

-- 2) pick latest version per event_id (latest by version_ts, tie-breaker ingestion_ts)
latest_per_event AS (
  SELECT *
  FROM (
    SELECT c.*,
           ROW_NUMBER() OVER (PARTITION BY c.event_id ORDER BY c.version_ts DESC, c.ingestion_ts DESC) AS rn
    FROM candidates c
  ) t
  WHERE rn = 1
),

-- 3) optionally exclude tombstones or cancelled events (e.g., is_deleted flag)
deduped AS (
  SELECT event_id, event_date, revenue
  FROM latest_per_event
  WHERE COALESCE(is_deleted, FALSE) = FALSE
),

-- 4) aggregate daily revenue
daily_revenue_calc AS (
  SELECT event_date AS day,
         SUM(revenue) AS revenue_sum,
         COUNT(*) AS event_count
  FROM deduped
  GROUP BY event_date
)

-- 5) idempotent upsert into aggregates table
MERGE INTO daily_revenue t
USING (SELECT * FROM daily_revenue_calc) s
  ON t.day = s.day
WHEN MATCHED THEN
  UPDATE SET t.revenue = s.revenue_sum, t.event_count = s.event_count, t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (day, revenue, event_count, updated_at)
  VALUES (s.day, s.revenue_sum, s.event_count, CURRENT_TIMESTAMP);

Why this works:- Windowing guarantees latest-version per event_id (dedupes).- Restricting candidates by event_date ensures corrections for that day are included and earlier duplicates are ignored.- Tombstone handling prevents double-counting of deleted events.- MERGE makes the pipeline idempotent: repeated runs for the same day replace previous aggregate with the canonical value.

Edge cases / notes:- If corrections arrive with different event_date, include logic to process both original and corrected dates (join where corrected_event_date = target_day).- Use transactional/atomic execution where supported to avoid race conditions.- For very large volumes, pre-partition or use incremental processing keyed by event_id to avoid re-scanning entire raw table.

EasyTechnical

61 practiced

When should you use EXISTS, NOT EXISTS, IN, NOT IN, or JOIN for membership/anti-membership checks in analytics SQL? Discuss correctness implications with NULLs and relative performance characteristics on large tables. Give a short example where NOT EXISTS is preferable to LEFT JOIN / IS NULL.

Sample Answer

Use-case guidance- EXISTS / NOT EXISTS: Best for membership/anti-membership when you only need to test existence of related rows. Correct with NULLs (they don't compare values) and expressive for correlated subqueries.- IN / NOT IN: Use IN for small literal lists or when checking a non-nullable column. NOT IN is dangerous if the subquery can return NULL — it yields no rows if any NULL present (because NULL breaks the equality logic).- JOIN (INNER JOIN): Use when you need to bring columns from the other table or when you want duplicate-multiplying semantics (one row per match).- LEFT JOIN ... IS NULL (anti-join): Common pattern for anti-membership but can be subtly wrong vs NOT EXISTS in presence of duplicates or complex joins; usually equivalent logically but NOT EXISTS is clearer and often safer.

NULL correctness- EXISTS/NOT EXISTS: unaffected by NULLs in the joined columns; they test presence of rows.- IN: when subquery returns any NULL, expression a NOT IN (subquery) is UNKNOWN for rows not matching any non-null value, so they are excluded — surprising behavior.- JOINs: NULLs in join keys simply don't match, as expected.

Performance- On large tables, performance depends on indexes and planner. EXISTS/NOT EXISTS often compiles to semi/anti-joins and can be very efficient with proper indexes on the correlated column. IN with a large subquery can be slower unless optimized to a hash semi-join. JOINs materialize matches and can be heavier if you only need existence. Test with EXPLAIN on your engine.

Short example where NOT EXISTS is preferable- Find customers with no orders. If orders.customer_id may contain NULLs, NOT IN can fail; LEFT JOIN/IS NULL may produce extra work. NOT EXISTS is clear and correct:

sql

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Key takeaway: prefer EXISTS/NOT EXISTS for membership checks (especially anti-membership) for correctness with NULLs and often better planner-supported anti-semi-joins; use IN for small, non-null lists or when semantic simplicity is certain; use JOIN when you need joined columns or duplicate-preserving behavior.

HardTechnical

73 practiced

Using SQL, compute a cohort-based survival analysis for churn. Given users(user_id, signup_date) and events(user_id, event_date), define churn as no activity for 30+ days. For each weekly cohort, compute survival probabilities at 0, 30, 60, 90 days (proportion of users not churned by those times). Outline SQL steps and provide the main query structure (CTEs).

Sample Answer

Approach (brief): assign users to weekly signup cohorts, compute each user's churn_time (date when they first became inactive for >=30 days after signup — i.e., the start date of the first 30+ day gap or, if no such gap observed, set churn_time = NULL or large sentinel). For each threshold (0,30,60,90 days), a user “survives” if churn_time is after signup + threshold (or churn_time is NULL/after observation window). Aggregate per cohort to get survival probability = survivors / cohort_size.

Key steps / CTEs:1) cohorts: week bucket for signup_date2) ordered_events: for each user, event dates sorted (include signup_date as an event)3) gaps: compute next_event_date and gap_days between events4) churn_point: find first event_date where next_event_date - event_date >= 30 (churn_date = event_date + 30). If no gap >=30, optional set churn_date = NULL or last_event + 30 if you treat end-of-observation as churn5) survival_flags: for each user and each threshold compute survived = churn_date IS NULL OR churn_date > signup_date + threshold6) aggregate per cohort: count survivors and cohort size, compute proportions

Main SQL structure (skeleton):

sql

WITH cohorts AS (
  SELECT
    u.user_id,
    u.signup_date,
    DATE_TRUNC('week', u.signup_date) AS cohort_week
  FROM users u
),

ordered_events AS (
  -- include signup as an event to anchor timeline
  SELECT
    e.user_id,
    e.event_date
  FROM events e
  UNION ALL
  SELECT user_id, signup_date AS event_date
  FROM users
),

events_with_lag AS (
  SELECT
    oe.user_id,
    oe.event_date,
    LEAD(oe.event_date) OVER (PARTITION BY oe.user_id ORDER BY oe.event_date) AS next_event_date
  FROM ordered_events oe
),

gaps AS (
  SELECT
    user_id,
    event_date,
    next_event_date,
    CASE
      WHEN next_event_date IS NULL THEN NULL
      ELSE DATE_PART('day', next_event_date - event_date)
    END AS gap_days
  FROM events_with_lag
),

churn_point AS (
  -- first event_date where gap >= 30; churn_date = event_date + 30
  SELECT
    g.user_id,
    MIN(g.event_date + INTERVAL '30 day') AS churn_date
  FROM gaps g
  WHERE g.gap_days >= 30
  GROUP BY g.user_id
),

user_churn AS (
  -- left join keeps users with no observed churn_date (NULL)
  SELECT
    c.user_id,
    c.cohort_week,
    c.signup_date,
    cp.churn_date
  FROM cohorts c
  LEFT JOIN churn_point cp ON c.user_id = cp.user_id
),

survival_flags AS (
  SELECT
    uc.*,
    CASE WHEN uc.churn_date IS NULL OR uc.churn_date > uc.signup_date + INTERVAL '0 day'  THEN 1 ELSE 0 END AS survive_0,
    CASE WHEN uc.churn_date IS NULL OR uc.churn_date > uc.signup_date + INTERVAL '30 day' THEN 1 ELSE 0 END AS survive_30,
    CASE WHEN uc.churn_date IS NULL OR uc.churn_date > uc.signup_date + INTERVAL '60 day' THEN 1 ELSE 0 END AS survive_60,
    CASE WHEN uc.churn_date IS NULL OR uc.churn_date > uc.signup_date + INTERVAL '90 day' THEN 1 ELSE 0 END AS survive_90
  FROM user_churn uc
),

cohort_survival AS (
  SELECT
    cohort_week,
    COUNT(*) AS cohort_size,
    SUM(survive_0)::float / COUNT(*) AS p0,
    SUM(survive_30)::float / COUNT(*) AS p30,
    SUM(survive_60)::float / COUNT(*) AS p60,
    SUM(survive_90)::float / COUNT(*) AS p90
  FROM survival_flags
  GROUP BY cohort_week
  ORDER BY cohort_week
)

SELECT * FROM cohort_survival;

Notes, assumptions and edge cases:- We treat churn as the moment a 30-day inactivity window starts: churn_date = event_date + 30 when next_event - event_date >= 30.- Users with no observed 30+ gap are treated as still active (churn_date = NULL). If you have a limited observation window, you may want to censor users whose last_event is within 30 days of data end — treat them as unknown rather than survived.- If events are sparse / duplicate timestamps, ensure deduplication of event dates.- For very large tables, index on user_id,event_date and push heavy per-user ops into analytic DBs (BigQuery/Redshift) or process in Spark for performance.- You can extend to Kaplan-Meier style life-table by handling censoring explicitly if needed.

HardSystem Design

63 practiced

You have an events table expected to grow to ~10B rows. Propose a partitioning, indexing, and data distribution strategy for a cloud data warehouse (compare BigQuery and Redshift / Snowflake features). Include DDL examples for partitions/clustering, and explain how your design supports both heavy analytical scans (cohort queries) and frequent point lookups by user_id.

Sample Answer

Requirements & constraints:- 10B rows, heavy cohort/time-range scans (date windows), frequent point lookups by user_id, cloud-managed, cost-sensitive analytics latency.

High-level strategy (common principles)- Partition by event_date (day) for big time-range pruning.- Secondary locality: cluster/primary key on user_id to accelerate point lookups and reduce IO for per-user aggregation.- Maintain compact dimension tables, pre-aggregate (materialized views) for common cohorts.- Use appropriate distribution to avoid skew and co-locate user rows where possible.

BigQuery (partition + clustering)- Partitioning: ingestion/event_date (DATE) daily.- Clustering: user_id, event_type to prune scans and speed point queries.DDL example:

sql

CREATE TABLE analytics.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type AS
SELECT * FROM `project.raw.events`;

Why: Partition elimination reduces scanned bytes for cohort/time queries; clustering stores rows with same user_id nearby enabling fast point lookups and small-range scans. Use columnar storage (capable of skipping) and query-level filters. Consider partition expiration & ingestion-time partitioning for streaming. Use materialized views for repeated aggregations (daily active users, retention tables).

Redshift (or Snowflake) considerations- Redshift: set DISTKEY=user_id and SORTKEY(event_date, user_id) to co-locate user rows and speed range scans.DDL example (Redshift):

sql

CREATE TABLE analytics.events (
  event_id        bigint,
  user_id         bigint,
  event_timestamp timestamptz,
  event_date      date,
  event_type      varchar(50),
  payload         varchar(max)
)
DISTKEY(user_id)
SORTKEY(event_date, user_id);

Why: DISTKEY avoids network shuffle for per-user joins/aggregations; SORTKEY by date then user allows efficient sequential scans for cohorts and fast binary search for user-specific ranges. Also use compression encodings and VACUUM/ANALYZE maintenance. For very large tables consider interleaved sort keys if point lookups dominate.

- Snowflake:Snowflake auto micro-partitions by ingest order; specify clustering key on (event_date, user_id) for re-clustering if queries benefit:

sql

CREATE TABLE analytics.events (
  event_id bigint,
  user_id bigint,
  event_timestamp timestamp,
  event_date date,
  event_type string,
  payload variant
);
ALTER TABLE analytics.events CLUSTER BY (event_date, user_id);

Why: Snowflake uses clustering metadata to prune micro-partitions for date/user scans; automatic reclustering can be enabled with cost considerations.

Data distribution & skew mitigation- If a few users are extremely heavy, shard user_id hash modulo N (logical buckets) to avoid hot partitions: add user_bucket = farm_fingerprint(user_id) % 256 and include in clustering/distribution.- For Redshift/Snowflake, consider companion lookup table mapping hot users to separate storage or TTL strategies.

Supporting cohort scans vs point lookups- Cohort scans: date partitions allow scanning only relevant days; clustering/sort keys minimize row groups scanned and improve locality for GROUP BY user/day.- Point lookups: clustering/sort + DISTKEY (Redshift) or clustering + narrow predicate (BigQuery) ensures only a few blocks/micro-partitions read; create an index-like materialized table of latest event per user if lookups frequently need only last event.

Operational practices- Monitor partition growth and prune/expire old partitions.- Maintain statistics and run ANALYZE (Redshift) or ensure BigQuery table stats updated.- Use cost-aware sampling for development queries (LIMIT, TABLESAMPLE).- Build scheduled rollup tables for common windows (7/28/90-day cohorts) to avoid repeatedly scanning 10B rows.

Trade-offs- BigQuery: simpler serverless scaling, cheaper for infrequent interactive queries but clustering costs increase as table grows; clustering is best-effort.- Redshift: more control (DIST/SORT) giving better point query latency at cost of management (VACUUM/SORT).- Snowflake: automatic micro-partitioning + optional clustering provides good balance; reclustering costs may be incurred.

This design balances massive time-window scans and frequent per-user lookups by combining time partitioning with user-locality clustering/distribution, plus pre-aggregates for recurring cohort workloads.

MediumTechnical

65 practiced

Explain when you would choose GROUP BY aggregations vs. window functions to compute metrics. Provide an analytics example where both approaches can be applied (e.g., compute monthly revenue and each user's share of that month) and show which is more appropriate and why.

Sample Answer

Use GROUP BY when you need a reduced, aggregated result set (one row per group). Use window functions when you need group-level metrics attached to each original row (or to compute rankings, running totals, percentiles) without collapsing rows. Window functions provide row context; GROUP BY is cheaper when you only need aggregates.

Example: compute monthly revenue and each user's share of that month's revenue.

1) GROUP BY + JOIN (good if you only need one row per user-month and want to persist aggregated table)

sql

-- monthly revenue per user
WITH user_month AS (
  SELECT
    user_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS user_month_revenue
  FROM orders
  GROUP BY user_id, DATE_TRUNC('month', order_date')
),
-- total revenue per month
month_totals AS (
  SELECT month, SUM(user_month_revenue) AS month_revenue
  FROM user_month
  GROUP BY month
)
SELECT
  um.user_id,
  um.month,
  um.user_month_revenue,
  mt.month_revenue,
  um.user_month_revenue::numeric / mt.month_revenue AS share
FROM user_month um
JOIN month_totals mt USING (month);

2) Window function (better if you want the share on each individual order row or avoid an extra GROUP BY/ JOIN)

sql

SELECT
  user_id,
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS user_month_revenue,
  SUM(SUM(amount)) OVER (PARTITION BY DATE_TRUNC('month', order_date)) AS month_revenue,
  SUM(amount) / SUM(SUM(amount)) OVER (PARTITION BY DATE_TRUNC('month', order_date)) AS share
FROM orders
GROUP BY user_id, DATE_TRUNC('month', order_date);

Why choose which:- Use GROUP BY when you only need aggregates and want fewer rows (storage, easier downstream joins).- Use window functions when you need aggregate context per row (e.g., percent of month for each order, running totals, ranking) or to avoid extra joins.Performance/trade-offs:- GROUP BY can be more efficient when working with pre-aggregated datasets; window functions may compute more intermediate work and keep more rows.- For large datasets, pre-aggregate (GROUP BY) then join or materialize if reused often.Edge cases:- Handle months with zero total (avoid division by zero).- Be explicit about time zones in DATE_TRUNC.

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.