Intermediate interview questions in Analytics & Metrics, page 10
Calculate Daily Active Users (DAU) with SQL
This tests your ability to translate a business metric into a precise technical definition and query. A good answer defines "active," specifies the event data needed (user_id, timestamp, event_name), and uses COUNT(DISTINCT user_id).
Calculate Daily Active Users (DAU) with SQL
This tests product sense and SQL fundamentals. Define 'active' with a core product action, describe the event data needed, then write a COUNT(DISTINCT user_id) query. A red flag is writing SQL before defining the business logic for 'active'.
Find leading indicators of long-term churn
Cohort renewers vs churners, compare first-30-day engagement depth and breadth, validate correlations and check causality.
How would you find leading indicators for long-term churn?
Tests your ability to connect a lagging business KPI to leading product metrics. A good answer defines churned/retained cohorts, analyzes first 30-day engagement differences (e.g., feature adoption), and validates findings. A red flag is jumping to ML models.
Find leading indicators for long-term churn
This tests your ability to translate a business problem into a data investigation. A strong answer defines churned vs. retained cohorts, hypothesizes key early behaviors, and compares their frequency to find a leading indicator.
Reframe time series for a tree model
Lag and rolling-window features, calendar and cyclical encodings, then split chronologically to avoid leakage.
Transform a Time Series for a Gradient Boosting Model
Tests your ability to convert a sequential problem into a tabular one. A great answer covers creating lagged/rolling features and time-based features (e.g., day of week), and crucially, specifies a time-aware validation split.
Transform a time series for a supervised learning model?
This tests your ability to reframe a time series problem for tabular models. A great answer explains creating features from lags, rolling windows, and calendar data, then emphasizes using a time-aware validation split. A red flag is forgetting validation.
Design a multi-touch attribution model
Pick a model (time-decay, position-based, or data-driven Shapley), stitch touchpoints by user identity into ordered paths, then assign fractional credit.
Propose a multi-touch attribution model and its data pipeline
This tests your ability to choose a practical data model under real-world constraints. Propose a time-decay or position-based model, then describe the data pipeline: event collection, identity resolution, and aggregation. A red flag is ignoring signal loss.
Propose a multi-touch attribution model and its data pipeline
Tests your grasp of attribution models and their data engineering needs. Propose a rule-based model (e.g., time-decay), outline the data pipeline for it, and acknowledge privacy-driven signal loss. A red flag is ignoring the challenge of identity resolution.
Implement CDC from OLTP to warehouse
Contrast log-based CDC with query-based timestamp polling, cover deletes and load on source, then pick log-based for minimal impact.
Implement CDC from an OLTP database to a data warehouse
This tests your grasp of production system trade-offs. A good answer compares log-based and trigger-based CDC, focusing on source impact and data fidelity, then recommends log-based for its low overhead.
How would you implement Change Data Capture (CDC)?
Tests your grasp of data replication trade-offs. A great answer compares log-based CDC (low impact, complete) with query-based methods (higher impact, misses deletes), and recommends log-based CDC for its minimal production impact.
Define a consistent day across timezones
Store events in UTC, capture the local/source timezone, then convert to a single reporting timezone at query time.
How do you handle timezones for a global daily sales report?
This tests your ability to translate ambiguous business needs (a "day") into a robust data model. First, clarify the business definition of a day. Then, store all event times in UTC and convert to the target timezone at query time for reporting.
How do you handle timezones for a daily global sales report?
This tests your understanding of time data modeling and business requirements. A good answer stores events in UTC with a timezone identifier, then converts to the business's chosen 'day' at query time. A red flag is storing local time without context.
Interpret a p-value in an A/B test
Define it as P(data this extreme | null true), interpret 0.03 against a 0.05 threshold, and state what it is NOT.
What is a p-value, and what does 0.03 practically mean?
This tests your ability to translate stats into business decisions. A great answer defines p-value, compares 0.03 to the standard 0.05 threshold to reject the null hypothesis, and recommends shipping.
What does a p-value of 0.03 mean in an A/B test?
This tests your practical grasp of statistical significance. A good answer defines p-value (probability of the result if the null hypothesis is true), explains that p=0.03 is significant vs. alpha=0.05, and concludes you can reject the null.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles