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.

HardTechnical

52 practiced

Running median by day: Given a transactions table (txn_date, amount), describe SQL approaches to compute the daily median transaction amount for the past year. Provide at least two strategies (exact and approximate) and discuss performance trade-offs on large datasets.

Sample Answer

Exact approach (SQL window/ordered-set aggregates)1) Use DB-native percentile functions (Postgres/Redshift/Oracle) or window + row_number:

sql

-- Postgres ordered-set aggregate (exact median per day)
SELECT
  txn_date,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median_amt
FROM transactions
WHERE txn_date >= current_date - interval '1 year'
GROUP BY txn_date;

If ordered-set not available:

sql

-- Generic: rank and pick middle
WITH ranked AS (
  SELECT txn_date, amount,
         ROW_NUMBER() OVER (PARTITION BY txn_date ORDER BY amount) rn,
         COUNT(*) OVER (PARTITION BY txn_date) cnt
  FROM transactions
  WHERE txn_date >= current_date - interval '1 year'
)
SELECT txn_date,
       AVG(amount) FILTER (WHERE rn IN ((cnt+1)/2, (cnt+2)/2)) AS median_amt
FROM ranked
GROUP BY txn_date;

Approximate approach (fast, scalable)2) Use built-in approximate percentile UDFs or t-digest sketches in big-data engines:

sql

-- BigQuery/Presto style
SELECT txn_date,
       approx_percentile(amount, 0.5) AS approx_median
FROM transactions
WHERE txn_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AND CURRENT_DATE()
GROUP BY txn_date;

Or store per-day t-digest sketches and merge (supports streaming).

Performance trade-offs and operational guidance- Exact methods are CPU- and memory-heavy: window + sorting per day requires sorting all rows per date (O(n log n) per group). On high-cardinality days or very large tables this can spill to disk and be slow.- Ordered-set/percentile_cont is simpler but still needs to scan and sort per group; some engines optimize with streaming algorithms but many do not.- Approximate methods (approx_percentile, t-digest) use summaries/sketches; they are much faster, use bounded memory, and merge well across partitions—ideal for daily rollups on billions of rows, with small error margins.- Hybrid/production pattern: maintain incremental pre-aggregates (daily sketches or histograms) in a partitioned table or materialized view. Use exact calculation only for sample audits or low-volume days.- Indexing & partitioning: partition by txn_date, cluster/sort by amount to speed group processing. For OLAP warehouses, use date partition + columnar storage to reduce I/O.- Monitoring & accuracy: track approximation error (sample comparisons), and choose sketch parameters (t-digest compression) balancing accuracy vs memory.

Recommendation: For BI dashboards, use daily precomputed approx_percentile/t-digest in a scheduled job for low latency. Run exact medians on-demand for audits or small cohorts.

HardSystem Design

58 practiced

Design an analytical schema for an e-commerce analytics workload. Define fact and dimension tables, the grain of the main fact, and justify your choice. Include handling of product hierarchies, promotions, and multi-currency pricing in your design.

Sample Answer

Requirements & constraints:- Support attribution and revenue analytics, cohort, funnel, LTV, unit economics, multi-currency reporting, and product/category drill-downs. Near-real-time ETL from OLTP, business-friendly dimensions, slowly changing attributes.

Grain:- Order line item at the time of purchase (one row = one purchased SKU on an order). Justification: enables per-unit margin, promotion application, and product-level cohorts.

Core fact:- fact_order_line - order_line_id (PK), order_id, customer_id, sku_id, qty, unit_price_local, currency, exchange_rate_to_reporting, price_usd (computed), line_total_usd, discount_amount_usd, promo_id, tax_amount_usd, shipped_date, order_date, fulfillment_center_id

Dimensions:- dim_customer: customer_id, name, segment, signup_date, region, LTV_bucket (SCD2 for key attributes)- dim_sku: sku_id, product_id, current_price, cost, attributes (color/size), is_active (SCD2)- dim_product: product_id, product_name, brand_id, category_id, subcategory_id, launch_date (supports hierarchy keys)- dim_category: category_id, parent_category_id, category_level, category_path (precomputed hierarchy for fast rollup)- dim_promotion: promo_id, promo_name, promo_type (percent/fixed/bogo), start_date, end_date, eligibility_rules, discount_priority (SCD2)- dim_time: date_key, day, week, month, quarter, year, is_fiscal

Handling product hierarchies:- Maintain dim_category with parent pointers + category_path (e.g., "Electronics > Audio > Headphones"). Also keep dim_product -> category_id foreign key. Use recursive transforms to populate path and level for fast aggregation.

Promotions:- Capture promo_id on each line; store both applied discount and referenced promo attributes. For multi-promo rules, ETL should record promo_stack_id or multiple rows in fact_promo_applied linking order_line_id to promo_id with applied_amount_usd and priority to support attribution.

