Apple Business Intelligence Analyst - Junior Level Interview Preparation Guide (2026)

Direct answer: Once you allow reactivation, "retained in week W" has to mean "had at least one event in week W," checked independently for every week, rather than "still inside one continuous active streak." The counting itself is simple: COUNT(DISTINCT user_id) per (cohort_week, week) never double-counts, because a user either had an event that week or didn't, regardless of how many separate active stretches they've had. Where overlapping intervals actually cause trouble is in any metric that isn't a plain per-week yes/no flag, like a session count or average active-streak length; those need the user's raw active days coalesced into non-overlapping intervals first, or a two-events-in-the-same-week pattern can inflate a total that a simple DISTINCT count never would.

Structured elaboration

The core, correct query needs no interval logic at all:

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('week', MIN(event_date)) AS cohort_week
  FROM events GROUP BY user_id
),
activity_weeks AS (
  SELECT DISTINCT user_id, DATE_TRUNC('week', event_date) AS active_week
  FROM events
),
flagged AS (
  SELECT c.user_id, c.cohort_week,
         DATE_DIFF('week', c.cohort_week, a.active_week) AS week_offset
  FROM cohort c JOIN activity_weeks a ON a.user_id = c.user_id
)
SELECT cohort_week, week_offset, COUNT(DISTINCT user_id) AS retained_users
FROM flagged
GROUP BY cohort_week, week_offset
ORDER BY cohort_week, week_offset;

activity_weeks is already DISTINCT (user_id, active_week), so a user active on three separate days inside the same week only produces one row for that week, and COUNT(DISTINCT user_id) in the final aggregation is a second safety net even if that dedup step were skipped. A user who churns after week_offset 1 and comes back at week_offset 5 simply produces two rows in flagged: one at offset 1, one at offset 5, both counted independently, which is exactly "retained again on return, no double count."

Where overlapping intervals genuinely matter: the moment the question moves from "was this user active in week W" to any statistic that involves the span or count of active stretches, e.g. average session length, number of distinct engagement streaks per cohort, or a churn definition based on N consecutive inactive periods. Those require turning raw active days into non-overlapping intervals first (the classic gaps-and-islands pattern), because summing or averaging over raw, unmerged, possibly-overlapping-or-adjacent day ranges double-counts the days where two source intervals touch:

WITH ordered AS (
  SELECT user_id, event_date,
         LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_date
  FROM (SELECT DISTINCT user_id, event_date FROM events) d
),
flagged AS (
  SELECT user_id, event_date,
         SUM(CASE WHEN prev_date IS NULL OR event_date - prev_date > 14 THEN 1 ELSE 0 END)
           OVER (PARTITION BY user_id ORDER BY event_date) AS session_id
  FROM ordered
)
SELECT user_id, session_id, MIN(event_date) AS session_start, MAX(event_date) AS session_end
FROM flagged
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

This assigns a new session_id every time the gap since the previous active day exceeds a business-defined threshold (14 days here), then collapses each session to its start/end. That gives you clean, non-overlapping intervals to compute session-level statistics from, without touching the weekly retention flag at all: the two queries answer different questions and don't need to be merged into one pipeline.

Worked example (executed in DuckDB)

Two users, cohort_week = the week containing 2026-01-05. User 1 is active in week_offset 0 (2026-01-05), then goes quiet, then reactivates in week_offset 5 (2026-02-09). User 2 is active continuously in week_offsets 0, 1, 2, then stops. Running the retention query gives retained_users of 2 at offset 0, 1 at offset 1, 1 at offset 2, and 1 at offset 5 (user 1 alone, reappearing), with no row at offsets 3 or 4. Each (cohort_week, week_offset) pair appears exactly once per qualifying user, confirming no double count on the reactivation. Running the sessionization query separately on the same data produces two sessions for user 1 (2026-01-05 to 2026-01-10, then a second session starting 2026-02-09, since the gap exceeds 14 days) and one continuous session for user 2 (2026-01-05 to 2026-01-19, since consecutive active weeks are within the 14-day threshold of each other).

Trade-offs & pitfalls

