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

transactions(transaction_id, customer_id, amount NUMERIC, transaction_date DATE). Write SQL to flag transactions on a given day that are anomalous: amount > median + 3 * IQR per customer over the trailing 90 days. Provide a solution that computes per-customer 90-day median and IQR and then flags today's transactions. Discuss performance considerations and options for incremental computation.

Sample Answer

Approach: for each customer and a target date (e.g., '2025-12-05'), compute the 25th and 75th percentiles and median of amounts over the trailing 90 days (excluding the target day), compute IQR = q3 - q1, compute threshold = median + 3*IQR, then join to that day's transactions to flag anomalies.

SQL (Postgres syntax using percentile_cont):

sql

WITH params AS (
  SELECT DATE '2025-12-05' AS target_day
),
window_data AS (
  -- rolling 90-day history per customer (exclude target day)
  SELECT
    t.customer_id,
    percentile_cont(0.25) WITHIN GROUP (ORDER BY t.amount) AS q1,
    percentile_cont(0.5)  WITHIN GROUP (ORDER BY t.amount) AS median,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY t.amount) AS q3
  FROM transactions t
  CROSS JOIN params p
  WHERE t.transaction_date >= p.target_day - INTERVAL '90 days'
    AND t.transaction_date < p.target_day
  GROUP BY t.customer_id
),
today_tx AS (
  SELECT t.*
  FROM transactions t
  JOIN params p ON t.transaction_date = p.target_day
)
SELECT 
  d.*,
  w.q1, w.median, w.q3,
  (w.q3 - w.q1) AS iqr,
  CASE
    WHEN w.median IS NULL THEN FALSE  -- insufficient history
    WHEN d.amount > w.median + 3 * (w.q3 - w.q1) THEN TRUE
    ELSE FALSE
  END AS is_anomaly
FROM today_tx d
LEFT JOIN window_data w USING (customer_id);

Key points:- percentile_cont gives exact interpolation-based percentiles; group-by customer computes per-customer stats.- Exclude customers with insufficient history (NULLs) or treat them separately.

Performance considerations and incremental options:- Scanning 90-day data for every target day can be expensive at scale. Improve by: - Index on (transaction_date, customer_id, amount) to speed range scans. - Pre-aggregate daily per-customer sketches: store daily histograms/t-digests or quantile summaries for each customer; then merge 90-day summaries to produce approximate percentiles (much faster). - Use approximate percentile functions (e.g., Postgres extension tdigest, BigQuery APPROX_QUANTILES, Redshift APPROXIMATE PERCENTILE) for speed and lower memory. - Materialized view / summary table: maintain a rolling 90-day summary per customer updated incrementally each day (add new day, drop day-91) — O(customers) per day instead of O(transactions). - Streaming/DBT approach: maintain a per-customer state (q1, q2, q3 or t-digest) in a table and update with each day's batch.- Trade-offs: exact percentiles vs approximate (accuracy vs speed); memory when computing many per-customer aggregations in one query.- Validate thresholds with business rules (min transaction count threshold, cap extreme IQRs) and monitor false positives.

MediumTechnical

73 practiced

Explain how to design indexes for queries that frequently filter by customer_id and a date range and also order by created_at. Give examples of single-column and composite indexes, including covering indexes, and explain the trade-offs like write amplification and maintenance cost.

Sample Answer

Situation: You frequently run queries like:SELECT ... FROM eventsWHERE customer_id = ? AND created_at BETWEEN ? AND ?ORDER BY created_at DESC;

Goal: make these fast for a Data Analyst’s reporting/ad-hoc workloads with minimal extra cost.

Recommendation and reasoning- Equality then range rule: Put equality columns before range columns in a composite index so the DB can seek to the customer and then do an ordered range scan on created_at. That supports both the WHERE and ORDER BY without a filesort.

Examples

Single-column index- Good when queries sometimes only filter by one field.

sql

CREATE INDEX idx_events_customer ON events(customer_id);
CREATE INDEX idx_events_created_at ON events(created_at);

- These help selective single filters, but won’t eliminate a sort or a range+order optimization when both filters are used.

Composite index- Primary recommended index:

sql

CREATE INDEX idx_events_cust_created ON events(customer_id, created_at DESC);

- This lets the engine seek to customer_id, scan the created_at range in descending order, and satisfy ORDER BY created_at DESC cheaply.

Covering index- If queries select only a few columns (e.g., id, amount), include them to avoid lookups:

