Metric Definition and Implementation Questions

End to end topic covering the precise definition, computation, transformation, implementation, validation, documentation, and monitoring of business metrics. Candidates should demonstrate how to translate business requirements into reproducible metric definitions and formulas, choose aggregation methods and time windows, set filtering and deduplication rules, convert event level data to user level metrics, and compute cohorts, retention, attribution, and incremental impact. The work includes data transformation skills such as normalizing and formatting date and identifier fields, handling null values and edge cases, creating calculated fields and measures, combining and grouping tables at appropriate levels, and choosing between percentages and absolute numbers. Implementation details include writing reliable structured query language code or scripts, selecting instrumentation and data sources, considering aggregation strategy, sampling and margin of error, and ensuring pipelines produce reproducible results. Validation and quality practices include spot checks, comparison to known totals, automated tests, monitoring and alerting, naming conventions and versioning, and clear documentation so all calculations are auditable and maintainable.

EasyTechnical

59 practiced

Explain tumbling (calendar-aligned), sliding (rolling) and session-based windows for metric aggregation. When would you choose a rolling 7-day average instead of month-to-date, and what pitfalls arise from timezones and daylight savings when computing daily metrics?

Sample Answer

Tumbling (calendar-aligned), sliding (rolling) and session-based windows are common ways to aggregate time-series metrics:

- Tumbling (calendar-aligned) windows: fixed, non-overlapping intervals (e.g., daily 00:00–23:59 UTC, hourly, monthly). Each event belongs to exactly one window. Use when you need clear, auditable periods (billing, daily reports).

- Sliding (rolling) windows: fixed-duration windows that move continuously (e.g., 7-day rolling average computed at each day t covers t−6..t). Windows overlap; useful for smoothing noise and showing short-term trends.

- Session-based windows: dynamic windows driven by activity with inactivity gaps (e.g., user session ends after 30 minutes of inactivity). Useful for engagement metrics where natural session boundaries matter.

When to choose a rolling 7-day average vs month-to-date:- Rolling 7-day average: choose when you want to smooth weekday/weekend cyclical noise and reveal short-term trend changes irrespective of calendar month boundaries (e.g., trend detection, anomaly alerts).- Month-to-date: choose when aligning to business cycles, billing, or month-based targets is required.

Pitfalls with timezones and daylight savings:- Anchor aggregations to a consistent timezone (usually UTC) or explicitly to the business timezone. DST shifts can move the "day" boundary, causing 23- or 25-hour days if using local time—this can drop/duplicate events if you floor timestamps without handling offsets.- Use event timestamps (user-local or server) with a clear canonical datetime and store timezone-aware timestamps (UTC + original tz if needed).- Handle late-arriving/out-of-order events with watermarking or grace periods so they can be backfilled into previous windows.- Be cautious when comparing calendar-aligned periods across DST changes or when months have different lengths—normalize or annotate comparisons.Best practices: store UTC, convert for presentation, use windowing features that support timezones and late data, and document window semantics for consumers.

MediumTechnical

85 practiced

Write SQL to compute DAU, WAU, and MAU for the last 90 days from table events(user_id STRING, event_time TIMESTAMP, event_name STRING, ingestion_time TIMESTAMP). Deduplicate user activity per day using event_time normalized to UTC. Provide the SELECT query and discuss performance considerations and approximate alternatives for high cardinality.

Sample Answer

Approach: normalize event_time to UTC date, deduplicate one user per UTC day, then compute daily unique users (DAU) and rolling 7-day and 30-day unique counts (WAU/MAU) over the past 90 days. Use windowed aggregation on pre-aggregated per-day user uniques to avoid double-counting.

sql

WITH raw AS (
  SELECT
    user_id,
    -- normalize to UTC date (adjust if event_time has timezone)
    DATE_TRUNC('day', event_time AT TIME ZONE 'UTC')::date AS event_date
  FROM events
  WHERE event_time >= (CURRENT_DATE - INTERVAL '90 days') -- keep relevant range
),

-- dedupe per user per day
daily_users AS (
  SELECT DISTINCT user_id, event_date
  FROM raw
),

-- daily unique counts
dau AS (
  SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau_count
  FROM daily_users
  GROUP BY event_date
),

-- for rolling uniques we need the set of users per day; we can aggregate into arrays (works in Postgres) or use joins
-- here using relational approach: for each day, count distinct users in preceding window
wau_mau AS (
  SELECT
    d.event_date,
    -- DAU already available
    d.dau_count,
    -- 7-day unique (including current day)
    (SELECT COUNT(DISTINCT du.user_id)
     FROM daily_users du
     WHERE du.event_date BETWEEN d.event_date - INTERVAL '6 days' AND d.event_date) AS wau_count,
    -- 30-day unique
    (SELECT COUNT(DISTINCT du2.user_id)
     FROM daily_users du2
     WHERE du2.event_date BETWEEN d.event_date - INTERVAL '29 days' AND d.event_date) AS mau_count
  FROM dau d
)
SELECT *
FROM wau_mau
ORDER BY event_date DESC;