Multi-currency pricing:- Store unit_price in local currency and exchange_rate_to_reporting at transaction time. Normalize to reporting currency (e.g., USD) in ETL to compute price_usd/line_total_usd. Keep raw currency and rate for audit and FX revaluations.

Additional tables:- fact_refund_lines (refunds at line-grain), fact_inventory_snapshot (daily per sku per location), dim_store/fulfillment.

Design choices & trade-offs:- Line-grain gives flexibility for margin/promo analysis; larger fact size but efficient analytics with columnar DB (Snowflake/BigQuery/Redshift). SCD2 on dimensions preserves historical attribution. Storing exchange_rate at txn time preserves auditability and supports revaluation jobs. Precomputed category_path speeds dashboard rollups; separate promo mapping table handles complex promotions without bloating fact.

HardTechnical

61 practiced

Anomaly detection via SQL: Given a transactions table, write a SQL query (or describe one) to flag daily transaction totals that are statistical outliers vs the previous 30-day window. Return date, total_amount, z_score, is_outlier boolean (z_score threshold 3). Explain when this approach works and its limitations.

Sample Answer

Approach: compute per-day total_amount, then for each date compute mean and stddev of totals over the previous 30 days (excluding current day). Calculate z_score = (total - mean)/stddev and flag is_outlier when |z_score| >= 3. Use window functions and a 30-day RANGE or self-join if RANGE not supported.

SQL (Postgres-style; adjust function names for other DBs):

sql

WITH daily AS (
  SELECT
    DATE(tx_time) AS day,
    SUM(amount) AS total_amount
  FROM transactions
  GROUP BY DATE(tx_time)
),
rolling AS (
  SELECT
    d.day,
    d.total_amount,
    -- compute stats over previous 30 days excluding current day
    AVG(d2.total_amount) AS mean_30,
    STDDEV_SAMP(d2.total_amount) AS stddev_30
  FROM daily d
  LEFT JOIN daily d2
    ON d2.day >= d.day - INTERVAL '30 days'
   AND d2.day < d.day
  GROUP BY d.day, d.total_amount
)
SELECT
  day,
  total_amount,
  CASE WHEN stddev_30 = 0 OR stddev_30 IS NULL THEN NULL
       ELSE (total_amount - mean_30) / stddev_30 END AS z_score,
  CASE
    WHEN stddev_30 IS NULL OR stddev_30 = 0 THEN FALSE
    WHEN ABS((total_amount - mean_30) / stddev_30) >= 3 THEN TRUE
    ELSE FALSE
  END AS is_outlier
FROM rolling
ORDER BY day;

When this works:- Good for detecting large deviations vs recent baseline when daily totals are roughly stationary and approximately normally distributed after aggregation.- Simple, explainable, easy to implement in BI pipelines and alerts.

Limitations:- Sensitive to non-normal data, skew, seasonality, and trends (e.g., weekly patterns). May flag expected seasonal peaks.- Small sample window (fewer than ~10 distinct prior days) yields unstable mean/stddev; stddev can be zero.- Outliers in the 30-day window inflate stddev, masking anomalies.- For robustness consider: robust stats (median + MAD), seasonally-aware windows (compare to same weekday), weighted windows, exponentially weighted moving averages, or employ time-series anomaly detectors (ARIMA, Prophet, isolation forest) for complex patterns.

MediumTechnical

68 practiced

Explain differences between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions. Provide SQL examples that demonstrate when each gives different results and a BI use case for each (e.g., top-N selection, handling ties in leaderboards).

Sample Answer

ROW_NUMBER(), RANK(), and DENSE_RANK() all assign ordinal values over a window, but handle ties differently.

Example dataset and query (sales per rep):

sql

WITH sales AS (
  SELECT 'Alice' AS rep, 500 AS amt UNION ALL
  SELECT 'Bob', 700 UNION ALL
  SELECT 'Carol', 700 UNION ALL
  SELECT 'Dave', 300
)
SELECT rep, amt,
  ROW_NUMBER() OVER (ORDER BY amt DESC)    AS row_num,
  RANK()       OVER (ORDER BY amt DESC)    AS rank,
  DENSE_RANK() OVER (ORDER BY amt DESC)    AS dense_rank
FROM sales;

Results:- Bob (700): row_num=1, rank=1, dense_rank=1- Carol (700): row_num=2, rank=1, dense_rank=1- Alice (500): row_num=3, rank=3, dense_rank=2- Dave (300): row_num=4, rank=4, dense_rank=3

Key differences:- ROW_NUMBER(): gives a unique sequential number to each row regardless of ties — useful when you need an arbitrary deterministic tiebreaker (e.g., pick one row per group).- RANK(): gives same rank for ties but leaves gaps after ties (1,1,3…) — useful when you want standard competition ranking (tie-aware positions).- DENSE_RANK(): gives same rank for ties and no gaps (1,1,2…) — useful when you want consecutive ranks for grouping tied values.