sql

CREATE INDEX idx_events_cover ON events(customer_id, created_at DESC, amount, id);

- The index itself contains all requested columns — no table lookup — much faster for large result sets.

Trade-offs- Write amplification & maintenance: each additional index increases cost on INSERT/UPDATE/DELETE (extra writes, locking/time). Covering indexes are fastest on reads but cost most on writes and storage.- Storage: composite and covering indexes consume more disk and memory for caching.- Selectivity: If customer_id is very low-cardinality (few customers), index usefulness drops. Consider indexing created_at alone or partitioning by date instead.- Flexibility: Adding many narrow indexes for varied ad-hoc queries can explode maintenance costs — prioritize indexes for the most frequent, high-impact queries.- Alternatives: Partitioning by date (or customer) can accelerate range queries and reduce index size; combine with a composite index for best effect.

Best practice- Start with (customer_id, created_at DESC). Measure using EXPLAIN and real query profiles. Add covering columns only when that query is frequent and performance-critical. Monitor write latency and index sizes; drop or consolidate low-value indexes.

MediumTechnical

67 practiced

raw_events(event_id, external_id TEXT, insert_ts TIMESTAMP, payload JSONB). You find duplicate external_id rows; remove duplicates keeping the row with the latest insert_ts. Write a safe DELETE query (Postgres) using a CTE and window functions. Discuss transactional considerations for running this in production.

Sample Answer

Approach: use a CTE with ROW_NUMBER() partitioned by external_id ordered by insert_ts DESC (and event_id as tiebreaker). Mark rows with rn > 1 as duplicates and delete them. Test with a SELECT first, then run DELETE in a controlled transaction or in batches for large tables.

Query (safe pattern — preview then delete):

sql

-- 1) Preview duplicates to be deleted
WITH ranked AS (
  SELECT
    event_id,
    external_id,
    insert_ts,
    ROW_NUMBER() OVER (PARTITION BY external_id
                       ORDER BY insert_ts DESC, event_id DESC) AS rn
  FROM raw_events
  WHERE external_id IS NOT NULL
)
SELECT * FROM ranked WHERE rn > 1 LIMIT 100;

-- 2) Delete duplicates (single transaction)
BEGIN;

WITH ranked AS (
  SELECT
    event_id,
    ROW_NUMBER() OVER (PARTITION BY external_id
                       ORDER BY insert_ts DESC, event_id DESC) AS rn
  FROM raw_events
  WHERE external_id IS NOT NULL
)
DELETE FROM raw_events r
USING ranked
WHERE r.event_id = ranked.event_id
  AND ranked.rn > 1;

COMMIT;

Why this works:- ROW_NUMBER() picks one "keeper" per external_id (the latest insert_ts).- Tiebreaker on event_id ensures deterministic choice when insert_ts is equal.- USING lets DELETE reference the CTE result efficiently.

Transactional / production considerations:- Always run the SELECT preview first to validate which rows will be removed.- For large tables, delete in batches (LIMIT with primary-key ranges) to avoid long transactions and heavy WAL growth; e.g., loop deleting 10k–100k rows at a time.- Locking: DELETE will take row-level locks; avoid full table locks. Still, long transactions can bloat MVCC and block VACUUM; prefer small batches.- Replication & WAL: large deletes produce substantial WAL and can affect replicas. Consider copy-then-swap or CREATE TABLE AS + rename if downtime/replica lag is a concern.- Constraints/FKs: ensure no FK violations or cascade surprises. If other tables reference raw_events, check CASCADE behavior.- Post-cleanup: run VACUUM (and ANALYZE) to reclaim space and update planner statistics.- Prevent recurrence: add a UNIQUE index on external_id (or partial/conditional) if business logic allows: CREATE UNIQUE INDEX CONCURRENTLY idx_raw_events_external_id_unique ON raw_events (external_id) WHERE external_id IS NOT NULL; create after deduplication.- Test in staging and take backups/snapshots before mass deletes.

HardTechnical

65 practiced

survey_responses(user_id, score INT, response_date DATE). Each user can respond multiple times; for an NPS calculation over a survey window, keep the latest response per user in that window. Write SQL that computes NPS = %promoters (9-10) - %detractors (0-6). Return counts and NPS percentage. Handle users with no responses (exclude them).

Sample Answer

Approach: filter responses to the survey window, pick each user’s latest response in that window, classify as promoter/passive/detractor, then aggregate counts and compute NPS = %promoters − %detractors. Exclude users with no responses in the window.

