Data Problem Solving and Business Context Questions

Practical data oriented problem solving that connects business questions to correct, robust analyses. Includes translating business questions into queries and metric definitions, designing SQL or query logic for edge cases, handling data quality issues such as nulls duplicates and inconsistent dates, validating assumptions, and producing metrics like retention and churn. Emphasizes building queries and pipelines that are resilient to real world data issues, thinking through measurement definitions, and linking data findings to business implications and possible next steps.

EasyTechnical

23 practiced

Your product stores timestamps in UTC, and you have a user table with user_id and time_zone (IANA tz string). Describe how to compute daily active users by user's local date. Provide example SQL (PostgreSQL) or pseudocode to convert event_time to local date using the user's timezone, and explain how you would handle missing or invalid timezone values.

Sample Answer

Approach (brief): Convert each UTC event timestamp to the user's local timestamp using their IANA timezone, extract the local date, then count distinct users per local date. Handle missing/invalid timezones by falling back to a safe default (e.g., 'UTC') or a cleaned/mapped value and logging rows for later data-quality fixes.

Example SQL (PostgreSQL). Two common event_time column types are shown:

1) event_time is timestamptz (stored in UTC)

SELECT
  (event_time AT TIME ZONE COALESCE(u.time_zone, 'UTC'))::date AS local_date,
  COUNT(DISTINCT e.user_id) AS dau
FROM events e
JOIN users u ON e.user_id = u.user_id
GROUP BY local_date
ORDER BY local_date;

2) event_time is timestamp without time zone but known to be UTC (explicitly mark it first)

SELECT
  ((e.event_time AT TIME ZONE 'UTC') AT TIME ZONE COALESCE(u.time_zone, 'UTC'))::date AS local_date,
  COUNT(DISTINCT e.user_id) AS dau
FROM events e
JOIN users u ON e.user_id = u.user_id
GROUP BY local_date
ORDER BY local_date;

Validating / handling invalid IANA names:- Preferred: validate user.time_zone against pg_timezone_names and fallback to 'UTC' (or a default per business rule), and surface the invalid rows for correction.

WITH valid_tz AS (
  SELECT name FROM pg_timezone_names
)
SELECT
  ((e.event_time AT TIME ZONE 'UTC') AT TIME ZONE COALESCE(u.time_zone, 'UTC'))::date AS local_date,
  COUNT(DISTINCT e.user_id) AS dau
FROM events e
JOIN users u ON e.user_id = u.user_id
LEFT JOIN valid_tz tz ON u.time_zone = tz.name
-- if tz.name IS NULL, the user's timezone is invalid; use UTC fallback and optionally flag
GROUP BY local_date
ORDER BY local_date;

Notes, edge cases and best practices:- Daylight Saving Time is handled automatically by PostgreSQL when using IANA names.- If many invalid/missing timezones exist, run a data-quality job to infer timezone from profile data / IP / signup locale or prompt users to set it.- Be explicit about event_time type: if you mis-handle timestamp vs timestamptz you’ll shift dates.- For large tables, precompute and store user_local_date in a daily ETL to speed analytics and ensure consistent logic.

MediumSystem Design

45 practiced

Design an ETL job to ingest daily transactions that sometimes arrive late and occasionally require backfills. Describe how you would implement idempotent upserts, watermarks for late windows, and a mechanism to reprocess backfills without double-counting. What metadata would you store to support audits?

Sample Answer

Requirements & constraints- Daily transactions arrive in files/stream; some arrive late; occasional backfills required.- Consumers need accurate aggregates (no double-counting) and auditable provenance.

High-level design- Ingest raw messages into a landing table (raw_events) capturing full payload + metadata.- Normalize/transform into a canonical transactions table via idempotent upserts into a curated table (txn_curated).- Maintain auxiliary tables: processing_log (batch metadata) and watermark_tracker.

Idempotent upserts- Use a stable natural key (transaction_id) or deterministic composite key (user_id, event_time, vendor_id).- Each raw event carries: transaction_id, event_time, amount, source_batch_id, file_checksum, ingestion_time.- Upsert pattern (pseudo-SQL):

sql

-- txn_curated primary key: transaction_id
MERGE INTO txn_curated t
USING (SELECT * FROM staging_transactions) s
ON t.transaction_id = s.transaction_id
WHEN MATCHED AND s.source_batch_id > t.source_batch_id THEN
  UPDATE SET amount = s.amount, event_time = s.event_time, source_batch_id = s.source_batch_id, last_modified = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (transaction_id, event_time, amount, source_batch_id, last_modified) VALUES (...)

