A feed's commenting rate falls from 42% to 26%. The first tempting answer is that a ranking change made the product worse. The first useful answer is a metric contract and a query that can tell a behavior change from a change in who is using the product.

This guide focuses on Meta Data Scientist, Product Analytics preparation: SQL, metrics, experimentation, quantitative judgment and communication. Research or modeling-heavy roles may need a different preparation mix. The case below is original and fictional; its counts and decision limits are neither Instagram telemetry nor Meta's private interview rubric.

For a related Meta-tagged drill, try Evaluating a 15 Percent Reduction in Post-card Height. It asks about feed layout, engagement, viewability and geographic differences. The following worked case supplies its own data and narrower decision, so you can test the reasoning rather than memorize a framework.

05Prepare for the role, then confirm the actual loop

For a Product Analytics role, prepare to translate an ambiguous product question into a measurable decision, execute the analysis and explain its limits to a product manager or engineer. Practice joins, conditional aggregates, CTEs and window functions, alongside inference, experiment design and project discussions. If the posting emphasizes modeling, deepen evaluation and model-related work accordingly.

Use the current recruiter packet for interview count, duration, language, medium and tool permissions. A reported screen followed by multiple analytics conversations is useful as a mock format, not a guaranteed schedule. The question-bank category counts on this page describe PracHub's records; they do not measure Meta's hiring weights or your probability of receiving a particular question.

Keep four preparation artifacts: a metric contract, a query with grain checks, an experiment plan with a decision rule, and a true story in which your analysis influenced a decision. This set helps you demonstrate both execution and judgment without claiming a universal interview sequence.

06Case 1: a lower aggregate rate with unchanged cohort behavior

Imagine a feed product with two seven-day UTC windows: September 14–21 and September 21–28, 2026. Windows are half open: include the start and exclude the end. Analyze each user once per window. Eligibility is a completed upstream list of human accounts that opened the feed during that window; it excludes known test and bot accounts by a fixed rule.

The primary descriptive metric is the fraction of these eligible users with at least one server-accepted feed comment during the same window. It is not comments per impression, comments per author or average comments among commenters. Users with zero comments remain in the denominator. Repeated delivery of an event and multiple comments by the same user do not increase this binary outcome.

Define the cohort from account tenure at each window's start: established or new, using one fixed cutoff. This is a population comparison, not a paired analysis of the same users. All supplied totals below are synthetic.

Before: (4,000 + 200)/10,000 = 42%. After: (2,000 + 600)/10,000 = 26%. The drop is 16 percentage points, or 38.1% relative to the earlier rate. Neither cohort's supplied rate falls. The share of established users changes from 80% to 40%, and the newer users have the lower rate in both windows.

Holding the earlier weights fixed gives 0.8 × 50% + 0.2 × 10% = 42% for the later window too. That calculation attributes this synthetic aggregate change to the supplied two-cohort mix. It does not establish why the population shifted, whether unmeasured subgroups changed, or whether a launch caused anything.

07Complete SQL: preserve the denominator and prevent join inflation

eligible has one row per user/window and a primary key enforcing that grain. events contains delivery rows, so event_id can repeat. All timestamps use the same fixed-width UTC representation. A production warehouse needs documented timestamp types, lateness rules and consistent transformations; string ordering is appropriate only for the stated local format.

The schema and query below run in SQLite. They operate on event-level input, not precomputed rates:

CREATE TABLE periods (
  start_utc TEXT PRIMARY KEY, end_utc TEXT NOT NULL
);
CREATE TABLE eligible (
  start_utc TEXT NOT NULL, user_id INTEGER NOT NULL,
  cohort TEXT NOT NULL CHECK(cohort IN ('established','new')),
  PRIMARY KEY(start_utc,user_id)
);
CREATE TABLE events (
  event_id TEXT NOT NULL, user_id INTEGER NOT NULL,
  occurred_utc TEXT NOT NULL, surface TEXT NOT NULL,
  kind TEXT NOT NULL, accepted INTEGER NOT NULL
);
WITH commenters AS (
  SELECT DISTINCT p.start_utc, e.user_id
  FROM periods AS p
  JOIN events AS e
    ON e.occurred_utc >= p.start_utc
   AND e.occurred_utc < p.end_utc
  WHERE e.surface = 'feed'
    AND e.kind = 'comment_success'
    AND e.accepted = 1
), cohort_counts AS (
  SELECT u.start_utc, u.cohort,
         COUNT(*) AS eligible_users,
         COUNT(c.user_id) AS commenting_users
  FROM eligible AS u
  LEFT JOIN commenters AS c
    ON c.start_utc = u.start_utc AND c.user_id = u.user_id
  GROUP BY u.start_utc, u.cohort
)
SELECT start_utc, cohort, eligible_users, commenting_users,
       1.0 * commenting_users / eligible_users AS commenter_rate
