SQL for Data Analysis Questions

Using SQL as a tool for data analysis and reporting. Focuses on writing queries to extract metrics, perform aggregations, join disparate data sources, use subqueries and window functions for trends and rankings, and prepare data for dashboards and reports. Includes best practices for reproducible analytical queries, handling time series and date arithmetic, basic query optimization considerations for analytic workloads, and when to use SQL versus built in reporting tools in analytics platforms.

MediumTechnical

61 practiced

Create an SQL test case (sample rows and query) that demonstrates an off-by-one error when computing monthly cohorts using date_trunc; explain the bug and provide the corrected SQL. Use sample data and show expected vs incorrect outputs conceptually.

Sample Answer

Situation: A common off-by-one error happens when cohorting by month if someone accidentally shifts the cohort month by adding/subtracting a month when it's unnecessary. Below is a small reproducible test showing the bug, the incorrect output, the explanation, and the corrected SQL.

Sample data (create table + rows):

sql

CREATE TEMP TABLE users_events(user_id int, signup_at timestamp, event_at timestamp);

INSERT INTO users_events VALUES
 (1, '2023-05-31 20:00', '2023-06-01 09:00'), -- signs end of May, event next day in June
 (2, '2023-06-05 10:00', '2023-06-20 12:00'), -- signs in June, event same month
 (3, '2023-04-01 08:00', '2023-04-15 11:00'); -- April

Incorrect query (off-by-one bug: accidentally shifts cohort to next month):

sql

-- BUG: adding + interval '1 month' moves cohort forward by one month
SELECT
  user_id,
  date_trunc('month', signup_at) + interval '1 month' AS cohort_month_wrong,
  date_trunc('month', event_at) AS event_month
FROM users_events
ORDER BY user_id;

Incorrect (conceptual) output:- user 1: cohort_month_wrong = 2023-06-01, event_month = 2023-06-01 => cohort assigned to June (wrong)- user 2: cohort_month_wrong = 2023-07-01, event_month = 2023-06-01 => cohort assigned to July (wrong)- user 3: cohort_month_wrong = 2023-05-01, event_month = 2023-04-01 => shifted

Why it's wrong:- Adding interval '1 month' to date_trunc('month', signup_at) shifts the cohort anchor to the next month. For signups at end-of-month (e.g., '2023-05-31') date_trunc('month',...) yields '2023-05-01' — adding one month makes it '2023-06-01', so the user is incorrectly placed into the following month's cohort.

Corrected query:

sql

SELECT
  user_id,
  date_trunc('month', signup_at)        AS cohort_month,
  date_trunc('month', event_at)         AS event_month
FROM users_events
ORDER BY user_id;

Expected (correct) conceptual output:- user 1: cohort_month = 2023-05-01, event_month = 2023-06-01 => member of May cohort with event in month+1- user 2: cohort_month = 2023-06-01, event_month = 2023-06-01 => June cohort, event in same month- user 3: cohort_month = 2023-04-01, event_month = 2023-04-01 => April cohort

Takeaway / test strategy:- Always assert cohort anchor for edge dates (month-ends, leap days).- Write unit tests with signups on last/first day of month to catch +/–1 month shifts.- When computing offsets (e.g., month_number = date_diff('month', cohort_month, event_month)), ensure both sides use the same truncation and no accidental +/- interval is applied.

HardTechnical

64 practiced

Given this EXPLAIN ANALYZE snippet (simplified):

Hash Join  (cost=1000..2000 rows=10000) (actual time=2000..2500 rows=10000)
  Hash Cond: (a.user_id = b.user_id)
  -> Seq Scan on large_table a (cost=0..800 rows=1000000) (actual time=0..800 rows=1000000)
  -> Hash (cost=900..900 rows=10000) (actual time=100..100 rows=10000)

Explain why this plan might be slow and list concrete SQL or schema changes you would try to improve runtime.

Sample Answer

