MediumTechnical

123 practiced

An A/B test on checkout shows: control conversion = 12.0% (n=50,000), variant conversion = 12.9% (n=50,000), p-value = 0.06. As the data analyst, explain how you would interpret these results, whether you would recommend shipping the variant, and list additional analyses (sample-ratio-test, secondary metrics, segment consistency) you would perform before making a decision.

Sample Answer

Interpretation:The variant shows a +0.9 percentage-point lift (12.0% → 12.9%), a relative increase of 7.5%. However the p-value = 0.06 is above the conventional 0.05 threshold, so we fail to reject the null at α=0.05. That means the observed lift could plausibly be due to chance given our data, though it is close to significance and practically meaningful.

Recommendation:I would not automatically ship the variant based solely on this p-value, but I also would not dismiss it. Because the effect size is business-relevant and p≈0.06 (marginal), I’d recommend further checks and contextual decision-making (risk tolerance, expected revenue impact, cost of rollout). Possible actions: extend the experiment to collect more samples, or run a pre-specified sequential test / Bayesian analysis to quantify probability of positive lift.

Additional analyses to run before deciding:- Sample Ratio Test (SRT): verify randomization and that observed group sizes match expected. Rejecting SRT suggests data quality/assignment issues.- Secondary metrics: check basket size, revenue per session, checkout abandonment, time-to-purchase, errors, returns. Ensure uplift in conversion isn’t offset by negative downstream effects.- Segment consistency: analyze lift across device, browser, geography, new vs. returning users, traffic sources. Look for heterogeneity or a small segment driving the effect.- Duration and power review: re-evaluate power calculations given observed variance; estimate additional sample needed to achieve 80–90% power.- Temporal stability and novelty bias: test if effect changes over time or fades.- Safety checks: look for instrumentation bugs, bots, or spikes in QA traffic.- Bayesian / lift probability: compute the probability that variant is better than control and expected value of shipping.

Decision rule:If SRT passes, secondary metrics are neutral or positive, segments show consistent or targetable gains, and business impact justifies risk, proceed with rollout (possibly gradual). If data quality or negative secondary effects emerge, halt and investigate.

EasyTechnical

72 practiced

You are reviewing a monthly revenue dashboard that shows two charts over the past 24 months: (1) a line chart of 'Revenue' and (2) a line chart of 'Advertising Spend'. In the last three months revenue has declined 12% while ad spend increased 20%. As a data analyst, describe step-by-step the checks you would run to determine whether this is a real business issue, a data quality problem, or normal variation. List specific queries, Excel formulas, or quick Tableau/Power BI checks you would perform and which metrics you'd compute to quantify the change (MoM, YoY, cohort breakdowns, channel-level revenue, sample sizes).

Sample Answer

Step 1 — sanity checks / context- Confirm date ranges & granularity (end-of-month vs daily). Verify ETL ran for recent months.- Quick checks: total rows for last 3 months vs prior months to detect ingestion drops.

SQL examples:

sql

-- count events / transactions per month
SELECT DATE_TRUNC('month', order_date) month,
       COUNT(*) orders, SUM(revenue) revenue, SUM(ad_spend) ad_spend
FROM orders
WHERE order_date >= current_date - interval '24 months'
GROUP BY 1
ORDER BY 1;

Step 2 — quantify changes- Compute MoM and YoY percentages and absolute deltas.Excel formulas for month in A2 (revenue):- MoM%: =(A2-A3)/A3- YoY% (same month last year): =(A2 - INDEX(range, MATCH(month-12))) / INDEX(...)

SQL for MoM/YoY:

sql

-- rolling change
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) as rev_delta_mom,
       (revenue / LAG(revenue,12) OVER (ORDER BY month) - 1) as rev_pct_yoy
FROM monthly;

Step 3 — attribute to channels / segments- Break revenue & ad spend by channel, campaign, country, product category, device.- Compute conversion rates: sessions -> orders, CTR, CPC if available.SQL:

sql

SELECT month, channel, SUM(revenue) revenue, SUM(ad_spend) ad_spend,
       SUM(orders) orders, SUM(sessions) sessions,
       (SUM(orders)::float / NULLIF(SUM(sessions),0)) conv_rate
FROM channel_monthly
GROUP BY month, channel;

Step 4 — data quality checks- Compare source systems (ad platform spend vs ETL table): join on month & campaign and compute diff%.- Check for late-arriving transactions: compare daily backfill counts.- Verify currency and attribution windows (last-click vs multi-touch) changes.

Step 5 — statistical / normal variation- Calculate z-score of recent 3-month change vs 24-month monthly distribution: z = (Δ - mean(Δ))/sd(Δ). If |z|>2, likely significant.- Bootstrap cohorts: cohort revenue per acquisition month to spot cohort deterioration.

Step 6 — drill into conversion funnel & sample sizes- Metrics: impressions, clicks, CPC, CTR, sessions, add-to-cart, checkout conversion, average order value (AOV).- Check sample sizes: if sessions or orders fell, revenue decline may be noise.

Quick dashboard checks (Tableau/Power BI)- Add parameter for month window; display channel stacked bars and KPI cards for MoM/YoY.- Use trend line with confidence bands; add small-multiples by channel.- Filter to top 10 campaigns by spend and show spend vs revenue scatter.

Interpretation logic- If ad_spend ↑ but channel-level ROAS ↓ and conversion rate/AOV dropped -> business issue (campaign targeting/landing pages/pricing).- If ad_spend in data disagrees with ad platform -> data quality.- If changes within historical volatility (z ~<2) -> normal variation; monitor.

Recommended next steps- Reconcile spend with ad platform, run post-click funnel analysis, A/B test landing pages if conversion fell, and alert product/marketing with actionable metrics and top 3 suspected causes.

EasyTechnical

83 practiced

A scatterplot on your dashboard shows a strong positive correlation between product page views and support tickets. Explain why correlation does not imply causation, propose at least three plausible causal and confounding scenarios, and design two analyses or experiments you could run to test whether product views cause support tickets or a confounder exists.

MediumTechnical

81 practiced

You're designing an operations dashboard for two audiences: executives (top-level KPIs) and support engineers (drillable diagnostics). Propose a dashboard structure that balances both audiences: layout, visuals, interactions (filters/drill paths), data refresh cadence for each section, and access controls. Provide reasoning for widget choices and one mock layout example in words.

Sample Answer

Approach: split the dashboard into two linked tiers — an Executive Summary pane (high-level, read-only KPIs) and an Operational Diagnostics pane (interactive, drillable for engineers). Provide fast refresh for ops, slower for execs to reduce noise.

Layout & visuals:- Top row (Exec Summary, full width): 4–6 KPI cards (Availability %, MTTR, Incidents/day, Customer Impact %). Big numbers, sparkline last 30 days, color-coded status (green/amber/red).- Middle row (Trends & Alerts): time-series charts (incidents by severity, latency P95/P99) and an alerts feed. Use area/line charts with annotations for deploys.- Bottom row (Diagnostics, left) + Side panel (Filters & Logs, right): table of open incidents, service map (interactive topology), error-rate heatmap, and raw logs/snippets viewer. Each diagnostic widget links back to the exec KPI that triggered it.

Interactions & drill paths:- Click KPI card → opens filtered Diagnostics focused on that metric (e.g., MTTR → list of incidents last 24h, grouped by service).- Hover a point in time-series → shows events/deploys and allows "Open in Log Explorer".- Global filters: time range, environment (prod/stage), region, service. Persisted per user.- Pre-built drill buttons: “Root Cause View”, “Customer Impacted”, “Rollback Candidates”.

Data refresh cadence:- Executive KPIs: 5–15 minutes, aggregated nightly for trend comparisons.- Time-series & alerts: 1–5 minutes (near-real-time).- Raw logs/trace links: streaming / on-demand queries with user-triggered refresh to avoid costs.

Access controls:- Role-based views: Executives see only KPI cards and trend summaries (no raw logs or PII). Support engineers have full access to diagnostics and logs.- Row-level security for customer data; audit logging for sensitive access.- Temporary escalation tokens for execs to grant engineers short-lived access during incidents.