Performance considerations:- COUNT(DISTINCT) over large user sets is expensive. Ensure events table is partitioned by event_time and that daily_users is a compact pre-agg (materialized table) updated nightly/incrementally.- Indexes on (event_date, user_id) speed up the dedupe and windowed filters.- For online queries, avoid nested DISTINCT subqueries by precomputing and storing bitmaps or bloom filters per day.

Approximate / scalable alternatives for high cardinality:- HyperLogLog (HLL) sketches: store per-day HLL and compute union for WAU/MAU with small error (<1-2%). Supported in Postgres (hll extension), ClickHouse, BigQuery (APPROX_COUNT_DISTINCT).- Bitmap indexes (Roaring bitmaps): efficient set unions for user IDs if you can map IDs to integers.- Precompute rolling unions daily (incremental): maintain 7/30-day aggregated sets or sketches to serve queries in O(1).Edge cases: timezone of event_time sources, late-arriving events (use ingestion_time cutoff or backfill policy), bots/test users (filter via event_name or user flags).

EasyTechnical

84 practiced

Explain the difference between event-level and user-level metrics. Describe the steps to convert event-level data into user-level metrics (e.g., DAU, conversions per user) including deduplication, time-window choice, and sessionization pitfalls across devices.

Sample Answer

Event-level metrics count individual actions (pageviews, clicks, purchases). User-level metrics aggregate those events by user identity (DAU, conversions per user) so they reflect per-person behavior rather than raw event volume.

Steps to convert event-level data into user-level metrics1. Ensure reliable identity: choose primary user key (user_id if logged-in). Fall back to device_id/session_id only when necessary. Maintain an identity resolution table to map anonymous → known users after login.2. Deduplication: remove duplicate events by (user_key, event_type, event_id, event_timestamp) using the event_id or an idempotency key. For streaming, use watermarking and late-arrival policies; for batch, use SELECT DISTINCT or row_number() windowing.3. Normalize timestamps/timezones: store UTC and compute local windows consistently.4. Choose time-window semantics carefully: - Calendar-based (calendar day) for marketing/KPI reports. - Rolling windows (last 24h) for realtime monitoring. Document which you use and why.5. Sessionization: define session boundaries (e.g., 30 min inactivity) and compute session_id by user. Be cautious: - Cross-device: same user across devices may produce multiple concurrent sessions; merge by user_id but avoid falsely stitching anonymous device sessions. - Clock skew and delayed events can split sessions; use event timestamps + ingestion-time heuristics.6. Aggregate to user-level: for each user and window compute metrics (active = had >=1 qualifying event; conversions_per_user = conversions / unique_users or avg per user using GROUP BY user_id).7. Quality checks: sanity checks (events >= users), retention and identity merge audits, sampling tests.

Pitfalls and mitigations- Overcounting due to duplicates → idempotency keys, dedupe windows.- Under/over attribution across devices → robust identity graph and conservative stitching rules.- Misleading DAU because of bots or test accounts → filter known bot/test identifiers.- Sessionization inconsistency → make session rules explicit and apply uniformly in pipelines.

Quick checklist: canonical user key, dedupe by idempotency, UTC timestamps + defined window semantics, session rules documented, identity-resolution pipeline, monitoring for anomalies.

MediumTechnical

74 practiced

Cohort SQL exercise: Given table events(user_id STRING, event_time TIMESTAMP, event_name STRING), write SQL to compute weekly cohorts and their 7-day retention rates. Output should be rows like cohort_week, users_in_cohort, day_0_active, day_1_retention, ..., day_7_retention. Explain edge cases (users with multiple first events).

Sample Answer

Approach: assign each user to a weekly cohort by their first-ever event (DATE_TRUNC to week), then for each cohort count users and compute how many of those users were active on day 0..7 relative to their first event. Day N = had any event with event_time >= first_event + N days and < first_event + N+1 days. Use DISTINCT user counts so duplicates don’t inflate retention.

SQL (Postgres / BigQuery style):

sql

WITH first_event AS (
  -- determine each user's first event timestamp and cohort week
  SELECT
    user_id,
    MIN(event_time) AS first_event_time,
    DATE_TRUNC('week', MIN(event_time)) AS cohort_week
  FROM events
  GROUP BY user_id
),
events_rel AS (
  -- join original events to first_event to compute day offset
  SELECT
    f.user_id,
    f.cohort_week,
    f.first_event_time,
    e.event_time,
    FLOOR(EXTRACT(EPOCH FROM (e.event_time - f.first_event_time)) / 86400) AS day_offset
  FROM first_event f
  JOIN events e
    ON e.user_id = f.user_id
  -- keep events within 0..7 days (optional: extend if needed)
  WHERE e.event_time >= f.first_event_time
    AND e.event_time < f.first_event_time + INTERVAL '8 day'
),
cohort_agg AS (
  -- base cohort sizes
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS users_in_cohort
  FROM first_event
  GROUP BY cohort_week
)
SELECT
  c.cohort_week,
  c.users_in_cohort,
  -- day_0 is those with any event on day_offset = 0 (should equal users_in_cohort unless deduped)
  COALESCE(d0.day_users, 0) AS day_0_active,
  COALESCE(d1.day_users, 0) AS day_1_retention,
  COALESCE(d2.day_users, 0) AS day_2_retention,
  COALESCE(d3.day_users, 0) AS day_3_retention,
  COALESCE(d4.day_users, 0) AS day_4_retention,
  COALESCE(d5.day_users, 0) AS day_5_retention,
  COALESCE(d6.day_users, 0) AS day_6_retention,
  COALESCE(d7.day_users, 0) AS day_7_retention
