Business Strategy and Alignment Questions

Covers how projects, technical decisions, metrics, and operational governance are connected to an organization's strategic objectives. Candidates should demonstrate the ability to translate high level strategy into executable plans, prioritize initiatives that create measurable business value such as revenue, cost reduction, customer experience, or time to market, and recognize consequences of misalignment. Includes metric governance and dashboard ownership processes, defining metric definitions, assigning owners, managing technical debt in reporting systems, and evolving measurement as priorities change. Interviewers assess business literacy, use of business language when explaining technical trade offs, stakeholder communication, and examples where alignment succeeded or where misalignment created problems.

MediumBehavioral

47 practiced

As a data analyst, how would you help a non-technical executive interpret a complex funnel analysis that suggests a trade-off between increasing conversion and degrading customer satisfaction? Describe how you'd present options, use visual aids, quantify the trade-off, and recommend an approach aligned to strategy.

EasyTechnical

44 practiced

Write a SQL query (Postgres flavor) to compute monthly active users (MAU) over the last 6 months. Given table `events(user_id bigint, event_time timestamp, event_type text, is_bot boolean)` compute distinct users per calendar month excluding bots. Return columns: month (YYYY-MM), mau. Provide a concise, production-ready query and mention one caveat about the metric definition.

Sample Answer

Here's a concise, production-ready Postgres query that computes MAU (distinct non-bot users) for each calendar month over the last 6 months (including the current month):

sql

WITH months AS (
  SELECT generate_series(
    date_trunc('month', current_date) - interval '5 months',
    date_trunc('month', current_date),
    interval '1 month'
  ) AS month_start
),
mau_per_month AS (
  SELECT
    date_trunc('month', event_time) AS month_start,
    COUNT(DISTINCT user_id) AS mau
  FROM events
  WHERE NOT is_bot
    AND event_time >= date_trunc('month', current_date) - interval '5 months'
    AND event_time < date_trunc('month', current_date) + interval '1 month'
  GROUP BY 1
)
SELECT
  to_char(m.month_start, 'YYYY-MM') AS month,
  COALESCE(p.mau, 0) AS mau
FROM months m
LEFT JOIN mau_per_month p USING (month_start)
ORDER BY m.month_start;

Caveat: MAU here counts any user with >=1 event in the month; results depend on which event_types you include and on event_time timezone normalization—be explicit about event types/timezone to avoid overstating activity.

MediumTechnical

46 practiced

Design a small set of unit tests for a canonical SQL metric 'Net Revenue' that ensure correctness after future changes. Describe three test cases (with input dataset examples and expected outputs) that catch common errors such as double-counting, missing refunds, and currency conversion issues.

MediumTechnical

53 practiced

You notice a sudden 25% drop in conversion on a key dashboard but product insists nothing changed in the funnel. Outline a pragmatic investigation plan to identify whether the drop is real, an instrumentation problem, a data pipeline issue, or a true product regression. List at least six investigation steps and the order you'd run them.

Sample Answer

Start by clarifying scope and impact, then run fast, evidence-focused checks from lowest-effort/high-confidence to deeper pipeline checks:

1) Confirm metric definition & time window- Verify the dashboard’s conversion definition, cohort filters, timezone, and the exact timestamps for the 25% drop. Small config changes or window shifts often explain anomalies.

2) Reproduce the drop from raw queries- Run the canonical SQL query against the source events table(s) for the same period + a prior baseline. Example: compare COUNT(conversions)/COUNT(sessions) day-over-day. If SQL shows no drop, dashboard rendering may be wrong.

3) Check upstream event counts and traffic- Query raw event volumes (pageviews, form_submits, conversion events) and unique user counts. Look for sudden decreases in event counts, increases in nulls, or spikes in error events.

4) Validate instrumentation integrity- Sample recent raw events for required fields (user_id, session_id, event_type, timestamps). Look for schema drift, missing event types, or changed event names (e.g., "purchase" -> "order_complete").

5) Inspect data pipeline health and logs- Review ETL job runs, lag, failed batches, recent deploys to pipeline code, and schema migration logs. Check whether transformations (joins, deduping) started filtering more rows.

6) Cross-check alternative data sources- Compare with third-party analytics (GA4), backend logs, payment gateway, or A/B test logs. If alternative sources show the same drop, it's likely real product/regression; if only dashboarded dataset shows it, it's data issue.

