Intermediate everything in Product Management, page 13
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.
Average latency up 50ms but p99 flat: diagnose the discrepancy
Tests if you know mean reflects full distribution while p99 is a threshold. Strong answers hypothesize body shift like cache misses or traffic mix changes, and demand histograms and segmentation by endpoint. Red flag: blaming outliers, which would raise p99.
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.
Stakeholder claims feature usage drives retention. How do you validate?
Probe confounders, reverse causality, selection bias; propose diff-in-diff or propensity scores; plot lagged usage vs retention.
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.
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.
Why is star schema preferred over 3NF for analytics?
Tests your grasp of the read-performance trade-off in analytical schemas. A great answer names fact and dimension tables, emphasizes fewer joins for aggregations, and cites simpler SQL and faster query plans.
Slow dashboard querying a large fact table: first three checks?
This tests systematic diagnosis of fact-table query latency. A strong answer checks the execution plan and indexing, evaluates partitioning and data model fit, and inspects caching or pre-aggregation.
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 data partitioning in a cloud data warehouse?
Tests physical data layout and cost/performance tradeoffs. Strong answers define time-based or integer-range partitioning, explain partition pruning avoids full scans, and warn against high-cardinality keys.
How would you handle late-arriving data in a streaming analytics pipeline?
Tests understanding of event-time processing, watermarks, and windowing for correctness. A strong answer covers watermarks with allowed lateness, event-time triggers, and idempotent updates to sinks.
Describe star and snowflake schemas and their trade-offs
Star schemas flatten dimensions for fast joins; snowflake schemas normalize hierarchies to reduce redundancy but add joins.
How do you approach user identity stitching across devices?
Tests cross-channel identity resolution design. Strong answers capture a persistent anonymous ID per device, rekey to a person ID at login via field-based or graph-based stitching, and respect consent.
Trade-offs: third-party analytics SDK versus in-house pipeline
This tests strategic build-versus-buy judgment for data infrastructure. Strong answers weigh time-to-market, maintenance burden, data sovereignty, and compliance against core product focus.
Conversion metric dropped suddenly with no recent deployments; debug instrumentation causes
Distinguishing real regressions from telemetry pipeline failures. Segment by device, channel, and geography to spot uniform loss signaling a tagging break; verify vendor delays and sampling; check for consent or ad-blocker shifts.
How do you track page views in a Single Page Application?
This tests SPA analytics beyond classic page loads. A strong answer covers History API pushState and popstate events, framework router hooks like useEffect or afterEach, and beaconing views. A red flag is relying only on window.load or polling URL changes.
How do you diagnose why a new feature's adoption is flat?
Tests structured analytics thinking across the adoption funnel. A strong answer maps discovery to habituation, segments cohorts, pairs behavior with feedback, and validates via experiments. Red flag: blaming UI without proving users know the feature exists.
Pitfalls of using conversion rate as a checkout North Star?
Tests if you know over-optimizing conversion can degrade revenue quality or trust. Strong answers cite lower AOV or fraud risks, then list guardrails like refund rate, lifetime value, and checkout errors. Red flag: insisting conversion is the sole metric.
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