FROM cohort_counts
ORDER BY start_utc, cohort;

The commenters CTE reduces the event relation to one user/window. The left join then keeps eligible noncommenters. Because c.user_id is non-null only for a match, COUNT(c.user_id) counts commenters while COUNT(*) counts all eligible users. Starting with an inner join would remove zeros and answer a different question.

No DISTINCT is needed after the final join if the declared grains hold. Adding it blindly can hide a duplicate eligibility bug. Inspect the row count after each join and reject or repair data that violates the contract. Do not choose an arbitrary duplicate cohort row just to make the result run.

Small rows that test the contract

In a separate tiny fixture, four eligible users have the same cohort. User 1 has two delivered copies of an accepted comment at the exact start. User 2 comments one second before the end. User 3 has an event at the exact end and a failed submission inside the window. User 4 has no events. The result must be four eligible, two commenting, or 50%.

A noneligible user with a valid event must not change either total. A success on another surface must not qualify. Adding another qualifying comment for user 1 must not change the binary metric. These checks catch more useful errors than verifying that a query contains the word GROUP BY.

The full local fixture generates 20,000 eligible user/window rows matching the cohort table, alongside duplicate deliveries and out-of-window records. Local verification passed 322 checks, executing this query against the full fixture, the tiny boundary case and 50 seeded event fixtures compared with independently counted user sets. This is synthetic SQLite execution, not a query against a Meta warehouse.

08Diagnosis: recommend an action without inventing a cause

First validate instrumentation and completeness: compare accepted-comment logs with the reporting table, align UTC windows, inspect late arrivals, and check client versions and the eligibility job. A one-day missing partition can look like a product effect. A successful event recorded under a new name can disappear from a stale query.

Then present the mix calculation and prioritize explanations for the population change. Did acquisition bring more new users? Did an established cohort stop opening the feed? Did a classification cutoff or bot exclusion change? Segment by country, device, platform and acquisition source to distinguish those hypotheses, while preserving a stable comparison contract.

The immediate recommendation is to hold the claim that “the launch reduced commenting” and investigate the population shift. If an independent reliability signal shows the new client failing to submit comments, contain that problem while investigating. The table does not justify ignoring a real incident simply because two coarse rates stayed flat.

A concise readout is: “The aggregate decline is fully reproduced by the supplied cohort weights; established and new rates are unchanged. I would verify the eligibility pipeline and explain the mix shift before recommending a ranking rollback. A decline within comparable users, or a confirmed logging/submission defect, would change that recommendation.”

09Case 2: experiment lift passes, but user-experience harm blocks launch

Now evaluate a proposed feed-layout change in a separate fictional experiment. Randomize 40,000 eligible accounts once, 20,000 per arm, using stable assignment before treatment. Keep ranking and ad insertion unchanged. Use a fixed 14-day observation period; each user's outcomes are binary across the full period. Eligibility is defined before randomization, unlike the descriptive feed-open population above.

Analyze all assigned eligible accounts, including people who never open the new layout. This intention-to-treat denominator estimates the effect of assignment. Exposure logs diagnose delivery failures; restricting the result to people who saw or interacted with the treatment can introduce selection bias. Check assignment ratios, missing outcomes, platform balance and logging symmetry before interpreting lift.

For teaching arithmetic, assume independent user outcomes and negligible spillovers. That assumption can fail in a social product: one person's behavior changes what another sees. Facebook's published network-experimentation research discusses cluster randomization for interference. If this feature materially affects peer behavior, redesign assignment and uncertainty at the cluster level; the simple user-level interval below is no longer sufficient.

For two independent proportions, use an approximate descriptive 95% interval for the difference: difference ± 1.96 × sqrt(pC(1−pC)/nC + pT(1−pT)/nT). This is an unpooled interval estimate, not the pooled null-hypothesis test formula. It requires adequate counts and the stated independence assumptions.