Reasoning for widget choices:- KPI cards give quick signal for decision-makers; sparklines add context without overload.- Time-series + annotations help correlate deploys/events with metric shifts.- Interactive service map and error heatmap let engineers quickly localize issues.- Raw logs/trace links ensure one-click investigation flow from summary to root cause.

Mock layout (in words):Top: KPI bar with 6 cards (Availability, MTTR, Incidents/day, Error Rate, Latency P95, Customer Impact). Middle-left: incidents/time-series and alerts feed; middle-right: deployment timeline and SLA trend. Bottom-left: interactive service map and heatmap; bottom-right: incidents table (click to expand logs) and log viewer. Right-most slim panel: persistent filters and “Run Diagnostics” controls.

MediumTechnical

59 practiced

Given a table users(user_id, email, created_at, updated_at), write an SQL query to deduplicate users by email keeping the most recent record (highest updated_at). Also outline how you would surface duplicates to stakeholders, how you would fix foreign keys referencing old user_id values, and how to prevent duplicates at ingestion.

Sample Answer

Approach: identify duplicate emails, keep the row with the max(updated_at) per email, delete others safely (or move them to an archive). First preview duplicates, then delete in a transaction using a CTE.

SQL to preview duplicates and ids to remove:

sql

-- Preview duplicates and candidate to keep
WITH ranked AS (
  SELECT user_id, email, created_at, updated_at,
         ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY updated_at DESC, user_id DESC) AS rn,
         COUNT(*) OVER (PARTITION BY lower(email)) AS cnt
  FROM users
)
SELECT *
FROM ranked
WHERE cnt > 1
ORDER BY email, rn;

SQL to archive then delete duplicates (keep rn = 1):

sql

BEGIN;

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY updated_at DESC, user_id DESC) AS rn
  FROM users
),
to_delete AS (
  SELECT user_id FROM ranked WHERE rn > 1
)
-- archive rows
INSERT INTO users_archive (user_id, email, created_at, updated_at, archived_at)
SELECT user_id, email, created_at, updated_at, now() FROM users WHERE user_id IN (SELECT user_id FROM to_delete);

-- delete duplicates
DELETE FROM users WHERE user_id IN (SELECT user_id FROM to_delete);

COMMIT;

Fixing foreign keys referencing old user_id:- Before delete, build a mapping from old_user_id -> kept_user_id (rn>1 → rn=1 for same lower(email)). Use that mapping to update referencing tables.Example update pattern:

sql

WITH mapping AS (
  SELECT d.user_id AS old_id, k.user_id AS keep_id
  FROM (
    SELECT user_id, lower(email) AS e, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY updated_at DESC, user_id DESC) AS rn
    FROM users
  ) k
  JOIN (
    SELECT user_id, lower(email) AS e FROM users WHERE 1=1
  ) d ON d.e = k.e AND k.rn = 1 AND d.user_id <> k.user_id
)
UPDATE orders o
SET user_id = m.keep_id
FROM mapping m
WHERE o.user_id = m.old_id;

- Do this for every FK table; run counts to validate no orphaned refs.

Surfacing duplicates to stakeholders:- Create a dashboard or scheduled report listing duplicates with counts, affected analyses, sample rows and business impact (e.g., revenue, MAUs).- Email stakeholders with a summary, number of affected records, example emails, and proposed remediation window.- Provide risk assessment (which systems reference users) and rollback plan.

Prevention at ingestion:- Enforce a unique constraint on normalized email (e.g., lower(email)) after migration, possibly use a separate canonical_user table.- Implement dedupe logic in ETL: lookup existing user by email (normalized), merge attributes, upsert user row, and emit warnings for conflicting data.- Add validation in upstream systems: verify email format, require email verification, and limit creation of multiple accounts per identity provider.- Add monitoring/alerts for spikes in duplicate creation and periodic data quality checks.

Data Interpretation & Dashboard Literacy Questions

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Unlock Full Question Bank