• The temptation to build a heavier gaps-and-islands-then-coalesce pipeline for the basic retention matrix is a real trap: it adds real complexity (and, if the coalescing step is written carelessly, real bugs) for a guarantee that COUNT(DISTINCT user_id) already provides for a simple per-week flag.
• The gap threshold used to define a "session" (14 days, 30 days, or something else) is a business decision, not a technical one, and changes what counts as one continuous engagement versus two separate reactivations; state it explicitly rather than hardcoding it silently.
• If cohort assignment itself uses each user's first event, a user's cohort never moves due to reactivation; only their weekly retention flags do. Conflating "cohort" with "current active streak" is a common source of retroactively-shifting cohort bugs.
• Time zone and week-boundary definitions (Monday-start vs Sunday-start weeks) change which week a borderline event lands in; keep the boundary consistent between the cohort assignment and the activity-week bucketing, or reactivation timing can appear off by one week.
• The sessionization query above uses an iterative window-function scan (LAG plus a running SUM); a recursive CTE (one that refers to itself to walk forward day by day or session by session) is an alternative way to express the same gap-and-island merge, and can be easier to reason about if the merge rule itself is more complex than a fixed day threshold, at the cost of the recursion's own overhead versus a single windowed pass.

Direct answer. Look at the shared-buffer or block-level statistics EXPLAIN reports alongside timing at each node: a node whose block reads dominate its own time, especially with many uncached ("read" rather than "hit") blocks, points at an I/O-bound step, while a node whose time is high but block activity is low points elsewhere, typically CPU-bound computation or lock waiting.