FROM cohort_agg c
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 0 GROUP BY cohort_week
) d0 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 1 GROUP BY cohort_week
) d1 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 2 GROUP BY cohort_week
) d2 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 3 GROUP BY cohort_week
) d3 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 4 GROUP BY cohort_week
) d4 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 5 GROUP BY cohort_week
) d5 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 6 GROUP BY cohort_week
) d6 USING (cohort_week)
LEFT JOIN (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS day_users FROM events_rel WHERE day_offset = 7 GROUP BY cohort_week
) d7 USING (cohort_week)
ORDER BY cohort_week;

Key points and reasoning:- We anchor cohorts to the user's first event (MIN(event_time)) so every user belongs to exactly one cohort.- day_offset computed in full days (floor of seconds/86400) ensures events are bucketed into calendar-relative days since first event.- Use COUNT(DISTINCT user_id) to avoid double-counting multiple events per user on the same day.

Edge cases:- Multiple "first" events: MIN(event_time) picks the earliest timestamp. If two identical earliest timestamps exist, both are equivalent — user still in that cohort. If you need deterministic tie-breakers by event_name, include ROW_NUMBER() over (user_id ORDER BY event_time, event_name) and pick row_number = 1.- Timezone / truncation differences: ensure event_time is normalized to a consistent timezone before DATE_TRUNC.- Users with no subsequent events: retention days >0 will be zero for those users.- Very late event ingestion (backfilled events) can move a user's first_event earlier; consider using ingestion_time filters or snapshotting first_event at cohort calculation time.- Week boundary definition: DATE_TRUNC('week', ...) defaults may vary; clarify whether week starts on Monday or Sunday and adjust (e.g., use DATE_TRUNC('week', event_time, 'MONDAY') in some SQL dialects).

Alternatives:- Pivot with conditional aggregation instead of multiple joins: COUNT(DISTINCT CASE WHEN day_offset = 1 THEN user_id END) AS day_1_retention- For large datasets, compute events_rel only for day_offset <=7 to reduce data scanned; use pre-aggregated event-days table for performance.

EasyTechnical

67 practiced

Implement a conversion rate calculation in SQL. Given table user_events(user_id STRING, event_name STRING, event_time TIMESTAMP). Compute conversion_rate = unique users with event_name='purchase' divided by unique users with event_name='visit' over the last 30 days. Specify how you handle null user_id, duplicate events, and event_time boundaries.

Sample Answer

Approach: filter events to the last 30 days, exclude null user_id, de-duplicate at the user level (count unique users who had at least one event of that name), then compute ratio. Use COUNT(DISTINCT ...) for simplicity; for very large tables consider pre-aggregating or using approximate distinct functions.

sql

-- Standard SQL (e.g., BigQuery/Postgres)
WITH recent AS (
  SELECT user_id, event_name, event_time
  FROM user_events
  WHERE user_id IS NOT NULL
    AND event_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY   -- inclusive 30 days back
    AND event_time <  CURRENT_TIMESTAMP                       -- up to now, exclusive
)

SELECT
  visits.num_visitors,
  purchases.num_buyers,
  CASE
    WHEN visits.num_visitors = 0 THEN NULL                -- or 0.0 depending on product decision
    ELSE purchases.num_buyers::numeric / visits.num_visitors
  END AS conversion_rate
FROM
  (SELECT COUNT(DISTINCT user_id) AS num_visitors FROM recent WHERE event_name = 'visit') visits,
  (SELECT COUNT(DISTINCT user_id) AS num_buyers   FROM recent WHERE event_name = 'purchase') purchases;

Key notes:- Null user_id: excluded (can't attribute to a user).- Duplicate events: COUNT(DISTINCT user_id) ensures each user counted once per event type.- Time boundaries: used [now-30 days, now) to avoid double-counting future timestamps; adjust to >= and <= if inclusive of current second is desired. Consider timezones and storage timezone normalization.- Scaling: for very large tables, pre-aggregate daily user-level flags or use APPROX_COUNT_DISTINCT for speed.

Unlock Full Question Bank

Get access to hundreds of Metric Definition and Implementation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.