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.

EasyTechnical

57 practiced

Given two tables: users(user_id, created_at) and orders(order_id, user_id, amount, created_at), write an ANSI SQL query to find users who have never placed an order. Explain why you chose NOT EXISTS versus LEFT JOIN ... IS NULL, discuss NULL-sensitivity of NOT IN, and show a small sample dataset to validate the result.

Sample Answer

Approach: use an anti-join. I prefer NOT EXISTS because it's clear, handles duplicates predictably, and is safe with NULLs in the joined key.

Query (ANSI SQL):

sql

SELECT u.user_id, u.created_at
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
);

Why NOT EXISTS vs LEFT JOIN ... IS NULL- NOT EXISTS expresses intent: “no matching order for this user.” Most engines optimize it into an anti-join.- LEFT JOIN ... IS NULL is equivalent in result when join keys are NOT NULL and there are no duplicate semantics issues:

sql

SELECT u.user_id, u.created_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.user_id IS NULL;

- NOT EXISTS is typically safer and more readable when the subquery uses correlated conditions or when you want to avoid row-multiplication from multiple matching orders.

NULL-sensitivity of NOT IN- NOT IN can produce surprising results if the right-hand set contains NULLs. Example: user_id NOT IN (SELECT user_id FROM orders) returns no rows if any order.user_id IS NULL, because comparison yields UNKNOWN. Therefore avoid NOT IN unless you can guarantee no NULLs.

Sample dataset to validate:

sql

-- users
INSERT INTO users(user_id, created_at) VALUES
(1, '2024-01-01'), (2, '2024-02-01'), (3, '2024-03-01');

-- orders
INSERT INTO orders(order_id, user_id, amount, created_at) VALUES
(10, 1, 100, '2024-02-01'),
(11, NULL, 50, '2024-02-15'); -- demonstrates NULL in orders.user_id

Running the NOT EXISTS query returns user_id = 2 and 3 (users without orders). Using NOT IN would return no rows because orders.user_id contains NULL.

HardTechnical

89 practiced

Write an optimized SQL strategy (with example queries) to calculate monthly churn rate for a subscription product over billions of rows. Define churn (e.g., users active in month M but no activity in M+1 within 30 days), and explain how you minimize full-table scans using partition pruning, windowed deduplication, and materialized intermediate tables. Discuss explain-plan checks you would perform.

Sample Answer

Approach: define churn as users with activity in month M and no activity in the next 30 days after month M end (i.e., no event in M+1 within 30d). For billions of rows avoid full-table scans by (1) partitioning by event_date (or by month), (2) pre-aggregating user-month last_activity via windowed deduplication, (3) materializing user-month snapshots and incremental refreshes, and (4) using date-range filters so queries hit only relevant partitions.

Step 1 — maintain a materialized monthly user activity table (refreshed daily/incrementally):

sql

CREATE TABLE user_month_activity
PARTITION BY RANGE (activity_month) AS
SELECT
  user_id,
  DATE_TRUNC('month', activity_date) AS activity_month,
  MAX(activity_date) AS last_activity_in_month
FROM raw_events
WHERE activity_date >= DATE '2020-01-01' -- prune history
GROUP BY user_id, DATE_TRUNC('month', activity_date);

Use INSERT ... SELECT for new partitions only (incremental).

Step 2 — dedupe with window function when deriving last_activity per day/partition (if raw_events has many events per user/day):

sql

WITH ranked AS (
  SELECT user_id, activity_date,
         ROW_NUMBER() OVER (PARTITION BY user_id, DATE_TRUNC('day', activity_date) ORDER BY activity_date DESC) rn
  FROM raw_events
  WHERE activity_date BETWEEN @start AND @end
)
SELECT user_id, DATE_TRUNC('month', activity_date) activity_month, MAX(activity_date) last_activity_in_month
FROM ranked WHERE rn = 1 GROUP BY user_id, DATE_TRUNC('month', activity_date);

Step 3 — churn calculation using the materialized table, joining month M to any activity in (month_end, month_end + 30 days]:

sql

WITH m AS (
  SELECT user_id, activity_month, last_activity_in_month
  FROM user_month_activity
  WHERE activity_month = DATE '2025-10-01'  -- single-partition scan
)
, m1 AS (
  SELECT user_id
  FROM user_month_activity
  WHERE activity_month = DATE '2025-11-01' -- only next month partition
)
SELECT
  m.activity_month,
  COUNT(*) AS active_users,
  SUM(CASE WHEN m1.user_id IS NULL THEN 1 ELSE 0 END) AS churned_users,
  SUM(CASE WHEN m1.user_id IS NULL THEN 1 ELSE 0 END)::float / COUNT(*) AS churn_rate
