Analytics
494 bites tagged Analytics — interview questions with model answers, and 60-second explainers.
Design a real-time mobile analytics pipeline
Tests decoupling high-volume ingestion from low-latency querying. Strong designs use an event broker, a stream processor for windowed aggregates, and an OLAP database for sub-second dashboards.
Explain cohort retention and write a pseudo-query for May signups
Tests cohort retention vs aggregate DAU and SQL self-joins for Week 1, 2, and 4 retention from May signups. Strong answers define cohorts by signup date, use datediff, and left-join activity. Red flag: using calendar week instead of relative signup date.
Describe client-side events and properties to track Export to CSV usage
This tests telemetry design for async actions. A strong answer defines three custom events—click, success, failure—with properties like location, file_size, error_code, and user_id, fired at the right lifecycle moments.
Describe dbt's role and how it differs from traditional ETL
Position dbt as ELT's T with lineage, tests, docs; contrast with Python ETL using external compute and Airflow only scheduling tasks. Declarative warehouse transformation vs imperative ETL.
Describe tracking a user event end-to-end from frontend to BI tool
Mention frontend instrumentation, routing, ingestion, warehouse transforms, and BI querying. Grasp of data pipeline layers from product to warehouse. Stopping at the backend or confusing analytics with application logs.
How would you design UTM capture and attribution persistence?
Capture UTMs on landing, store in a first-party cookie with TTL, attach to events, and persist on conversion. First-touch attribution from URL to conversion. Last-click without session stitching or ignoring ITP limits.
What event and data payload track Add to Cart actions?
This tests GA4 ecommerce schema design. Fire add_to_cart with items array containing item_id, price, currency, quantity; include user_id, user_segment, session_id, and timestamp. Red flag: generic button_click with DOM selectors instead of semantic data.
Describe two methods for generating prediction intervals or probabilistic forecasts
Tests uncertainty quantification for risk-adjusted decisions. Strong answers: (1) parametric intervals via forecast error variance and normal multipliers, (2) bootstrap residual resampling for empirical percentiles.
Describe key components for EDA on three years of daily user sign-ups
This tests time-series decomposition intuition. A strong answer covers trend, seasonality, and noise via plots, autocorrelation, and calendar effects, plus checks for missing days and outliers. Red flag: jumping to forecast models before validating structure.
Determine if a 10% DAU drop is statistically significant
Tests signal vs noise in stable metrics. Good answers define a null hypothesis, compute a test statistic from historical variance, compare to a critical value at set alpha, and check seasonality. Red flag: calling a large drop real without baseline variance.
Describe data architecture for fast multi-dimensional slicing and pre-computation trade-offs
Tests analytical architecture trade-offs between latency and cost. Strong answers propose a hybrid pipeline: columnar warehouse for flexible exploration plus pre-aggregated OLAP cubes for sub-second dashboards.
Write SQL to generate a monthly cohort retention table from raw events
Tests window functions and date truncation for cohort analysis. A strong answer finds each user's first month, counts returning users per period, and divides by cohort size. Aggregating all users without isolating acquisition month hides new-user churn.
What user segments do you check first after a 10% DAU drop?
Validate by time, platform, and geography; then slice by new vs returning, channel, and feature usage to isolate the bleeding cohort. Structured triage of a metric drop through user segmentation.
Explain pre-attentive attributes and give three examples
This tests whether you know preattentive attributes are decoded in <200ms to guide attention freely. Name three such as color hue, size, and motion; then encode one variable in a dense scatter plot so targets pop out. Never call this decoration or color all.
How would you structure a user engagement dashboard for PMs?
Tests narrative sequencing of metrics across abstraction layers. Strong answer: DAU headline for health, retention cohorts for pattern diagnosis, feature adoption funnels for root-cause drill-down.
Compare five companies' market share: bar or line chart?
This tests categorical vs. temporal encoding. Pick a bar chart because companies are discrete categories, not a time series; line charts falsely imply sequence or trend. Calling a line chart acceptable is a red flag.
When is a pie or donut chart appropriate?
Tests judgment of part-to-whole encoding. Answer: use for few categories with clear dominance, cite a share scenario like device traffic, and name angle-comparison difficulty and 3D distortion as pitfalls.
How do you manage event schema evolution without breaking reports?
Backward-compatible serialization, nullable new fields, raw versus modeled layers, versioned schemas, and consumer alerts. Contract-change discipline across ingestion, warehouse, and BI.
What are the challenges of grouping by a high-cardinality dimension?
Tests columnar storage internals and query engine scalability. A strong answer covers memory pressure from giant hash tables, destroyed compression ratios, and massive result-set overhead.
How do duplicate events bias COUNT(*) and daily login reports?
Tests idempotency in streaming analytics. COUNT(*) overcounts; fix with unique event ID dedup via idempotent writes or COUNT(DISTINCT id), plus daily partition reconciliation. Red flag: SELECT DISTINCT * without a stable key or no reporting safeguard.
How would you build a Customer golden record across fragmented systems?
This tests master data management discipline for distributed, conflicting records. A strong response covers identity resolution, survivorship rules, merge architecture, lineage, and feedback loops.
Design a CDC pipeline that handles schema evolution gracefully
Tests designing resilient CDC pipelines against schema drift. A strong answer covers schema registries with versioning, backward-compatible serialization, and automated compatibility checks.
How would you implement data lineage for microservices analytics?
Tests end-to-end provenance tracking, not just docs. Strong answers cover automated metadata capture at service boundaries, a central catalog such as DataHub or OpenLineage, and column-level tracing.
What is a data schema and why enforce it at ingestion?
Tests schemas as contracts and ingestion validation as a quality gate. Strong answers cite blueprints with constraints, fail-fast ingestion catching type errors upstream, and downstream trust. Red flag: treating schemas as optional docs affecting only storage.
Get Analytics bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.