Reason this plan is slow- The Hash Join's actual time range (2000..2500 ms) is dominated by building/reading a large input: the Seq Scan on large_table returns 1,000,000 rows. Hash join builds hash on the smaller side (b) but must stream the huge a table and probe a big hash; reading 1M rows from disk or doing lots of memory/IO can be expensive. Possible causes: missing indexes, poor statistics, wrong join order, insufficient work_mem forcing spill-to-disk during hash build/probe, or bloated table leading to full seq scan.

Concrete changes to try (SQL / schema / config)1) Ensure join column indexed on the large side for alternative plans (nested loop with index) or for planner cost estimates:

sql

CREATE INDEX ON large_table(user_id);
CREATE INDEX ON small_table(user_id);
ANALYZE large_table;
ANALYZE small_table;

2) Increase planner visibility and memory so hash fits RAM:- Increase work_mem for the session:

sql

SET work_mem = '128MB';

3) Force different join strategy for testing:

sql

SET enable_hashjoin = off;
EXPLAIN ANALYZE ...;

4) Reduce scanned rows via predicate pushdown / selective WHERE clauses or pre-filtering (materialized subquery):

sql

WITH filtered AS (
  SELECT * FROM large_table WHERE event_date >= '2025-01-01'
)
SELECT ... FROM filtered a JOIN small_table b USING (user_id);

5) Create a covering/indexed materialized view if same join repeated:

sql

CREATE MATERIALIZED VIEW mv AS SELECT ... FROM large_table JOIN small_table ...;
REFRESH MATERIALIZED VIEW mv;

6) Partition large_table by date (if queries often constrain by date) to avoid scanning all partitions.7) Re-evaluate statistics targets for skewed data:

sql

ALTER TABLE large_table ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE large_table;

How to validate- After each change run EXPLAIN ANALYZE and compare actual time, buffers, and whether seq scan is eliminated or rows reduced. Aim to reduce rows scanned or avoid spills to disk in hash operations.

HardSystem Design

63 practiced

You must design a time-series metrics schema for storing per-minute metrics for millions of users, with retention rules (store raw per-minute for 30 days, hourly rollups for 1 year). Sketch the table schemas, partitioning/clustering strategy, and SQL/automation approach to create hourly rollups from raw data. Focus on SQL patterns and trade-offs.

Sample Answer

Requirements clarification:- Raw per-minute metrics retained 30 days; hourly rollups retained 1 year.- Scale: millions of users, many metric types, high write volume.- System: relational / analytical DB (e.g., BigQuery, Redshift, Snowflake, Postgres+Timescale). Focus on SQL patterns and trade-offs.

Schema (normalized, append-only raw + aggregated):raw_minute_metrics- event_minute_ts TIMESTAMP NOT NULL, -- truncated to minute- user_id STRING / BIGINT NOT NULL- metric_name STRING NOT NULL- metric_value DOUBLE- ingestion_ts TIMESTAMP DEFAULT now()Primary/unique: (event_minute_ts, user_id, metric_name) optional depending on upstream de-dupPartitioning/clustering:- Partition by DATE(event_minute_ts) (daily partitions) to enable fast DROP for retention- Cluster / sort by (user_id, metric_name, event_minute_ts) to speed user-time range queries

hourly_rollups- event_hour_ts TIMESTAMP NOT NULL, -- truncated to hour- user_id- metric_name- sum_value DOUBLE- cnt BIGINT- min_value DOUBLE- max_value DOUBLE- last_update_ts TIMESTAMPPrimary: (event_hour_ts, user_id, metric_name)Partition by MONTH(event_hour_ts) (or WEEK) for 1-year retention, cluster by (user_id, metric_name)

SQL pattern to create hourly rollups (idempotent incremental MERGE)Initial full backfill / daily job:

sql

INSERT INTO hourly_rollups (event_hour_ts, user_id, metric_name, sum_value, cnt, min_value, max_value, last_update_ts)
SELECT
  date_trunc('hour', event_minute_ts) AS event_hour_ts,
  user_id,
  metric_name,
  SUM(metric_value) AS sum_value,
  COUNT(*) AS cnt,
  MIN(metric_value) AS min_value,
  MAX(metric_value) AS max_value,
  MAX(ingestion_ts) AS last_update_ts