- Use source_batch_id (monotonic per ingestion) and file_checksum to decide newer vs older; idempotency ensured by deterministic key + not re-applying same source_batch_id.

Watermarks & late windows- Track watermark per source/topic in watermark_tracker: watermark = max(event_time) processed minus allowed_lateness.- Allowed lateness (e.g., 48 hours) chosen by business.- For windowed aggregates, only advance finalization of a window after watermark passes window_end + allowed_lateness. Before that, emit tentative aggregates marked as partial.- Implementation: compute aggregates from txn_curated filtering event_time <= watermark for final windows; keep separate partial_aggregates table for in-flight windows.

Backfill & reprocess without double-counting- Backfill runs create a new processing_log entry with backfill_id and target_date_range; they write to staging and then MERGE with higher precedence: backfill source_batch_id > normal batch ids for the range.- To avoid double-counting: - Use MERGE logic comparing source_batch_id / file_checksum. - Alternatively implement soft-deletes and versioning: txn_versions table that stores every version with version_ts and active flag; reprocess marks older versions inactive and activates new one. - For aggregates, compute from current canonical table (txn_curated active records) rather than incrementally adding counts. If incremental required, store change-delta per batch and ensure idempotent application keyed by (aggregate_id, source_batch_id).

Metadata for audits- processing_log: source_batch_id, source_type, file_name, file_checksum, ingestion_time, processed_by, row_count, backfill_id (nullable), status.- raw_events: ingestion_time, source_batch_id, file_checksum, raw_payload.- txn_curated: transaction_id, event_time, amount, source_batch_id, last_modified, version_id.- watermark_tracker: source, watermark_time, allowed_lateness, last_updated.- txn_versions (optional): version_id, transaction_id, effective_from, effective_to, active_flag, source_batch_id.- Aggregation_audit: aggregate_window_start, aggregate_window_end, computed_at, computed_by, source_batch_ids_used, checksum_of_input, aggregate_value.

Operational notes & best practices- Make source_batch_id monotonic (timestamp + sequence) and apply checksums to detect exact-duplicate replays.- Keep raw retention long enough to re-run backfills; store partitioned by ingestion_date.- Provide idempotent CLI/API for reprocessing a backfill: it creates a new processing_log entry and runs the same pipeline; MERGE semantics ensure no double-counting.- Monitor: failure alerts, counts comparison between raw and curated, and data-quality checks (nulls, negative amounts).This approach gives deterministic idempotency, clear late-arrival handling via watermarks, safe backfills via versioning/precedence, and rich metadata for auditing.

MediumTechnical

23 practiced

Write a SQL query to compute 7-day retention for user signup cohorts. Schema:

events(user_id uuid, event_name text, event_time timestamp with time zone)

Define cohort by users' first event (signup) date, and report retention for days 0 through 7 for cohorts in the last 90 days. Explain how you handle users with no activity after signup and late-arriving events.

Sample Answer

Approach: determine each user’s signup (first) date, build cohorts for signups in the last 90 days, then for each cohort compute how many distinct users returned on day 0..7 (day 0 = signup day). Include users with no later activity by keeping them in cohort denominator. To mitigate late-arriving events, either join on event ingestion timestamp if available, or apply a small buffer (e.g., only consider cohorts ending at now() - 48h).

SQL (PostgreSQL):

sql

