Problem Solving Under Constraints Questions

Assess how candidates identify, prioritize, and resolve problems when faced with limited time, limited resources, changing requirements, or unclear information. This includes execution discipline to maintain delivery and unblock teams, pragmatic adaptation of designs or plans to meet constraints, handling ambiguity by making reasonable assumptions and iterating, communicating trade offs and risks to stakeholders, and demonstrating creative but practical solutions that preserve core quality objectives. It also covers applied troubleshooting for realistic business problems such as calculating retention cohorts, reconciling datasets of differing granularity, or debugging data quality and pipeline issues, with emphasis on clearly explaining approach, assumptions, and recovery steps.

EasyBehavioral

45 practiced

Tell me about a time you had to deliver a machine learning model or analysis under a tight deadline and limited resources (compute, data, or people). Use the STAR structure: describe the Situation, the Task with explicit constraints, the Actions you prioritized (what you trimmed, what assumptions you made), and the Result. Highlight how you communicated trade-offs to stakeholders and any recovery steps you planned after delivery.

EasyTechnical

44 practiced

You have daily sales table (sales_by_day: date, product_id, total_revenue) and hourly web events (web_events: hour_ts, user_id, product_id, event_type). A stakeholder asks for daily conversion rate (purchases / visits) by product within 2 hours. Describe a pragmatic approach to reconcile the granularity mismatch, including SQL/pseudocode steps, assumptions about attribution windows, and how you'd flag uncertainty for stakeholders.

Sample Answer

Approach summary- Problem: web_events are hourly (event-level) and sales_by_day is daily revenue only — no purchase timestamps or order counts. Pragmatic plan: (A) derive daily visits from web_events, (B) derive daily purchases (or estimate counts) from sales_by_day, (C) compute conversion = purchases / visits with a 2-hour attribution window assumption and surface uncertainty/confidence.

Assumptions (state these to stakeholders)- “Visit” = event_type IN ('view','page_view','product_page') and we count unique users per product within day.- 2-hour attribution: a visit converts if a purchase happens within 2 hours after that visit.- sales_by_day has only revenue; we either have product_price table to convert revenue→count, or we estimate purchases = revenue / avg_order_value(product).- If an orders table exists, use it instead (preferred).

SQL/pseudocode steps

1) Visits per hour/day per product (unique users)

sql

WITH visits AS (
  SELECT
    DATE(hour_ts) AS day,
    product_id,
    COUNT(DISTINCT user_id) AS unique_visitors
  FROM web_events
  WHERE event_type IN ('view','product_page')
  GROUP BY 1,2
)

2) If orders table available, get purchase events with timestamps:

sql

, purchases AS (
  SELECT
    DATE(purchase_ts) AS day,
    product_id,
    COUNT(DISTINCT order_id) AS purchases
  FROM orders
  WHERE purchase_ts >= <start>
  GROUP BY 1,2
)

If no orders table, derive purchases from revenue:

sql

, purchases_est AS (
  SELECT
    day,
    product_id,
    ROUND(total_revenue / NULLIF(avg_price,0)) AS purchases_est
  FROM sales_by_day s
  JOIN product_price p USING(product_id)   -- or precomputed avg_price
)

3) Compute conversion by product/day:

sql

SELECT
  v.day,
  v.product_id,
  COALESCE(p.purchases, p_est.purchases_est) AS purchases,
  v.unique_visitors AS visits,
  SAFE_DIVIDE(COALESCE(p.purchases, p_est.purchases_est), NULLIF(v.unique_visitors,0)) AS conversion_rate
FROM visits v
LEFT JOIN purchases p
  ON v.day = p.day AND v.product_id = p.product_id
LEFT JOIN purchases_est p_est
  ON v.day = p_est.day AND v.product_id = p_est.product_id;

4) (Optional) 2-hour attribution sensitivity: if you have order timestamps and visit timestamps, join visits to purchases where purchase_ts BETWEEN visit_ts AND visit_ts + INTERVAL '2' HOUR and count unique visits that led to purchase. If only daily granularity, note this is an approximation.

Flagging uncertainty for stakeholders- Explicitly label rows as "estimated" when purchases come from revenue/avg_price rather than orders.- Provide confidence metrics: % of conversions based on actual orders vs estimated, coverage of web events (missing user_id?), and sensitivity analysis (recompute conversion with 1h, 2h, 4h windows and show variance).- Show top assumptions in a short doc: how visits defined, how AOV computed, timezone handling, bot filtering.- Recommend next steps to improve accuracy: join to orders table, capture purchase timestamps, instrument session IDs, or store purchase counts in daily sales.

Why this is pragmatic- Uses available data first, falls back to reasonable estimation, surfaces uncertainty so stakeholders understand limits, and provides concrete next steps to reduce ambiguity.

EasyTechnical

38 practiced