BI use cases:- ROW_NUMBER(): Top-N per category with deterministic tie-breaker (e.g., select latest transaction per customer). Use when you need exactly N rows.

sql

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY txn_date DESC) rn
    FROM transactions
  ) t WHERE rn = 1;

- RANK(): Leaderboards where tied positions share the same place and next position reflects competition (e.g., tournament standings where two players tie for 1st and next is 3rd).- DENSE_RANK(): Bucketing KPI values into consecutive ranks for compact visualization (e.g., top-selling product tiers where ties should map to the next consecutive tier).

Edge considerations:- Combine with ORDER BY tie-breakers for deterministic ROW_NUMBER().- For Top-N with ties: use RANK() or DENSE_RANK() to include all tied entries rather than truncating with ROW_NUMBER().

HardTechnical

59 practiced

You're presented with a slow analytic SQL query. Describe the steps you take to diagnose and optimize it in a modern cloud data warehouse (e.g., BigQuery, Snowflake, Redshift). What metrics and tools do you use to find hotspots and what rewrite strategies are common?

Sample Answer

Approach — high level1) Reproduce & baseline: run the query with representative params and capture execution time, bytes scanned, slots/credits used and query profile. Record the slowest user-facing view / dashboard tiles so optimizations are measurable.2) Profile & find hotspots: inspect the explain/Query Profile to see scan volume, shuffle/repartition, join ordering, long-running stages, spills to disk, or concurrency waits. Tools:- BigQuery: Query Plan / Query History (bytes processed, slot ms), INFORMATION_SCHEMA.JOBS, Query Plan visualizer- Snowflake: Query Profile, QUERY_HISTORY,WAREHOUSE_METRICS (credits, bytes scanned, spills)- Redshift: EXPLAIN, STL_QUERY, SVL_QUERY_REPORT, WLM for queueing, disk-based stepsKey metrics: bytes scanned, slot-ms / CPU time, memory usage, shuffle/sort time, disk spill, queue time, number of partitions scanned.

Diagnosis checklist (what I look for)- Full table scans caused by missing partition/cluster pruning- Large shuffles from joining high-cardinality keys or missing broadcast- Expensive window functions or non-pushed-down predicates- Repeated subqueries or CTEs being recomputed- Wide SELECT * returning unnecessary columns- Data skew creating one slow reducer

Common rewrite strategies (practical, role-appropriate)- Push filters early: apply WHERE before joins; filter on partition columns to prune files.- Partitioning & clustering: ensure tables are partitioned on time/date and clustered on join/filter keys (BigQuery clustering or Snowflake clustering keys; Redshift sort keys).- Reduce data scanned: SELECT only needed columns, predicate pushdown, use materialized views or pre-aggregated summary tables for heavy dashboards.- Join tuning: re-order joins so the most selective filters/join-reducing tables run first; for small dimension tables use broadcast/replicated join; for large joins ensure both sides are clustered on join key to reduce shuffle.- Replace repeated CTEs with temp or staging tables when reused multiple times (avoid re-computation).- Pre-aggregate: rollups at the ETL layer or use grouping sets/materialized aggregates to avoid heavy GROUP BY at query time.- Rewrite window functions: convert to joins with precomputed aggregates where possible.- Use approximate functions (APPROX_COUNT_DISTINCT) where accuracy trade-off acceptable.- Minimize data movement: in Redshift tune distribution keys; in Snowflake choose appropriate warehouse size vs concurrency; in BigQuery reduce cross-region reads.

Testing & measurement- Apply changes incrementally and compare runtime, bytes processed, and user-facing latency.- Use A/B testing on dashboards: validate that results match and monitor downstream refresh times.- Automate checks: set alerts for query bytes growth or query latency regressions.

Example quick winOriginal: join two huge tables then filter:SELECT ... FROM big_events e JOIN users u ON e.user_id=u.id WHERE e.event_date BETWEEN X AND Y;Rewrite: filter events first (partition prune), select required columns into temp table or materialized view, then join:- CREATE TEMP TABLE recent_events AS SELECT user_id, event_type FROM big_events WHERE event_date BETWEEN X AND Y;- Then join recent_events to users (broadcast users if small).

Trade-offs & governance- Materialized views and pre-aggregates increase storage and ETL complexity—use for high-value dashboards.- Increasing warehouse/slots reduces latency but costs more—balance on SLAs.- Document and socialize changes with stakeholders; include tests to ensure correctness.

This process (baseline → profile → targeted rewrite → measure → monitor) gives repeatable, low-risk performance improvements for BI workloads in cloud warehouses.

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.