FROM m
LEFT JOIN m1 USING (user_id);

If churn definition requires 30 days beyond month end (covers overlapping months), precompute a small lookup of user activity in the 30-day window using partition-pruned raw_events limited to two partitions.

Optimizations and operational notes:- Partitioning: partition raw_events by date (daily or monthly) and user_month_activity by month to limit IO.- Indexing: create composite index on (activity_month, user_id) on user_month_activity for joins.- Materialization: refresh only new partitions; keep historical snapshots immutable for reproducibility.- Mini-batches: compute churn for rolling windows incrementally; store results in a churn_metrics table.- Memory: push heavy aggregations to the MPP engine (e.g., Snowflake/Redshift/Spark SQL) and avoid shuffles by partitioning by user_id when using Spark.

Explain-plan checks:- Verify partition pruning: ensure predicates on activity_date or activity_month appear in the plan and only target required partitions.- Check index/scan type: expect index seeks or partition scans, not full table scan.- Confirm join strategy: for huge user sets prefer hash join with sufficient memory, otherwise broadcast small side (e.g., next-month user list if small).- Estimate rows: ensure planner row estimates are close; if off, update table statistics and histograms.- IO and spill: check for disk spill during aggregation; increase memory or change plan (e.g., use staged sort/aggregate).- Runtime profile: verify actual vs. estimated and adjust filters, indexes, or rewrite queries (e.g., replace NOT EXISTS with LEFT JOIN ... IS NULL if planner favors that).

Edge cases:- Users with events exactly at month boundary — define inclusive/exclusive consistently.- Timezones: normalize timestamps.- Late-arriving events: backfill partitions and re-materialize affected month(s).

This strategy minimizes full scans by restricting reads to a few partitions, reduces row counts via windowed deduplication and aggregation into a compact materialized table, and ensures predictable, incremental churn calculations at scale.

MediumTechnical

50 practiced

Given a table user_activity(user_id, event_date), write SQL to label each user's activity in a given week as 'new' if the user's first activity is in that week, otherwise 'returning'. Provide a query to compute weekly counts of new vs returning users and discuss indexing strategies for large datasets.

Sample Answer

Approach: compute each user’s first activity date, map each event_date to its week (e.g., ISO week or week starting Monday), then label an event week as 'new' when user_first_week = event_week, otherwise 'returning'. Aggregate counts per week and label.

sql

-- Using first_activity per user and week buckets (week starts Monday)
WITH user_first AS (
  SELECT
    user_id,
    DATE_TRUNC('week', MIN(event_date))::date AS first_week
  FROM user_activity
  GROUP BY user_id
),
events_week AS (
  SELECT
    ua.user_id,
    DATE_TRUNC('week', ua.event_date)::date AS event_week
  FROM user_activity ua
)
SELECT
  ew.event_week,
  CASE WHEN uf.first_week = ew.event_week THEN 'new' ELSE 'returning' END AS cohort_type,
  COUNT(DISTINCT ew.user_id) AS users_count
FROM events_week ew
JOIN user_first uf USING (user_id)
GROUP BY ew.event_week, cohort_type
ORDER BY ew.event_week;

Key points:- Use DATE_TRUNC (Postgres) or equivalent (TRUNC(event_date,'IW') in Oracle, DATE_TRUNC in Redshift). Adjust week definition as needed.- COUNT(DISTINCT user_id) ensures unique users per week.

Indexing & performance for large data:- Maintain a clustered/partitioned table by event_date (daily/weekly partitions) to prune scans.- Create composite index on (user_id, event_date) to speed MIN(event_date) and grouping; or a covering index (user_id, event_date) INCLUDE (...) where supported.- If queries run frequently, persist a user_first table (materialized view) updated incrementally to avoid scanning full history.- For very large scale, compute first_activity in batch Spark job and store in key-value table (user_id -> first_week) to join in downstream analytics.- Ensure statistics are up-to-date and avoid SELECT DISTINCT over huge raw tables by pre-aggregating or using approximate distinct methods if exact counts unnecessary.

HardSystem Design

53 practiced

You inherit a slow nightly ETL implemented as a single large SQL transform which recomputes multiple analytics tables. Propose a SQL-first plan to make this ETL incremental, resilient to late-arriving data, idempotent, and faster. Include suggested schema changes (e.g., added updated_at/change_date), partitioning strategy, use of MERGE/upserts, and monitoring queries to validate correctness after changes.

Sample Answer