WITH first_signup AS (
  -- first signup timestamp per user
  SELECT user_id,
         date_trunc('day', MIN(event_time))::date AS cohort_date
  FROM events
  WHERE event_name = 'signup'
  GROUP BY user_id
),
cohorts AS (
  -- only cohorts in the last 90 days (apply a 48h buffer for late arrivals)
  SELECT *
  FROM first_signup
  WHERE cohort_date BETWEEN (current_date - interval '90 days')::date
                        AND (current_date - interval '2 days')::date
),
events_after AS (
  -- events mapped to cohort and day offset (0..7)
  SELECT c.cohort_date,
         c.user_id,
         (date_trunc('day', e.event_time)::date - c.cohort_date) AS day_offset
  FROM cohorts c
  LEFT JOIN events e
    ON e.user_id = c.user_id
   -- consider events from signup through day 7 inclusive
  WHERE e.event_time IS NOT NULL
    AND date_trunc('day', e.event_time)::date BETWEEN c.cohort_date
                                                 AND c.cohort_date + 7
),
daily_active AS (
  -- distinct active users per cohort per day
  SELECT cohort_date,
         day_offset,
         COUNT(DISTINCT user_id) AS active_users
  FROM events_after
  GROUP BY cohort_date, day_offset
),
cohort_sizes AS (
  SELECT cohort_date,
         COUNT(*) AS cohort_users
  FROM cohorts
  GROUP BY cohort_date
),
days AS (
  SELECT generate_series(0,7) AS day_offset
)
SELECT
  c.cohort_date,
  cs.cohort_users,
  d.day_offset,
  COALESCE(da.active_users, 0) AS users_active,
  ROUND(100.0 * COALESCE(da.active_users,0) / cs.cohort_users, 2) AS retention_pct
FROM cohort_sizes cs
CROSS JOIN days d
LEFT JOIN daily_active da
  ON da.cohort_date = cs.cohort_date
 AND da.day_offset = d.day_offset
ORDER BY c? -- placeholder removed
 cs.cohort_date DESC, d.day_offset;

Key points / reasoning:- Cohort defined by first signup date (date only) so comparisons are by day.- Denominator includes all users in cohort (so users with no later events produce 0% for later days).- generate_series ensures days with zero activity still appear.- Late-arriving events: I apply a 48-hour buffer when selecting cohorts; for stricter correctness, use an ingestion timestamp column (e.g., event_ingested_at) to exclude events that arrived after analysis cutoff.- Time complexity is linear in events scanned; using proper indexes on events(user_id, event_time) and filtering by cohort users improves performance.

EasyTechnical

21 practiced

You observe a one-day spike in signups on March 1. List the SQL checks and data validations you would run to determine whether the spike is a true business event or a data ingestion/duplication error. Provide example SQL snippets or steps you would run against event and ingestion logs.

Sample Answer

Start by clarifying scope: “signups” = rows in events table (signup events) or new users in users table? Then run a sequence of checks from data-level to business-level.

1) Basic sanity: counts by day + compare to rolling baseline

sql

SELECT event_date, COUNT(*) AS signups
FROM events
WHERE event_type = 'signup'
GROUP BY event_date
ORDER BY event_date;

2) Check duplicates (same user_id/email created multiple times that day)

sql

SELECT user_id, email, COUNT(*) cnt
FROM events
WHERE event_type='signup' AND event_date='2025-03-01'
GROUP BY user_id, email
HAVING COUNT(*) > 1;

3) New users table vs events: ensure inserts match

sql

SELECT
  e.count_events, u.count_users
FROM
  (SELECT COUNT(*) count_events FROM events WHERE event_type='signup' AND event_date='2025-03-01') e,
  (SELECT COUNT(*) count_users FROM users WHERE created_at::date='2025-03-01') u;

4) Ingestion/ETL logs: check batch runs & timestamps

sql

SELECT run_id, status, rows_ingested, started_at, finished_at
FROM etl_runs
WHERE date(started_at) = '2025-03-01'
ORDER BY started_at;

5) Source attribution (channel/campaign/IP/region) — look for abnormal concentration

sql

SELECT source, COUNT(*) cnt
FROM events
WHERE event_type='signup' AND event_date='2025-03-01'
GROUP BY source ORDER BY cnt DESC LIMIT 10;

6) IP / user agent / device skew — detect bots or replay

sql

SELECT ip_address, user_agent, COUNT(*) cnt
FROM events
WHERE event_type='signup' AND event_date='2025-03-01'
GROUP BY ip_address, user_agent
HAVING COUNT(*) > 10
ORDER BY cnt DESC;

7) Temporal pattern within day — sudden bursts at ETL times

sql

SELECT DATE_TRUNC('hour', created_at) hour, COUNT(*) cnt
FROM events
WHERE event_type='signup' AND event_date='2025-03-01'
GROUP BY hour ORDER BY hour;

8) Backfill / late-arriving data: check created_at vs event_timestamp

sql

SELECT COUNT(*) FROM events
WHERE event_type='signup' AND event_date='2025-03-01' AND event_timestamp < '2025-02-25';

9) Referential integrity / malformed rows

sql

SELECT COUNT(*) FROM events
WHERE event_type='signup' AND event_date='2025-03-01' AND (user_id IS NULL OR email IS NULL);