You're on-call and a nightly ETL job fails with 'null value in column user_id' which prevents downstream dashboards from refreshing. You have 30 minutes to restore service for stakeholders. Outline triage steps, a hotfix to restore pipeline quickly (temporary assumptions allowed), and longer-term remediations to prevent recurrence (including tests and monitoring you would add).

Sample Answer

Triage (first 0–10 minutes)- Quickly gather context: check alert details, pipeline logs, job run ID, and timestamps; identify where the null occurs (ingest, transform, or load).- Assess impact: which dashboards/tables depend on this job, who the stakeholders are, and the SLA (30 minutes goal).- Reproduce minimally: run a quick query on source/raw table to confirm presence and volume of null user_id (e.g., SELECT COUNT(*) FROM raw_events WHERE user_id IS NULL AND event_date = '…').- If possible, pause downstream consumers to avoid partial/incorrect refreshes.

Hotfix to restore service (10–25 minutes)- If nulls are limited and stakeholder priority is freshness over completeness, filter out null user_id rows during transformation: - e.g., in SQL transform step:

sql

INSERT INTO analytics.events_clean
    SELECT *
    FROM raw_events
    WHERE event_date = '…'
      AND user_id IS NOT NULL;

- Or replace nulls with a sentinel if dashboards tolerate aggregation by "unknown":

sql

COALESCE(user_id, 'unknown') AS user_id

- Run the ETL for the affected window manually, validate row counts and key aggregates against last successful run, and trigger downstream refresh.- Communicate: notify stakeholders that dashboards are live again and this is a temporary fix; log incident.

Longer-term remediations (post-incident)- Root cause analysis: determine why nulls appeared (source change, upstream bug, schema drift, delayed enrichment).- Data validation tests: - Add schema checks in the pipeline (e.g., Great Expectations or custom assertions) to fail early on unexpected nulls for required fields. - Row-level and aggregate checks (e.g., percent nulls per column; compare key metrics vs baseline).- Monitoring and alerting: - Instrument metrics: null rate for critical columns, row counts, pipeline latency; alert on thresholds or sudden deviations. - Add end-to-end freshness and sanity checks that run after ETL and prevent downstream refresh if checks fail (or flag dashboards).- Pipeline hardening: - Make required fields explicit in schema, enforce at ingestion; add defensive transformations (coalesce, validation). - Add idempotent reprocessing and clear run metadata to simplify reruns.- Tests and CI: - Unit tests for transform logic, integration tests with synthetic data including null scenarios. - Automate regression tests in CI so future changes can’t introduce nulls silently.- Follow-up: update runbook with steps I took, stakeholder communications, and schedule a postmortem to assign owners and deadlines.

This approach restores service fast while ensuring changes to prevent recurrence and improve observability.

HardSystem Design

43 practiced

Design a scalable data architecture to join petabyte-scale datasets with differing temporal granularities (minute telemetry, daily sales, monthly billing) supporting ad-hoc analytics while minimizing compute cost. Specify storage formats (columnar), partitioning schemes, pre-aggregation strategies, approximate join techniques (sampling or bloom-filtered joins), and how to provide reasonably fast exploratory queries for analysts.

Sample Answer

Requirements & constraints:- Join datasets at petabyte scale with different temporal granularities (minute telemetry, daily sales, monthly billing).- Support ad-hoc exploratory analytics with reasonably fast response times.- Minimize compute cost (avoid full shuffles/scans where possible).

High-level architecture:- Raw landing → ETL/ingest → Curated datasets in a data lake (Iceberg/Delta Lake) on object storage (S3/GCS).- Query layer: Presto/Trino or Spark SQL for heavy jobs; serverless SQL (Athena/BigQuery) for ad-hoc.- Catalog & metadata: Hive metastore/Glue + table versions.

Storage formats & layout:- Columnar: Parquet or ORC with dictionary encoding and ZSTD compression.- Partitioning: - Telemetry (high-cardinality, high-frequency): partition by date (YYYY-MM-DD) + bucket/hash on device_id or customer_id (e.g., 128 buckets) to avoid too many small files; sort within files by timestamp. - Sales (daily): partition by date (YYYY-MM-DD) + customer_id bucketing. - Billing (monthly): partition by billing_month (YYYY-MM) + customer_id bucket.- Clustering/sort within partitions on keys used in joins/time to improve predicate pushdown.

Pre-aggregation strategy:- Build time-windowed materialized aggregates at multiple granularities: - Telemetry -> minute, hourly, daily aggregates per customer/device. - Sales -> daily, weekly aggregates per customer/product. - Maintain aggregate tables in Iceberg/Delta (incrementally updated via streaming or scheduled batch).- Use compaction/partition pruning and maintain retention policies for raw vs aggregated data.