Structured elaboration. Buffer-level reporting (available in many engines' detailed EXPLAIN ANALYZE output) typically distinguishes blocks found already in memory (a "hit") from blocks that had to be fetched (a "read," which usually implies real disk or network I/O, though this can itself be served from an OS-level cache rather than physical disk). A scan node reporting a large number of non-cached block reads, with actual time roughly proportional to that read count, is straightforwardly I/O-bound: the bottleneck is getting the data off storage, and the fix path runs through reducing how much data needs to be read (a better index, a narrower projection, more selective partitioning) or increasing how much of the working set fits in cache. A node with modest block activity but disproportionately high time is more likely CPU-bound (an expensive per-row computation, a large sort, a complex aggregate) or contending for a lock, neither of which more I/O-focused fixes will help.

Worked example. Comparing two plan nodes with similar row counts: a sequential scan showing a large number of uncached block reads and time roughly proportional to that count is a strong I/O-bound signal, worth attacking with an index or a narrower scan; a hash aggregate over the same row count showing almost all cached hits (little real I/O) but still consuming a large share of total time is more likely CPU-bound on the aggregation computation itself, which a different index wouldn't meaningfully help.

Trade-offs and pitfalls. Buffer-hit-versus-read numbers reflect the STATE of the cache at the moment you ran the query, which can be misleading if you test right after a cold restart (everything looks I/O-bound because nothing is cached yet) or right after running the same query repeatedly (everything looks CPU-bound because it's now fully cached); run this diagnostic under conditions that resemble the workload's real, steady-state cache behavior, not an artificially cold or artificially warm one, or explicitly test both to understand the range.

Direct answer

Don't try to average everyone's position into a compromise nobody's happy with. Ground the negotiation in the shared outcome, make the trade-offs between options explicit with evidence, and force a real decision (with an owner and a documented rationale) within a fixed timeframe. A compromise sticks when people can see why it was chosen, not just that it split the difference.

Structured elaboration

1. Reframe around outcome, not position. Ask each stakeholder what success looks like for them, not what they want built. Two stakeholders who seem opposed on the "what" often agree on the "why," which is where the real compromise lives.
2. Bring evidence, not opinions. Gather whatever is available and relevant: usage data, cost/effort estimates, prior incidents, qualitative feedback. A room full of opinions negotiates forever; a room with a shared set of facts converges faster.
3. Make trade-offs visible. Lay out 2-3 real options with their costs and benefits side by side, instead of a single proposal to accept or reject. People compromise more easily when they're choosing between concrete alternatives than when they're being asked to give up a specific ask.
4. Use a structured negotiation move. Propose a balanced default option first, then invite each side to request a bounded concession from it, rather than starting from each side's maximal ask and negotiating down. Time-box the discussion so it doesn't drift into re-litigating the same points.
5. Document the decision and name an owner. Write down what was decided, why, who owns it, and when it will be revisited. If the group truly can't converge, escalate with a specific recommendation rather than an open question, so the escalation itself doesn't become another unresolved debate.
6. Build in a review point. Treat the agreement as provisional and testable, not permanent. A short follow-up (after the next milestone, or a fixed number of weeks) to check whether the compromise is actually working keeps people bought in because they know it isn't final and unappealable.

Worked example

Three stakeholders disagree on scope for a feature: one wants the full version shipped now, one wants it deferred a quarter, one wants a stripped-down version shipped immediately. Instead of negotiating "how much scope," the facilitator asks each what outcome they're protecting: the first is protecting a customer commitment, the second is protecting engineering capacity for other work, the third is protecting the team's ability to learn before over-investing. That reframing surfaces a real option none of them had proposed: ship a narrow version that satisfies the customer commitment, explicitly scoped as a first iteration, with the deferred work logged and re-prioritized at the next planning cycle. The decision, the scope boundary, and the re-prioritization date are written down and shared with all three stakeholders.

Trade-offs & pitfalls

• Pitfall: false compromise, where everyone gets a token piece of what they asked for and the result satisfies no one's actual underlying need.
• Pitfall: skipping documentation. An undocumented "agreement" gets re-argued the moment someone's memory of it differs.
• Pitfall: treating consensus as required. Some decisions need a single accountable owner to make the call after input, not unanimous agreement, especially under a deadline.
• Senior differentiator: designing the forcing function (a default option, a timebox, a named decision owner) instead of facilitating an open-ended discussion indefinitely. That's what turns "several people who each want something different" into an actual decision.

Situation: I’m transferring from one BI team to another and must ensure dashboards, analyses, and models keep running and can be improved without disruption.

Task: Create a structured, low-risk handoff so the new team can maintain and enhance artifacts quickly.

Action:

1. Inventory & prioritization (Day 1): I compile a catalog (spreadsheet) of all artifacts with owner, business consumer, tool (Tableau/Power BI/Looker), data sources, last updated, criticality (P0–P3), and SLAs.
2. Dependency & lineage mapping: For each item I map ETL jobs, source tables, dbt models, refresh schedules, downstream consumers, and sensitive data flags. I attach SQL snippets or model names.
3. Documentation bundle: For each artifact I provide:
   • Purpose and key metrics (KPIs, definitions)
   • Data model diagram and transformation logic
   • Access instructions and credentials locations (vault references)
   • Troubleshooting runbook (common failures, logs to check, quick fixes)
   • Test cases and validation queries used for QA
4. Demo & walkthroughs: I run recorded walkthroughs plus 2 live sessions—one with engineers (tech deep dive), one with stakeholders (business context). I walk through rebuilding a dashboard end-to-end.
5. Shadowing period (2 weeks): I take tickets with the new team, review their fixes, and hand over ownership gradually.
6. Automated checks & alerts: I ensure monitors exist (data freshness, row counts, anomaly alerts) and that alerting routes to the new team’s channel.
7. Clean-up & tech-debt log: I list known bugs, undocumented hacks, and suggested refactors with estimated effort.
8. Sign-off & access transfer: Formal sign-off with a checklist: inventory acceptance, ability to run ETL, permissioned access, and confirmed notifications. I leave contact for fast follow-up for 30 days.

Result: This approach minimizes downtime, preserves business context, transfers operational knowledge, and gives the new BI team a prioritized roadmap to maintain and enhance artifacts. Learned: combine clear docs + hands-on shadowing to move tacit knowledge into team capability.

Direct answer. NULL never equals anything, including another NULL, so a join's equality condition (or a UNION/INTERSECT/EXCEPT row comparison) silently treats two NULL keys as non-matching, which can drop rows you'd expect to see or distort a downstream aggregate that assumed every row got included.

Structured elaboration. This is the same three-valued-logic fact showing up in two related places. In a join predicate, a.key = b.key evaluates to unknown (not true) whenever either side is NULL, so that pair is never considered a match, full stop, regardless of join type. In a set operation, UNION/INTERSECT/EXCEPT compare whole rows for equality, and most engines treat two NULLs in the same column position as "the same" for the PURPOSE of deduplication or set comparison (this is a special-cased exception to normal SQL NULL semantics, and it's the opposite behavior from a join predicate, which is exactly why it surprises people). Separately, once you're past the join, any NULL that a LEFT JOIN introduces into the right-hand columns will silently exclude those rows from a naive SUM or AVG (SUM ignores NULLs, which is usually fine, but AVG's denominator is the count of NON-NULL values, which can quietly change what "average" means).

Worked example. left_tbl(key, val): (1,'a'), (NULL,'b'). right_tbl(key, val): (1,'x'), (NULL,'y').

-- the NULL-keyed row on each side never matches, even though both keys "look the same"
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON l.key = r.key;
-- returns only ('a','x'); the two NULL-keyed rows are silently excluded

-- COALESCE lets you decide explicitly whether NULL keys should match each other
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON COALESCE(l.key, -1) = COALESCE(r.key, -1);
-- now returns ('a','x') AND ('b','y')

For the aggregate side: if orders(customer_id, amount) is LEFT JOINed from customers and a customer has no matching order, amount is NULL for that row. AVG(amount) divides by the count of non-NULL amounts, not the count of customers, so a naive AVG silently excludes the zero-order customers from the denominator rather than treating them as zero.

Trade-offs and pitfalls. COALESCE is a blunt instrument: picking a sentinel value like -1 to stand in for NULL is only safe if that sentinel can never be a legitimate key value in the data, otherwise you've just created a false match. For aggregates, the fix is usually to be explicit about what you're dividing by: COALESCE the joined column to 0 before summing if "no match" should count as zero, and compute your denominator from the LEFT side's row count directly rather than trusting COUNT/AVG's default NULL-skipping behavior to mean what you want.

Direct answer

An unqualified column that exists in more than one joined table is ambiguous, and most engines refuse to guess: they raise a compile-time error naming the column. Fix it by aliasing every table and qualifying every column reference. The genuinely dangerous version of this bug is not the one that errors, it's when the same unqualified name exists in only ONE of the tables currently in scope: no error is raised, the reference silently binds to that one table, and if you meant the other one (or a different scope entirely), you get a wrong answer with no warning at all.

Structured elaboration

Approach: understand SQL's name-resolution search order, then apply blanket aliasing discipline rather than only fixing the column that happened to error.

1. An unqualified column is resolved by searching every table (and, for a subquery, every enclosing outer scope too) currently in scope.
2. If it matches in more than one place, the engine raises an ambiguous-reference error and refuses to guess.
3. If it matches in exactly one place, it silently binds there, even if that's not the table the author intended. This can happen invisibly across scope boundaries: a correlated subquery's inner query searches its own tables first, but if the name isn't found there, it searches the OUTER query's columns next, without error.

Key points:

• Alias every table with a short, meaningful alias (u, a, not t1, t2), and qualify every column, including ones that look unique today. Schemas change; a later migration can silently turn a previously-safe unqualified reference into either an error or, worse, a silent misbind.
• Treat the compile-time ambiguous-reference error as the SAFE outcome. It fails loudly. The real risk is the case that doesn't error.

Worked example

Seed data: users(id, name, created_at) and accounts(id, user_id, created_at), joined on users.id = accounts.user_id. Both tables have id and created_at.

Ambiguous query:

SELECT id, name, created_at
FROM users
JOIN accounts ON users.id = accounts.user_id
WHERE created_at > '2024-01-01';

Run in DuckDB, this raises (verbatim):

BinderException: Binder Error: Ambiguous reference to column name "id" (use: "users.id" or "accounts.id")
LINE 2: SELECT id, name, created_at
               ^

Fixed, aliased and fully qualified:

SELECT u.id AS user_id,
       u.name,
       u.created_at AS user_created_at,
       a.created_at AS account_created_at
FROM users AS u
JOIN accounts AS a
  ON u.id = a.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY u.id;

Result:

user_id | name | user_created_at | account_created_at
--------+------+-----------------+-------------------
      2 | Bob  |      2024-02-15 |         2024-03-10

Note the output also renames both created_at columns explicitly in the SELECT list. Qualifying a column in the WHERE clause is not enough if the SELECT list still emits an unqualified (or ambiguous-looking) output column name; a consumer of this result set needs to know which created_at they're looking at just as much as the query engine does.

Now the silent, no-error version of the same underlying mistake. Suppose the intent was "does this user have any account at all", written as a correlated subquery with an unqualified id:

SELECT u.id, u.name,
  EXISTS (SELECT 1 FROM accounts WHERE user_id = id) AS has_account
FROM users u
ORDER BY u.id;

Both Alice and Bob actually have an account in the seed data, so has_account should be TRUE for both. Result (run in DuckDB):

id | name  | has_account
---+-------+------------
 1 | Alice |       False
 2 | Bob   |       False

No error. The inner id is resolved against accounts.id (the innermost table that has a column by that name), not the intended u.id from the outer query, so the subquery effectively became accounts.user_id = accounts.id, a self-referential comparison that's false for every row in this data. Qualifying it fixes it silently and completely:

SELECT u.id, u.name,
  EXISTS (SELECT 1 FROM accounts a WHERE a.user_id = u.id) AS has_account
FROM users u
ORDER BY u.id;

Result:

id | name  | has_account
---+-------+------------
 1 | Alice |        True
 2 | Bob   |        True

Complexity

Both the correlated subquery and its qualified fix run in the same complexity class for this data size, one lookup into accounts per row of users. Qualifying the column changes correctness, not cost. At larger scale, the correlated form is O(n) subquery evaluations against accounts; if accounts.user_id isn't indexed, that's O(n * m). This is a separate concern from the ambiguity bug itself, but worth naming since correlated subqueries are exactly where the silent-misbind version of this bug hides.

Edge cases

• A column name that's unique today but will collide after a planned schema change (e.g., two tables both gaining a status column): unqualified references that are safe now are landmines for the next migration.
• SELECT * combined with a join on tables sharing a column name doesn't error, it just silently produces two output columns with the same name, which most client libraries then handle inconsistently (last one wins, or an error at the client layer, depending on the driver).
• Self-joins (a table joined to itself) always require aliases, there is no unqualified fallback that works at all.

Trade-offs & pitfalls

• A hard ambiguous-reference error is actually the safe branch of this bug, because it's loud. Budget code-review attention toward the silent branch: any unqualified column inside a correlated subquery deserves a second look, since its resolution can reach into the outer query without any syntax marking that it did.
• Aliasing discipline is cheap and doesn't cost readability once aliases are short and meaningful; resist the temptation to skip it on "simple" two-table joins, since those are exactly the queries most likely to get a third table bolted on later without a full rewrite.
• Different engines have historically differed on how strict this checking is (some older or looser SQL layers resolve ambiguity by table order rather than erroring). Don't assume behavior observed on one engine generalizes to another; verify against the engine actually in production.

Direct answer

The underlying finding stays identical, but you change altitude, vocabulary, and level of supporting detail. An executive gets the headline, the business impact, and the recommended decision in one or two lines up front. The implementation team gets the mechanism, the caveats, and enough of the underlying data to act on it correctly.

Structured elaboration

• Altitude: conclusion-first for the executive, versus enough method detail for the team to trust and reproduce the diagnosis.
• Vocabulary: business-impact language (revenue, risk, timeline) for the executive, technical specifics (segments, funnels, thresholds) for the team.
• Format: a one-slide or one-paragraph summary versus a working document with a data appendix.
• What must never change: the number itself and the direction of the conclusion, in both versions.

Worked example

Finding: onboarding drop-off at step 3 is costing an estimated 6% of new signups per month.
Executive version: "we're losing about 6 of every 100 new signups at the step-3 confirmation screen, fixing it could recover meaningful revenue this quarter, recommend prioritizing it."
Team version: "62% of that drop-off happens on mobile between form submit and confirmation render, median time to abandon is 9 seconds, this looks like a loading-state issue on mobile specifically."
Both versions agree on the 6% headline number and the recommendation to prioritize the fix.

Trade-offs and pitfalls

The two versions can quietly drift into different conclusions if you're not careful, always trace both back to the same underlying analysis. Over-simplifying for the executive can also strip out the one caveat that would have changed their decision, so pick what to omit deliberately, not by default.

What the interviewer probes next

Expect a question about what happens when the executive summary gets forwarded on without you in the room, and how you prevent it from being read out of context.

Direct answer
When one group wants flexible, arbitrary ad-hoc exploration and another wants a small set of fast, trustworthy curated views, the right answer is usually both, as a hybrid: a small number of governed, pre-aggregated curated dashboards for routine decisions, plus a separate self-serve template with guardrails (definitions, sampling warnings) for ad-hoc exploration, rather than forcing every user through one interface.

Structured elaboration

1. Curated views: pre-aggregated, fast, and governed with a single agreed metric definition; serve the majority of routine, repeated questions ("what's this week's conversion rate") reliably and quickly.
2. Self-serve exploration: a template with a documented, safe data model (which events/properties are available, what they mean), and safeguards like sampling warnings when a query would run on a sampled dataset, or definitional callouts when a metric's ad-hoc computation might diverge from the official curated version.
3. Hybrid design: default every user to the curated view; expose a clearly-labeled "explore" mode as an escape hatch for users who need flexibility, so the majority of traffic stays on fast, governed views while power users get the flexibility they need without degrading performance or trust for everyone else.
4. Guardrails specifically for self-serve: cap query complexity or row scans to protect shared infrastructure, warn when a query result is based on a sample rather than the full dataset, and label any self-serve-computed metric as "unofficial/exploratory" if it isn't reconciled against the curated metric layer.

Worked example
A growth team gets a curated weekly dashboard for their standard KPIs (fast, governed), plus a self-serve funnel-builder tool that lets them define an arbitrary custom funnel for a one-off analysis, with a visible warning banner whenever the funnel-builder's query would exceed a row-scan threshold and fall back to sampling.

Trade-offs and pitfalls
Building only the curated set frustrates power users who need to explore; building only self-serve tooling leads to metric proliferation (everyone computing "activation" slightly differently) and slower, less predictable performance for routine questions. The hybrid costs more to build and maintain than either alone.

Direct answer

Map each AARRR (Acquisition, Activation, Retention, Referral, Revenue, commonly called "pirate metrics") stage to one or two channel-attributable KPIs (key performance indicators) and one visualization built for a fast, non-technical read: a stacked bar for acquisition volume and cost by channel, a funnel for activation drop-off, a cohort heatmap for retention, and a combined trend-plus-bar for revenue. The brief here focuses on acquisition, activation, retention, and revenue; referral would slot in the same way (referral rate, one viral-loop chart) if the marketing manager later asks for it.

Structured elaboration

Include a channel filter and a date-range picker so the same dashboard supports both a monthly executive glance and a campaign manager's weekly diagnostic dig, and annotate major campaign launches directly on the charts so a metric move has an obvious candidate explanation attached.

Worked example

For one channel with $10,000 spent in a month producing 500 new signups:

Of those 500 signups, 350 complete the defined activation action (say, creating a first project):

Of the original 500 signups, 140 are still active on day 7:

A marketing manager reading the funnel sees the full chain at a glance: 500 acquired at $20 each, 70% activate, and 28% of the original cohort are still around a week later, which is a very different story than any single one of those numbers shown alone.

Trade-offs & pitfalls

A single shared dashboard that tries to serve every stage equally for every audience tends to serve none of them well; an executive wants the revenue trend and retention heatmap, while a campaign manager wants the acquisition breakdown and activation funnel drilled down by specific channel and creative. The fix is one dashboard with role-based default views, not one static layout for everyone. A common mistake is choosing acquisition volume as the headline number without pairing it with cost or downstream activation, which rewards channels that bring cheap, low-intent traffic that never activates. Cohort heatmaps communicate retention trends well but can be misread by a non-technical viewer as a simple gradient rather than a time-since-acquisition comparison; a one-line caption stating what the axes mean avoids that misreading.

Compute a rolling mean and standard deviation over a trailing window using window functions, partitioned by whatever dimension matters (country, service, product), and flag a day whose absolute z-score against that rolling baseline crosses a threshold.

Approach (verified by execution)

```sql
WITH w AS (
SELECT d, country, users,
AVG(users) OVER (PARTITION BY country ORDER BY d ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING) AS roll_mean,
STDDEV_SAMP(users) OVER (PARTITION BY country ORDER BY d ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING) AS roll_std
FROM daily_active_users
)
SELECT d, country, users, roll_mean, roll_std,
(roll_std > 0 AND ABS(users - roll_mean) > 3 * roll_std) AS is_anomaly
FROM w;
```

Worked example (verified by execution)

Six days of a country's DAU holding steady around 990-1010, followed by a seventh day at 2000: the rolling mean and standard deviation computed only from the prior six days correctly stay tight (mean around 1000, standard deviation in the single digits), so 2000 clears the 3-sigma bar decisively and flags correctly, while a separate, much smaller country's normal day-to-day fluctuation stays unflagged.

Trade-offs and pitfalls

Early in the series (the first 29 days, in this example), the window doesn't yet have enough history and the baseline is either undefined or unreliable, a cold-start problem every rolling-window check has to account for explicitly (typically by suppressing alerts until the window is full). A flat rolling window also doesn't know about known seasonality; an exponentially weighted moving average (EWMA) reacts faster to genuine recent shifts while still smoothing noise, and comparing against a day-of-week-normalized baseline avoids flagging every Monday as an anomaly relative to a weekend-heavy average. Finally, running the identical 3-sigma rule independently across many segments (countries, products) at once compounds the false-positive rate across the whole set, since checking dozens of segments daily means SOME of them will cross a 3-sigma threshold by chance alone; a multiple-comparisons-aware adjustment (or simply requiring a higher bar when scanning many segments) keeps that compounding effect from drowning a real signal in noise.