from math import sqrt

def difference_interval(x_control, n_control, x_treat, n_treat):
    pc, pt = x_control / n_control, x_treat / n_treat
    se = sqrt(pc * (1 - pc) / n_control
              + pt * (1 - pt) / n_treat)
    return pt - pc, pt - pc - 1.96 * se, pt - pc + 1.96 * se

The comment-rate lift is 7.5% relative, with an approximate difference interval of +0.71 to +2.29 percentage points. The hide/report interval is about +0.50 to +1.10 points. Retention's interval is about −1.48 to +0.48 points. Retention is not proven equivalent merely because its interval includes zero.

Before looking at results, set fictional decision limits: require the primary lower bound above +0.5 points, the hide/report upper bound below +0.3 points, and the retention lower bound above −1 point. The primary passes. Hide/report clearly fails; retention also cannot exclude harm beyond its allowed loss. Recommend holding broad launch and revising the layout, even with a positive engagement estimate.

These individual intervals do not form a simultaneous 95% guarantee across all outcomes. A real experiment plan must specify multiplicity, monitoring, power, practical effect sizes and any sequential stopping method before execution. Do not repeatedly peek and stop on the first favorable result. The supplied sample size is a teaching input, not a power calculation or Meta policy.

10Challenge the decision, then name reversal conditions

If someone says seasonality explains the randomized lift, note that concurrent valid randomization should distribute the shared seasonal shock across arms. That argument depends on assignment integrity, comparable logging and the independence/spillover assumptions. A platform-specific delivery bug or unequal exclusion could still explain the observed difference, so check those directly.

If someone argues that more visible content must be better, distinguish exposure from user value. More comments can accompany more frustration, spam or unreadable content. The hide/report result makes that concern concrete. Inspect the predeclared device and accessibility segments and rendering traces to find a fix, rather than choosing a favorable subgroup after seeing results.

A reversal requires a new, properly planned evaluation of the revised layout: validated assignment and exposure, practical primary benefit, acceptable harm bounds and no unresolved critical segment failure. Extending the old test until its retention interval becomes convenient is not the same plan. Keep diagnosis, design iteration and confirmation separate.

11Strong answers produce inspectable evidence

Use a five-day loop: write the metric contract, execute the SQL and boundary fixtures, calculate the experiment, defend the decision in a mock, then repeat your weakest task. Keep the resulting artifacts rather than a list of topics read. For behavioral preparation, choose a true project where you owned an analysis or influenced a decision; distinguish your work from the team's implementation and describe measurement limits.

12Frequently asked questions

Is this guide for every Meta data science role?

It focuses on Product Analytics preparation. Research, modeling and other tracks can require different evidence. Match the current posting and recruiter packet before choosing your preparation mix.

How many interview rounds should I assume?

Use the invitation rather than a universal count. Prepare SQL, product reasoning, experiments and real project discussions, then confirm session names, duration, language and permitted tools.

Why does the SQL use a left join?

Eligible users with no qualifying comment must remain in the denominator. The commenter CTE has one row per user and period so repeated comments or retries do not multiply eligible rows.

Does a significant engagement lift mean we should launch?

No. Validate assignment and measurement, estimate practical benefit, and check the predeclared harm limits. The worked experiment passes the primary threshold but fails the hide-rate guardrail.

Are these real Instagram data or actual Meta experiment results?

No. All cohort counts, assignments, outcomes and decision thresholds are fictional teaching inputs. The SQL and arithmetic were executed locally; no Meta data or private evaluation criteria are claimed.

13Sources and scope

• Engineering at Meta: Airlock: historical example of assignment versus actual exposure and logging failures; not a claim about the current interview loop.
• Karrer and colleagues: Network experimentation at scale: Facebook experimentation research on interference and cluster randomization.
• SQLite SELECT documentation: join, aggregation and distinct behavior used in the local query.
• NIST: Comparing two proportions: independent-proportion inference and large-sample assumptions. The guide distinguishes its unpooled interval from the pooled test.

Checked October 8, 2026. All example data, thresholds and outcomes are fictional. Local execution covers SQL contracts and arithmetic only; it does not establish a real causal effect, validate a live experiment or reveal Meta's private hiring criteria.