Approximate & cost-saving join techniques:- Multi-stage join strategy: 1. Try to join on aggregated tables (e.g., daily telemetry aggregates to daily sales) to reduce input size. 2. For cases needing raw telemetry sampling, use stratified sampling per customer/device to estimate metrics quickly. 3. Bloom-filtered semi-joins: build bloom filters from the smaller table (billing or customer set) and push them to filter telemetry blocks before full join — reduces data scanned and shuffle. 4. Precompute hashed join keys and use bloom filters at file/block level (store bloom metadata per file) so the query engine can skip files.- Use approximate algorithms where acceptable (HyperLogLog for uniques, t-digest for quantiles).

Query optimization & UX for analysts:- Maintain a set of interactive-friendly materialized views for common ad-hoc patterns (customer-day rollups, LTV windows).- Provide a “fast-preview” API that runs on sampled/aggregated datasets and returns estimated results with error bounds; an option to run a full-precision job if needed.- Expose cost/bytes-estimates in query UI and templates for common joins showing expected runtime.- Enable predicate pushdown, column pruning, and partition pruning via well-defined schema and strict partition keys.

Operational & cost controls:- Use serverless/on-demand compute for ad-hoc; schedule heavy joins and full-precision rebuilds in off-peak hours.- Auto-compaction and file sizing (target ~256MB) to reduce small file overhead.- Store bloom filter / min/max column stats at file granularity in table metadata to accelerate file pruning.- Monitor query patterns and promote frequently-used ad-hoc queries into cached materialized views.

Trade-offs:- More pre-aggregation and materialized views reduce cost and latency but increase storage and ETL complexity.- Approximate methods speed exploratory analysis but may require user awareness of error bounds.

Example flow for an analyst query (Revenue vs avg telemetry per customer-month):- Planner selects monthly sales and telemetry-daily aggregates.- Use partition pruning on billing_month and customer_id buckets.- Apply bloom-filter from billing customers to telemetry partitions to avoid scanning unrelated devices.- If interactive and permitted, run on sampled telemetry aggregates first; surface estimates + confidence intervals; optionally kick off full job.

This design balances fast exploratory responses with low compute by pushing filtering into storage (partitioning, bloom filters, file stats), preferring aggregated datasets, and offering approximate paths that can be escalated to full-precision when needed.

MediumTechnical

37 practiced

You have two production tables: revenue_daily(product_id, date, revenue) and web_events(hour_ts, product_id, event_type, user_id). Write performant SQL that reconciles revenue by day and product and flags days where revenue differs by >5% between the source of record and the aggregated events-derived revenue. Include approaches for improving performance on very large tables (indexes, partitions, incremental runs).

Sample Answer

Approach: Aggregate web_events to daily revenue by product (apply event->revenue mapping or join to price table), join to revenue_daily, compute relative difference, and flag >5%. Use windowing/CTEs for clarity and optimize with partitions/indexes and incremental delta runs for large tables.

sql

-- aggregate events to daily revenue per product and compare to source table
WITH events_daily AS (
  SELECT
    product_id,
    DATE_TRUNC('day', hour_ts) AS day,
    SUM(CASE
          WHEN event_type = 'purchase' THEN revenue_amount -- if stored on event
          WHEN event_type = 'purchase_line' THEN price * qty
          ELSE 0
        END) AS evt_revenue
  FROM web_events
  WHERE hour_ts >= CURRENT_DATE - INTERVAL '90 days'  -- limit for incremental run
  GROUP BY product_id, DATE_TRUNC('day', hour_ts)
)
SELECT
  r.product_id,
  r.date,
  r.revenue AS src_revenue,
  COALESCE(e.evt_revenue,0) AS evt_revenue,
  CASE WHEN r.revenue = 0 THEN NULL
       ELSE (e.evt_revenue - r.revenue) / r.revenue END AS pct_diff,
  CASE WHEN r.revenue <> 0 AND ABS((e.evt_revenue - r.revenue) / r.revenue) > 0.05 THEN TRUE ELSE FALSE END AS flag_mismatch
FROM revenue_daily r
LEFT JOIN events_daily e
  ON r.product_id = e.product_id AND r.date = e.day
WHERE r.date BETWEEN '2025-01-01' AND '2025-03-31'  -- restrict scope as needed
ORDER BY r.date, r.product_id;

Performance & scaling:- Partition web_events by date (hour_ts) and revenue_daily by date to prune scans.- Indexes: (product_id, hour_ts) or (hour_ts, product_id) on web_events; (product_id, date) on revenue_daily.- Pre-aggregate events into a materialized daily table (events_daily_mat) updated incrementally (append new days or use CDC).- Use LIMIT/WHERE date windows for incremental checks; run full reconciliation nightly and targeted deltas hourly.- Ensure statistics are up-to-date; consider query parallelism and avoiding SELECT * scans.Edge cases: NULL revenues, zero denominators, timezone alignment of hour_ts vs date, duplicated purchase events — dedupe by event_id if needed.

Unlock Full Question Bank

Get access to hundreds of Problem Solving Under Constraints interview questions and detailed answers.

Join thousands of developers preparing for their dream job.