FROM raw_minute_metrics
WHERE event_minute_ts >= DATE_SUB('day', 30)  -- for initial runs select range
GROUP BY 1,2,3;

Incremental scheduled merge (daily, covering last 48 hours to handle late arrivals):

sql

WITH src AS (
  SELECT date_trunc('hour', event_minute_ts) AS event_hour_ts, user_id, metric_name,
         SUM(metric_value) AS sum_value, COUNT(*) AS cnt,
         MIN(metric_value) AS min_value, MAX(metric_value) AS max_value,
         MAX(ingestion_ts) AS last_update_ts
  FROM raw_minute_metrics
  WHERE event_minute_ts >= TIMESTAMP_SUB(current_timestamp, INTERVAL '48' HOUR)
  GROUP BY 1,2,3
)
MERGE INTO hourly_rollups tgt
USING src
ON tgt.event_hour_ts = src.event_hour_ts AND tgt.user_id = src.user_id AND tgt.metric_name = src.metric_name
WHEN MATCHED THEN UPDATE SET
  sum_value = tgt.sum_value + src.sum_value,
  cnt = tgt.cnt + src.cnt,
  min_value = LEAST(tgt.min_value, src.min_value),
  max_value = GREATEST(tgt.max_value, src.max_value),
  last_update_ts = GREATEST(tgt.last_update_ts, src.last_update_ts)
WHEN NOT MATCHED THEN INSERT (...)

If MERGE not available, use staging table + upsert logic (DELETE + INSERT or INSERT ... ON CONFLICT UPDATE).

Automation:- Orchestrate with Airflow / Prefect / dbt Cloud scheduled DAGs: - Raw ingestion writes to raw_minute_metrics (streaming or micro-batches). - Hourly incremental job runs every hour or daily window; use watermark/lag window to include late data. - Daily retention jobs: DROP / VACUUM partitions older than 30 days on raw table; DROP monthly partitions older than 1 year on hourly_rollups, or rely on TTL features. - Backfill path: cadence to recompute historical windows via partitioned full-aggregate.

Trade-offs & considerations:- Partition granularity: daily partitions for raw balances partition count vs purge speed. Hourly partitioning could be too many partitions for millions of users.- Clustering keys: picking user_id helps user-level queries; metric_name too if cardinality low. High-cardinality clustering increases maintenance cost.- Aggregation windows: deterministic aggregates (sum,count,min,max) are easy to merge; for averages store sum+count. For distinct counts use HyperLogLog sketches to reduce state.- Late-arriving data: choose a lateness window (e.g., 48 hours) and run incremental re-aggregation for that window; store last_update_ts for auditing.- Storage vs compute: storing per-minute for 30 days uses space but enables fine-grained analysis; hourly rollups reduce longer-term storage and speed dashboards.- Consistency & idempotency: use idempotent MERGE/upsert; keep raw immutable for reconciliations.- Query patterns: provide materialized views for common aggregations; precompute user cohorts if needed.

This design balances retention, query performance, and operational simplicity for a data analyst querying per-user time series and business-level dashboards.

MediumTechnical

61 practiced

Given two tables `page_views(session_id, user_id, view_ts)` and `purchases(session_id, user_id, purchase_ts, amount)`, write ANSI SQL to compute average time (in seconds) from first page view in a session to purchase for sessions that converted within 24 hours. Return `avg_seconds_to_purchase`. Explain how you handle sessions with multiple views and multiple purchases.

Sample Answer

Approach: compute the first page view per session, find the earliest purchase in that session that occurs within 24 hours after that first view, compute the seconds difference, and average across sessions that converted within 24 hours. For sessions with multiple views we use the first view; for multiple purchases we pick the earliest purchase after that first view (and within 24h).

SQL (ANSI-style with common functions—EXTRACT(EPOCH FROM ...) shown; replace with your SQL engine’s equivalent if needed):

sql