7) Segment the drop and run quick root-cause slices- Break down by device, browser, geo, channel, user cohort, and landing page. A localized drop (e.g., mobile iOS) suggests a product bug; broad drop suggests business change.

8) Coordinate with product/eng and rollback investigation- Share findings with product and engineering: include SQL results, sample bad events, pipeline error logs, and segments affected. If evidence points to a product regression, prioritize a hotfix; if instrumentation, fix tracking and backfill.

Rationale: this order goes from cheap/high-signal checks (definitions, raw queries) to costlier investigations (pipeline logs, cross-system reconciliation), minimizing false alarms and speeding resolution.

MediumSystem Design

38 practiced

You are building a dashboard to track Time-to-Market (TTM) for features across multiple product teams. Requirements: measure TTM per feature from 'PR opened' to 'production', support filters by team and priority, show distribution, and surface outliers. Outline the dataset design, sample fields you would store, key calculated metrics, and a recommended visualization set for stakeholders.

Sample Answer

Requirements & constraints:- Measure TTM per feature from "PR opened" to "production" (single ground-truth interval).- Filters: team, priority, timeframe, product, feature type.- Show distribution and surface outliers; support cohort comparisons and drill-to-row.- Data freshness: near-real-time (hourly) or daily depends on stakeholders.- Handle reopens, multiple PRs, hotfixes, and merges across repos.

Dataset design (event + feature-centric):- Feature table (dimensions, one row per feature id): - feature_id (PK) - title, description - product, team_id - priority (P0/P1/P2), feature_type - created_at (spec/req created) - planned_release (optional)- PR events table (time-series): - pr_id, feature_id (FK) - pr_opened_at, pr_merged_at, author, repo - pr_status- Deployment events table: - deploy_id, feature_id (FK) - environment (staging/prod), deploy_started_at, deploy_succeeded_at, version, pipeline_id- Audit / state-change table: - feature_id, event_type (e.g., PR_OPEN, PR_MERGE, DEPLOY_PROD), timestamp, metadata

Key derived fields / preprocessing rules:- ttm_start = earliest pr_opened_at linked to feature (or first PR after spec approved)- ttm_end = first deploy_succeeded_at to production that contains the feature (resolve by changelog or release mapping)- ttm_days = business_days(ttm_end - ttm_start) and ttm_hours- ttm_lead_components: review_time (pr_merged_at - pr_opened_at), qa_time (deploy_to_prod - staging_deploy), pipeline_time- handle multiple PRs: use min(pr_opened_at) and match deploys via release notes or commit hashes; flag ambiguous matches

Key metrics to expose:- Median, mean, 75th/90th/95th percentile TTM by team/priority/product- Distribution histogram / kernel density of TTM- Outlier rule: > Q3 + 1.5*IQR or > 95th percentile; surface top N outliers with root-cause fields- SLA compliance rate: % features meeting target (e.g., <=14 days)- Trend: rolling 4-week median TTM, throughput (features deployed per period)- Breakdown of time components (review / QA / pipeline) as % of TTM

Recommended visualizations:- KPI strip: median TTM, 95th percentile, SLA compliance, throughput (filters apply)- Box-and-whisker by team and by priority (compact comparison)- Histogram + percentile lines with selectable bins for distribution; overlay previous period- Time-series: rolling median and 95th percentile with volume bars- Stacked bar or waterfall per feature showing component times (review/qa/deploy)- Outliers table: sortable list with feature_id, team, priority, ttm_days, component breakdown, links to PR/deploy- Cohort view: cohort retention-like panel to see TTM for features launched in same sprint/quarter- Drill path: click on a point/box to open feature-level timeline (PR & deploy events) for root-cause

Implementation notes / best practices:- Build transforms in SQL/ETL to produce single feature-level materialized view for dashboard performance.- Maintain lineage: store commit/release mapping to ensure accurate deploy attribution.- Expose confidence/flags for ambiguous matches and let analysts override mappings.- Add annotations for process changes (CI changes, holidays) to interpret shifts.

Unlock Full Question Bank

Get access to hundreds of Business Strategy and Alignment interview questions and detailed answers.

Join thousands of developers preparing for their dream job.