10) Cross-check with downstream metrics (active users, conversions) and stakeholder confirmation — ask marketing about campaigns or third-party imports.

Interpretation: if counts align across systems, sources distributed, and temporal pattern looks organic → true business signal. If duplicates, single IP/user agent, ingestion batch spikes, or mismatched ETL logs → likely data issue. Provide findings, recommended fix (dedupe, rollbacks, ETL patch) and monitor next 48–72 hours.

MediumTechnical

26 practiced

Define 'monthly churn rate' for a SaaS product with monthly auto-renew billing. You have subscriptions(user_id, plan_id, start_date, end_date, canceled_at). Provide SQL or pseudocode to compute monthly customer churn rate and explain treatment of partial months, trial users, and failed payments.

Sample Answer

Definition and approach:- Monthly churn rate = (Number of customers who churned during month M) / (Number of customers active at the start of month M).- "Active at start" = had an active subscription on 00:00 of the first day of month M (start_date <= month_start AND (end_date IS NULL OR end_date >= month_start)).- "Churned during month" = subscription that was active at month_start and whose canceled_at (or end_date) falls within that month and is not immediately reactivated.- Compute per-customer (unique user_id) to avoid multi-subscription double-counting.

SQL (standard SQL / BigQuery-style):

sql

WITH months AS (
  SELECT generate_date_array('2024-01-01', '2024-12-01', INTERVAL 1 MONTH) AS m
), month_row AS (
  SELECT m AS month_start, DATE_ADD(m, INTERVAL 1 MONTH) AS month_end
  FROM UNNEST((SELECT m FROM months)) AS m
),
active_at_start AS (
  SELECT mr.month_start,
         s.user_id
  FROM month_row mr
  JOIN subscriptions s
    ON s.start_date <= mr.month_start
   AND (s.end_date IS NULL OR s.end_date >= mr.month_start)
  GROUP BY mr.month_start, s.user_id
),
churned_in_month AS (
  SELECT mr.month_start,
         s.user_id
  FROM month_row mr
  JOIN subscriptions s
    ON s.start_date <= mr.month_start
   AND (s.end_date IS NULL OR s.end_date >= mr.month_start)
   AND (s.canceled_at IS NOT NULL AND s.canceled_at >= mr.month_start AND s.canceled_at < mr.month_end)
  -- exclude short-term reactivations: ensure no active subscription for that user at month_end
  LEFT JOIN subscriptions s2
    ON s.user_id = s2.user_id
   AND s2.start_date <= mr.month_end
   AND (s2.end_date IS NULL OR s2.end_date >= mr.month_end)
  WHERE s2.user_id IS NULL
  GROUP BY mr.month_start, s.user_id
)
SELECT
  a.month_start,
  COUNT(DISTINCT a.user_id) AS customers_at_start,
  COUNT(DISTINCT c.user_id) AS churned_during_month,
  SAFE_DIVIDE(COUNT(DISTINCT c.user_id), NULLIF(COUNT(DISTINCT a.user_id),0)) AS churn_rate
FROM active_at_start a
LEFT JOIN churned_in_month c
  ON a.month_start = c.month_start AND a.user_id = c.user_id
GROUP BY a.month_start
ORDER BY a.month_start;

Treatment notes and recommendations:- Partial months: Use the "active at start" convention (most common) — counts customers who were paying/active at month start; cancellations anytime in month count as churn. Alternatively report mid-month prorated churn in a companion metric but avoid mixing definitions.- Trial users: Exclude trial-only users from primary churn metric or segment churn by lifecycle stage. Define trialers as subscriptions with is_trial flag or start without payment method; only include them after they convert to paid. Report trial-to-paid conversion and trial churn separately.- Failed payments: Follow product's dunning policy. If failed payment results in automatic cancellation (or subscription state becomes canceled after grace period), count as churn and tag as "payment_failure_churn". If the customer remains active in a grace/past_due state, treat as active until final cancellation. Provide separate buckets: voluntary churn vs payment-failure churn to inform retention vs billing ops.

Extra: always publish churn definition in dashboard metadata, and show numerator breakdown (voluntary, payment_failure, fraud, plan changes) for actionability.

Unlock Full Question Bank

Get access to hundreds of Data Problem Solving and Business Context interview questions and detailed answers.

Join thousands of developers preparing for their dream job.