WITH first_views AS (
  SELECT session_id, user_id, MIN(view_ts) AS first_view_ts
  FROM page_views
  GROUP BY session_id, user_id
),
first_valid_purchases AS (
  SELECT
    v.session_id,
    v.user_id,
    v.first_view_ts,
    MIN(p.purchase_ts) AS first_purchase_ts
  FROM first_views v
  JOIN purchases p
    ON p.session_id = v.session_id
    AND p.user_id = v.user_id
    AND p.purchase_ts >= v.first_view_ts
    AND p.purchase_ts <= v.first_view_ts + INTERVAL '24' HOUR
  GROUP BY v.session_id, v.user_id, v.first_view_ts
),
seconds_per_session AS (
  SELECT
    session_id,
    EXTRACT(EPOCH FROM (first_purchase_ts - first_view_ts)) AS seconds_to_purchase
  FROM first_valid_purchases
)
SELECT AVG(seconds_to_purchase) AS avg_seconds_to_purchase
FROM seconds_per_session;

Key points / reasoning:- MIN(view_ts) gets the true session entry point.- MIN(purchase_ts) after that and within 24h picks the earliest qualifying purchase when multiple purchases exist.- We filter purchases to >= first_view and <= first_view + 24 hours so only conversions within 24h are included.- Replace EXTRACT(EPOCH FROM ...) with your DB’s TIMESTAMP_DIFF/TIMESTAMPDIFF or equivalent if needed (e.g., TIMESTAMPDIFF(SECOND, first_view_ts, first_purchase_ts) in MySQL). Edge cases: sessions with no qualifying purchase are excluded; ties and identical timestamps produce zero seconds.

HardTechnical

66 practiced

Given a denormalized fact table with repeated product attributes that change over time, explain strategies to model product attributes so analytics queries show the attribute value at time of purchase. Describe SQL approaches to join with slowly changing dimensions (SCD Type 2), and show example SQL to join orders to the correct product attribute snapshot.

Sample Answer

Brief approach: treat product attributes as a Slowly Changing Dimension Type 2 (SCD2) where each attribute change creates a new row with validity window (effective_from, effective_to) or a surrogate product_key plus effective_from. At query time join orders to the SCD row that was valid at the order timestamp. Alternatives: (A) validity-range join (BETWEEN), (B) nearest-prior-change using window functions or APPLY/LATERAL, (C) precomputed snapshot at ETL time (materialize attributes into the fact).

Example schema:- orders(order_id, product_id, order_ts, qty, amount)- product_scd(product_sk, product_id, name, price, effective_from, effective_to, is_current)

1) Validity-range join (ANSI SQL)

sql

SELECT o.order_id, o.order_ts, o.product_id, p.name, p.price, o.qty, o.amount
FROM orders o
JOIN product_scd p
  ON o.product_id = p.product_id
 AND o.order_ts >= p.effective_from
 AND o.order_ts <  p.effective_to;  -- use half-open interval; use '9999-12-31' for open-end

2) Use APPLY / LATERAL to find latest SCD row <= order_ts (SQL Server / Postgres)

sql

-- SQL Server (CROSS APPLY)
SELECT o.order_id, o.order_ts, o.product_id, p.name, p.price
FROM orders o
CROSS APPLY (
  SELECT TOP (1) *
  FROM product_scd p
  WHERE p.product_id = o.product_id
    AND p.effective_from <= o.order_ts
  ORDER BY p.effective_from DESC
) p;

3) Window-function approach (portable)

sql

WITH ranked AS (
  SELECT o.*, p.name, p.price,
         ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY p.effective_from DESC) rn
  FROM orders o
  JOIN product_scd p
    ON o.product_id = p.product_id
   AND p.effective_from <= o.order_ts
)
SELECT * FROM ranked WHERE rn = 1;

Practical notes:- Use half-open intervals [effective_from, effective_to) and set effective_to = '9999-12-31' for current rows.- Index product_scd on (product_id, effective_from, effective_to) for fast range seeks.- For high-volume joins, consider materializing order attributes at ETL (store snapshot of product attributes on fact) to avoid expensive historical joins.- Handle edge cases: timezone normalization, late-arriving dimension changes (backfilling), and identical timestamps (tie-breaker via surrogate key or effective_from + product_sk).These patterns ensure analytics reflect attribute values as-of purchase time.

Unlock Full Question Bank

Get access to hundreds of SQL for Data Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.