sql

-- Parameters: substitute or bind :start_date and :end_date as needed
WITH windowed AS (
  SELECT user_id, score, response_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY response_date DESC, score DESC) AS rn
  FROM survey_responses
  WHERE response_date BETWEEN :start_date AND :end_date
),
latest_per_user AS (
  SELECT user_id, score
  FROM windowed
  WHERE rn = 1
),
classified AS (
  SELECT
    user_id,
    score,
    CASE
      WHEN score BETWEEN 9 AND 10 THEN 'promoter'
      WHEN score BETWEEN 7 AND 8  THEN 'passive'
      WHEN score BETWEEN 0 AND 6  THEN 'detractor'
      ELSE 'unknown' -- guard for null/out-of-range
    END AS bucket
  FROM latest_per_user
)
SELECT
  COUNT(*) FILTER (WHERE bucket = 'promoter')        AS promoters_count,
  COUNT(*) FILTER (WHERE bucket = 'passive')         AS passives_count,
  COUNT(*) FILTER (WHERE bucket = 'detractor')       AS detractors_count,
  COUNT(*)                                            AS total_respondents,
  ROUND(
    100.0 * (
      (COUNT(*) FILTER (WHERE bucket = 'promoter')::numeric / NULLIF(COUNT(*)::numeric,0))
      -
      (COUNT(*) FILTER (WHERE bucket = 'detractor')::numeric / NULLIF(COUNT(*)::numeric,0))
    ), 2
  ) AS nps_percentage
FROM classified;

Key points:- ROW_NUMBER picks the latest response per user; tie-breaker by score if same date.- Excludes users with no responses since only latest_per_user rows are used.- NULLIF prevents division by zero; total_respondents will be 0 if no rows (handle in caller).- You can adapt to other SQL dialects (use TOP/QUALIFY or DISTINCT ON in some DBs).

EasyTechnical

54 practiced

Given query: SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.created_at BETWEEN '2024-01-01' AND '2024-01-31' AND c.country = 'US'; Describe which indexes you would create to help this query and why. Mention trade-offs (write overhead, index size) and what histogram/cardinality information you would check before deciding.

Sample Answer

Recommended indexes (Postgres) and rationale:

1. Orders table- If the date filter is selective: btree on created_at. CREATE INDEX ON orders (created_at); Rationale: speeds the WHERE o.created_at BETWEEN ... scan.- If queries commonly filter by date and join by customer_id, use a composite: CREATE INDEX ON orders (created_at, customer_id); or if join/cardinality favors customer first: CREATE INDEX ON orders (customer_id, created_at); Rationale: composite lets the planner both filter by date and efficiently find matching customer_id rows without an extra index lookup.

- If orders is huge and created_at is append-only (monotonic), consider a BRIN index: CREATE INDEX ON orders USING brin (created_at); Rationale: very small, cheap to maintain for large time-series; less precise than btree.

2. Customers table- Ensure customers.customer_id is PRIMARY KEY (btree index exists).- Add index on country if country filter is selective: CREATE INDEX ON customers (country); Or composite to cover join: CREATE INDEX ON customers (country, customer_id); Rationale: quickly find US customers before joining.

Covering & projection note:- SELECT * prevents effective covering indexes; better to select only needed columns so you can INCLUDE them in an index: CREATE INDEX ON orders (created_at, customer_id) INCLUDE (order_total, status);

Trade-offs- More indexes => faster reads for targeted queries but added write overhead (INSERT/UPDATE/DELETE slower), larger disk and memory usage, more maintenance (vacuum, reindex).- BRIN trades query precision for much smaller size and lower write cost.

Histogram/cardinality checks before deciding- Fraction of orders in the date range (rows_between / total_rows). If very high (e.g., >20–30%), index on created_at may not help.- Distinct customer count and avg orders/customer.- Selectivity of country = 'US' (proportion of customers in US). If very high (>80%), country index is low value.- Correlation of created_at with physical row order (pg_statistics correlation). High correlation favors BRIN.- Use: ANALYZE, SELECT COUNT(*) filtered queries, and check pg_stats (most_common_vals, n_distinct, histogram_bounds), plus EXPLAIN ANALYZE to confirm planner choices.

Practical workflow- Run EXPLAIN ANALYZE before/after adding an index.- Prefer adding minimal composite indexes tuned to real query patterns and reduce SELECT * to allow covering indexes.

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.