Clarify goal: convert monolithic nightly recompute into incremental, idempotent, late-data-tolerant ETL that updates multiple analytics tables with SQL-first changes.

Plan summary:1. Add change-tracking columns- Source tables: ensure (created_at, updated_at, deleted_at) or change_date and op_type if CDC available.- Target analytic tables: add last_processed_at, source_change_date, batch_id.

2. Partitioning & clustering- Partition targets by logical time window (event_date or ingestion_date) AND cluster by keys used in joins/filters (user_id, product_id).- Keep source raw/landing tables partitioned by ingestion_date for efficient reads.

3. Incremental selection (watermark)- Maintain a watermark table per pipeline: last_successful_max_updated_at (and optionally max_source_cdc_lsn).- Each run: SELECT rows WHERE updated_at > watermark OR updated_at BETWEEN watermark - late_threshold AND now() to capture late arrivals.

4. Idempotent writes using MERGE (upsert) and soft deletesExample MERGE:

sql

MERGE INTO analytics.orders tgt
USING (
  SELECT id, user_id, total, event_date, updated_at
  FROM staging.orders_delta
) src
ON tgt.id = src.id
WHEN MATCHED AND src.updated_at > tgt.source_change_date THEN
  UPDATE SET user_id = src.user_id, total = src.total, source_change_date = src.updated_at, last_processed_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (id, user_id, total, event_date, source_change_date, last_processed_at)
  VALUES (src.id, src.user_id, src.total, src.event_date, src.updated_at, CURRENT_TIMESTAMP);

5. Late-arriving data handling- Reprocess windows for N days back (late_threshold), e.g., re-run increments for event_date >= CURRENT_DATE - INTERVAL '7 days'.- Use change_date comparison to minimize rows reprocessed.

6. Backfills and full-refreshs- Support explicit backfill jobs that write to a separate batch_id; after validation, swap partitions or update watermark.

7. Resilience & retries- Wrap pipeline in transactional steps where supported; write intermediate deltas to durable staging table with batch_id and status (running/committed/failed) for replay.

8. Monitoring & correctness queries- Watermark check:

sql

SELECT pipeline, last_successful_max_updated_at, max_src_updated_at FROM pipeline_watermarks;

- Row counts & diffs per partition:

sql

SELECT event_date, COUNT(*) as tgt_count, SUM(CASE WHEN src.updated_at > tgt.source_change_date THEN 1 ELSE 0 END) as pending_updates
FROM staging.src_delta src
LEFT JOIN analytics.tgt ON src.id = tgt.id
WHERE event_date BETWEEN ... GROUP BY event_date;

- Idempotency test: run same batch twice and assert no change:

sql

SELECT COUNT(*) FROM analytics.tgt WHERE last_batch_id = :batch_id;

- Data quality checks: referential integrity, null thresholds, distribution changes.

Trade-offs:- Using updated_at relies on source correctness; if not trustworthy use CDC or hash-based row fingerprints.- Partition granularity balances query pruning vs small-file overhead.

This SQL-first approach minimizes recompute, provides idempotent MERGE semantics, reprocesses recent windows for late data, and uses watermarking + monitoring to validate correctness.

EasyTechnical

74 practiced

Explain the difference between GROUP BY and DISTINCT. Provide examples for both and show when HAVING is required. For example, write queries to 'Find countries with more than 1000 users' using GROUP BY+HAVING and explain why DISTINCT by itself is insufficient for that metric.

Sample Answer

GROUP BY groups rows so you can aggregate per group; DISTINCT removes duplicate rows (or duplicate values in a single column) across the whole result. Use GROUP BY when you need aggregation per group (COUNT, SUM, AVG). DISTINCT is for de-duplicating results — it doesn't compute per-group aggregates by itself.

Example table: users(user_id, country)

Find countries with more than 1000 users — requires counting users per country, so GROUP BY + HAVING:

sql

-- count users per country, then filter groups with >1000 users
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 1000;

HAVING is required here because WHERE cannot filter on aggregate results; HAVING filters groups after aggregation.

Why DISTINCT alone is insufficient:

sql

-- DISTINCT returns unique countries, not counts
SELECT DISTINCT country
FROM users;

That returns each country once, but gives no count to test ">1000". You could combine DISTINCT with a subquery that counts, e.g.:

sql

SELECT country
FROM (
  SELECT country, COUNT(*) AS user_count
  FROM users
  GROUP BY country
) t
WHERE user_count > 1000;

Or use COUNT(DISTINCT ...) when deduplicating within an aggregate (e.g., count distinct user_ids per country): COUNT(DISTINCT user_id) inside GROUP BY.

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.