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

Approach: show ISO-week (weeks start Monday; ISO week-numbering year) and a Sunday-start week-number (week starting Sunday) in Postgres and BigQuery for date '2024-01-01', then explain difference and recommendation.

Postgres (ISO week via to_char 'IYYY'/'IW', Sunday-start via date_trunc + extract):

SELECT
  date '2024-01-01' AS dt,
  to_char(date '2024-01-01', 'IYYY-IW') AS iso_year_week,        -- ISO year-week
  extract(week from date_trunc('week', date '2024-01-01' + 1))::int AS sunday_week_number; -- Sunday-start: shift then truncate

BigQuery (ISO via FORMAT_DATE/ISO_WEEK, Sunday-start via EXTRACT with WEEK(MODE)):

SELECT
  DATE '2024-01-01' AS dt,
  FORMAT_DATE('%G-%V', DATE '2024-01-01') AS iso_year_week,        -- ISO year (G) and ISO week (V)
  EXTRACT(WEEK FROM DATE '2024-01-01' WEEK(MONDAY)) - 0 AS monday_week_number,
  EXTRACT(WEEK FROM DATE '2024-01-01' WEEK(SUNDAY)) AS sunday_week_number;

Result explanation: For 2024-01-01 (a Monday), ISO week = 2024-01 (ISO assigns week 1 to the week with Jan 4). A Sunday-start method may place the date in week 1 or week 0 depending on implementation — definitions differ and can show 2023-week numbers for dates near year boundaries.

Recommendation: Use ISO-week for global reports because it's standardized (ISO-8601), consistent across locales, and avoids ambiguous year-boundary behavior. If business requires US convention, document and stick to Sunday-start consistently.

Direct answer

Lead with a one-line factual headline, not a narrative buildup, then follow immediately with what you're doing about it. Leadership's first question is always "what happens next," and making them wait for it while you explain the backstory reads as stalling.

Structured elaboration

1. Headline first: what's late, by roughly how much, stated plainly, no hedging language that makes people wonder if you're minimizing it.
2. One or two sentences of cause, at a level leadership can act on, a dependency slipped, a scope surprise, not a blow-by-blow of every technical decision that led here.
3. The plan: concrete next steps with owners and rough timing, even if the timing itself isn't final. "Here's how we'll know more by Friday" is a plan; "we're working on it" is not.
4. Handling pointed questions: answer what you actually know, say plainly when you don't know something rather than guessing to sound in control, and commit to a specific follow-up instead of a vague "I'll keep you posted." Take the most senior or highest-stakes question first rather than letting the conversation drift to whoever's loudest.
5. Close with a cadence: when you'll update again, and through what channel, so the room isn't left wondering whether this becomes a pattern of surprises.

Worked example

A high-visibility platform migration is going to miss its committed date by three weeks. In the leadership update, you open with "the migration will land three weeks past the committed date," not with a summary of everything that's gone right so far. You give the cause: "a dependency on the vendor's new authentication API turned out to need more integration work than their documentation implied." You lay out the plan: a revised, phased timeline with the riskiest piece de-risked first, and an owner and date for each phase. When someone asks "why didn't we know this two weeks ago," you say plainly: "we suspected it a week ago and confirmed it Tuesday, that's a valid gap, and here's what we're changing about how we track vendor dependencies going forward," rather than getting defensive or deflecting.

Trade-offs and pitfalls

• Leading with justification instead of the headline, explaining everything that went right first, reads as avoidance and makes people tune out before they hear the actual news.
• Promising a specific new date under pressure before you've actually validated it is the single most common way this conversation creates a second, worse version of itself two weeks later.
• Answering "I don't know" honestly feels risky in the room but is almost always received better than a confident guess that turns out wrong. Confidence you can't back up erodes trust more than admitting a gap does.
• Treating every pointed question as an attack invites a defensive tone that reads worse than the delay itself. Most pointed questions from leadership are about risk to other commitments, not about assigning blame.

Direct answer

When a small number of superusers dominate an average behavioral metric, the average stops representing a typical user, so robust reporting means leading with metrics less sensitive to extreme values, such as the median or a specific percentile, and reporting alternative cuts like top-decile share separately rather than folding everything into one mean.

Structured elaboration

A mean is pulled arbitrarily far by a small number of extreme values because it weights every unit of activity equally regardless of who generated it; a median is far more robust because it only cares about the middle of the distribution's ordering, not the magnitude of the extremes. For behavioral data specifically, a useful complementary pair is the median (what a typical user looks like) alongside a measure of concentration, such as what share of total activity comes from the top decile of users, since that number directly answers "how dependent is this metric on a small group" in a way neither the mean nor the median does alone. When stakeholders need to see the effect of a change on power users specifically, reporting a percentile (p90 or p99) alongside the median gives visibility into that population without letting it distort the headline number.

Worked example

import numpy as np
np.random.seed(5)

n = 1000
activity = np.random.poisson(5, n).astype(float)          # most users: modest activity
superuser_idx = np.random.choice(n, size=int(0.02 * n), replace=False)   # top 2%
activity[superuser_idx] += np.random.gamma(shape=2, scale=150, size=len(superuser_idx))

print('mean:', round(activity.mean(), 2), ' median:', round(np.median(activity), 2))
top_decile_share = activity[np.argsort(activity)[-100:]].sum() / activity.sum()
print('top-decile share of total activity:', f"{top_decile_share:.1%}")
print('superuser mean:', round(activity[superuser_idx].mean(), 1),
      ' non-superuser mean:', round(np.delete(activity, superuser_idx).mean(), 2))

Simulating 1,000 users' weekly event counts, where most users generate a modest, Poisson-distributed amount of activity but the top 2% (20 users) are superusers with much higher activity, gives a mean of 12.24 events per user but a median of only 5.00, a mean over twice the median. The top 100 users (the top decile) account for 65.5% of all activity in the simulation, and the 20 identified superusers alone average 361.8 events each, compared to 5.11 for everyone else. Reporting "average weekly engagement is 12.24 events" without qualification would meaningfully overstate what a typical user actually experiences, since more than 90% of users generated fewer events than that average.

Trade-offs and pitfalls

The main pitfall is reporting only the mean, or only the median, since each hides something the other reveals: the mean alone hides how unrepresentative it is of a typical user, and the median alone hides how much of the metric's total the tail contributes, which matters for questions like revenue or infrastructure cost that scale with total activity rather than with a typical user's activity. A second pitfall is treating superusers purely as a reporting nuisance to smooth away, when in practice they are often the most valuable and highest-retention segment of the user base, worth understanding and serving deliberately rather than only excluding from headline metrics.

Direct answer

I ran a six-week test of a new onboarding email sequence, hypothesizing that adding a short personalized video would raise activation, and by week four the data was inconclusive rather than clearly negative, which is the harder call: deciding whether to keep running for a real signal or stop because the result had stopped being informative. I stopped at week five, explained the decision and the reasoning to the two stakeholders who had sunk real time into producing the videos, and made sure what we'd learned about the underlying segment behavior carried into the next attempt instead of being lost with the failed one.

The hypothesis, design, and timeline

The hypothesis was that a short, personalized video early in onboarding would raise activation among users who had signed up but not completed setup, based on a pattern we'd seen in a smaller pilot. I designed a six-week A/B test with a defined minimum sample size calculated up front, specifically so I wouldn't be tempted to call it early or late based on how the numbers happened to be trending on a given day.

How I made the stop-or-continue call

By week four, the treatment group's activation rate wasn't meaningfully different from control, but the sample was also smaller than planned because a tracking issue had silently dropped a portion of the treatment group's data for the first ten days, which meant the result was underpowered (we didn't have enough clean data left to trust a negative result either way, not that the result was actually bad), not simply negative. I spent part of week four determining whether that was an environmental problem, the tracking gap, rather than a genuine sign the video didn't work. Extending the test to compensate was one option; I decided against it, because even a clean extension wouldn't have told us anything about the actual hypothesis with confidence by a reasonable date, and continuing mainly to avoid calling it a failure would have been the wrong reason to keep going.

What I did with what I'd learned

I stopped at week five and told the two people who had built the videos directly: the specific reason, an underpowered and contaminated dataset rather than a clear negative result, and that the honest conclusion was "inconclusive," not "the idea doesn't work." Rather than letting the attempt just end there, I salvaged what was usable: the clean portion of the data still showed a real behavioral pattern in how users engaged with onboarding content at all, which fed directly into redesigning the next attempt's tracking and targeting before we tried a similar idea again.

Trade-offs and pitfalls

The trade-off in a stop-or-continue call like this is sunk cost against real signal: the video work represented real time from real people, and there's pressure to keep going just to justify that investment rather than to actually learn something. The pitfall I watch for is treating "inconclusive" and "failed" as the same thing when explaining the decision, since conflating them either overstates how wrong the idea was or understates how little the test actually proved either way.

Direct answer: Diversity is who is in the room (the range of backgrounds, identities, and perspectives on a team). Equity is fair access to opportunity and outcomes, accounting for the fact that people start from different positions. Inclusion is whether the people in the room are actually heard, valued, and able to do their best work. Belonging is the felt result: people feel safe being themselves and see a future for themselves on the team. You can have diversity without inclusion (a diverse team where only some voices count), and inclusion without diversity (a homogeneous team that is warm to itself but has never had to prove it works across difference).

Structured elaboration:

• Diversity is a composition metric: representation across dimensions like gender, race/ethnicity, disability, age, veteran status, and less-visible dimensions like educational background or neurotype. It is countable.
• Equity is about process and outcomes being fair given different starting points. Equal treatment (same resources for everyone) is not the same as equitable treatment (resources matched to need, so outcomes converge). Day to day, this shows up as: two engineers get the same $500 conference budget (equal treatment), versus a caregiver on the team getting an asynchronous-friendly review schedule so they aren't penalized for missing a 7am stand-up they physically can't attend (equitable treatment, because the starting constraint differs). The measurable signal to watch is an outcome gap between groups on an ostensibly neutral process: a pay-equity analysis (adjusted pay gap by role and level, not raw average pay) or a promotion-calibration audit (promotion rate by group, controlling for tenure and performance rating).
• Inclusion is about behavior and process, day to day: does a design review actively invite dissent from a junior engineer, or only from the two most senior voices? Does the on-call rotation account for who has caregiving responsibilities at 2am?
• Belonging is the lagging, felt outcome of the other three. It's measured less by counting and more by asking (surveys, exit interviews, retention by group) and by proxies (who speaks in meetings, who gets credited in postmortems, who stays past year one).

Worked example: A 12-person engineering team is 40% women (diversity: reasonable). But in design reviews, the two most senior engineers get 80% of speaking time (a lack of inclusion, measurable by a rough speaking-time tally over a few meetings), and the women on the team leave within 18 months at twice the rate of men (a belonging/retention problem, measurable directly from HR data). Diversity alone told you nothing about the inclusion or belonging problem, and the team could easily "look diverse on paper" while quietly losing the people that diversity number depends on.

Trade-offs and pitfalls: The most common mistake is treating diversity (a headcount number) as the whole goal and stopping there; it's the easiest of the four to measure and the easiest to game (hire diverse, don't retain). The second common mistake is treating "inclusion" and "belonging" as fuzzy and unmeasurable and therefore skipping them; they have concrete behavioral and survey proxies (speaking time, promotion rate by tenure, eNPS-style belonging questions, regretted-attrition by group) even if the proxies are noisier than a headcount.

Situation: I joined a BI team with many conflicting metric definitions (e.g., “active user”, “churn”, “MRR”) and an MVP release scheduled in 4 weeks. The product must ship, but core metrics need standardization without blocking release.

Plan (pragmatic, time-boxed):

1. Triage & scope (Days 0–3)

• Identify 6–8 core metrics required by MVP reports (stakeholder ask + analytics usage).
• Categorize conflicts: definition drift, calculation variance, ETL timing, naming.

2. Produce canonical definitions (Days 3–10, parallel work)

• For each metric, create a one-page canonical spec: name, business intent, formal SQL definition, data source(s), calculation window, timezone, cadence, owner, examples.
• Store specs in a lightweight metadata repo (Markdown in git or a simple catalog) linked to BI tool lineage.

3. Ensure backward compatibility (Days 10–18)

• Implement canonical views/materialized views in the warehouse that expose the new canonical metrics with stable API names.
• Create compatibility layer: maintain legacy views as thin wrappers that map old names to canonical logic where semantics align. If differences exist, tag legacy view as “deprecated” but keep consistent results for MVP by versioning views (e.g., users_v1_active_users).
• Add feature flags / dataset version parameter so dashboards can opt into v1 or canonical definitions.

4. Validation & QA (Days 18–24)

• Run reconciliation tests: row-level sampling and automated checks comparing legacy vs canonical results, with acceptable delta thresholds documented.
• Smoke-test key dashboards; produce a “differences report” for stakeholders highlighting any material changes.

5. Release without blocking (Days 24–28)

• Ship MVP using legacy views mapped to canonical calculations where possible. If immediate parity cannot be achieved for a non-critical metric, document discrepancy and ship with a note in the dashboard metadata.
• Communicate clearly in release notes which metrics are canonical, which are deprecated, and where to find specs.

Governance to prevent recurrence:

• Establish metric stewardship: assign owners for each canonical metric and a lightweight change process (metric RFC in PRs, 48–72 hour review SLA).
• Add metric registry in the BI catalog (includes lineage, owners, SLA, tests).
• Enforce CI for metric SQL: unit tests, data contract checks, and automated lineage verification before merging.
• Quarterly metric review meetings with cross-functional reps to approve new metrics or changes.
• Embed canonical metric badges in BI dashboards and block publishing to “production” if using non-canonical sources (soft enforcement first, hard after one quarter).

Why this works:

• Time-boxed to avoid blocking MVP.
• Produces repeatable artifacts (specs, views, tests) that scale.
• Compatibility layer prevents breaking reports while enabling migration.
• Governance balances speed and control so standardization becomes sustainable.

Direct answer: For each user, number the activity dates in order with ROW_NUMBER(), then subtract that row number (in days) from the actual date. Within one unbroken run of consecutive days, the date increases by exactly 1 each row while the row number also increases by exactly 1, so date - row_number is a constant for the entire run and jumps to a new constant the moment there's a gap. That constant is a ready-made, stable group id: group by it (per user) and aggregate to get each streak's start, end, and length.

Structured elaboration

Why the trick works, concretely. If a user is active on Jan 1, 2, 3 (three consecutive days), their row numbers are 1, 2, 3. date - row_number, expressed as date - (row_number * INTERVAL 1 day) so both sides are dates, gives Dec 31, Dec 31, Dec 31 for all three rows: the row number is climbing at exactly the same rate as the date, so the difference is invariant. The moment there's a gap (say the next activity is Jan 5, skipping Jan 4), the row number continues climbing by 1 (to 4) but the date jumps by 2, so date - row_number shifts to a new constant. Every row in a contiguous run shares one constant; every gap produces a new constant. That is why grouping by this value is safe and deterministic, unlike an arbitrary running counter that would need a separate flag-and-cumsum step (the LAG-based alternative below does exactly that instead).

WITH numbered AS (
  SELECT user_id, activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn
  FROM activity
),
grouped AS (
  SELECT user_id, activity_date,
    activity_date - (rn * INTERVAL '1 day') AS island_id
  FROM numbered
)
SELECT user_id, island_id,
  MIN(activity_date) AS streak_start, MAX(activity_date) AS streak_end, COUNT(*) AS streak_length
FROM grouped
GROUP BY user_id, island_id
ORDER BY user_id, streak_start;

LAG-based equivalent. Instead of arithmetic on the date, compare each row directly to the previous one: flag a new streak whenever activity_date <> prev_date + 1, then take a running SUM of that flag as the group id. This produces the identical grouping, at the cost of one extra window pass; it generalizes more naturally when the gap rule is not a fixed "+1 day" (see below).

Worked example (executed in DuckDB). User 1's activity dates: Jan 1, 2, 3 (a 3-day streak), then Jan 5, 6 (a 2-day streak after a 1-day gap), then Jan 10 (an isolated day).

user_id | streak_start | streak_end | streak_length
1       | 2025-01-01   | 2025-01-03 | 3
1       | 2025-01-05   | 2025-01-06 | 2
1       | 2025-01-10   | 2025-01-10 | 1

The island_id values produced internally were three distinct dates (one per run), confirming the arithmetic correctly separated the three streaks without any explicit gap-detection logic.

Generalizing the same island logic

• Coarser granularity (3+ consecutive weeks). Replace "day" with "week": truncate each activity date to its week start (e.g. date_trunc('week', activity_date)), dedupe to one row per (user, week), then apply the identical date - row_number trick using INTERVAL '1 week' instead of '1 day'. The mechanism is unchanged; only the unit of contiguity changes.
• A per-user variable gap threshold. If "consecutive" means something other than a fixed 1-day gap per user (e.g. some users are only expected to be active every other day), the date-minus-row-number arithmetic trick stops applying cleanly, because it depends on the gap being a fixed, known constant. Switch to the LAG-based form and compare against a per-user threshold column instead of a literal + 1: CASE WHEN activity_date > prev_date + gap_threshold THEN 1 ELSE 0 END.
• A tolerance window on the contiguity test. If a single missed day should still count as "the same streak" (a grace-day rule), change the LAG comparison from <> prev_date + 1 to > prev_date + tolerance_days, i.e. only break the streak when the gap exceeds the tolerance, not on any gap at all.
• The same pattern on a non-boolean series. The identical island logic applies to "3+ consecutive days of declining revenue" or "consecutive growing-revenue days": instead of flagging by date contiguity, flag each row by CASE WHEN revenue < LAG(revenue) OVER (...) THEN 1 ELSE 0 END (a direction change breaks the streak) and take the running SUM of direction-changes as the group id. The grouping mechanism (a monotonically non-decreasing counter that only increments at a boundary) is exactly the same; only the definition of "boundary" changes.

Trade-offs & pitfalls

• Deduplicate same-day activity before ranking (GROUP BY user_id, activity_date first); otherwise a duplicate row inflates streak_length without representing a real extra day.
• The date-minus-row-number trick specifically needs a fixed, known step size (1 day, 1 week); once the gap rule is conditional or per-user, fall back to the LAG-and-cumulative-sum form, which handles any boundary condition you can express as a boolean.
• date - row_number only produces a stable id within one user's partition; always include user_id in the final GROUP BY, or two different users' unrelated streaks that happen to land on the same constant will merge.

Clarifying questions (grouped, with sample phrasing):

• Definitions
  • "How does the third party define this metric (e.g., 'active user' = any session vs. unique user with ≥1 event)? Can you share their spec or SQL?"
  • "What event/ID sources are included or excluded?"
• Timezone & time window
  • "What timezone and day boundary do they use? Are we aligning on UTC vs. local?"
  • "Is the metric by calendar day, rolling 24h, or a different attribution window?"
• Filters & joins
  • "What filters do they apply (bots, internal traffic, test accounts, geos)?"
  • "Do they deduplicate across devices or use a deterministic user ID?"
• Sampling & aggregation
  • "Do they sample data or use extrapolation? If sampled, what's the sample rate and method?"
  • "How do they handle partial-day data or late-arriving events?"
• Reconciliation method & provenance
  • "Can you share a sample extract (raw rows or aggregated CSV) and their reconciliation steps?"
  • "Which source system is considered the 'source of truth' for each dimension?"

How I’d present the discrepancy and recommend next steps:

• Prepare a succinct, non-technical one-page summary: metric name, our value vs. third-party value, absolute and % difference, time range, and suspected causes (e.g., timezone mismatch, filter differences, sampling).
• Include concrete evidence: side-by-side table / small sample of raw rows, query snippets (our SQL vs. their spec), and a visualization showing divergence over time.
• Recommend an action plan with owners and timeline:
  1. Short-term: Align definitions and timezone (owner: BI & vendor) — 1 business day.
  2. Run a controlled reconciliation for one day with raw extracts and shared ID mapping (owner: BI + vendor) — 2–3 days.
  3. If sampling/latency implicated, agree on acceptable reconciliation tolerance or change pipeline (owner: Data Engineering + vendor) — 1–2 weeks.
  4. Document final agreed definition and add automated reconciliation checks to our reporting pipeline.
• Communication tone: factual, collaborative, prioritize business impact (is discrepancy material for decisions?), and propose an immediate fallback if needed (use internally-agreed metric or vendor metric with caveats).

Direct answer. Write the join condition so a NULL on the left side's key part is treated as an automatic match against ANY value on the right (l.key2 = r.key2 OR l.key2 IS NULL), and keep that wildcard logic in the join predicate itself rather than trying to fake it by coalescing NULL to a literal placeholder string, which risks colliding with a genuinely real value.

Structured elaboration. The two halves of this question are really two sides of the same judgment call about what NULL means in a composite key. Sometimes NULL genuinely means "matches anything" (a wildcard, a business rule saying "this row applies regardless of the second key part"), and an explicit OR-with-IS-NULL clause in the join condition expresses that intent directly. Other times NULL means "unknown, do not guess," and coalescing it to a literal sentinel like the string 'NULL' to force a match is dangerous specifically because that sentinel could ALSO be a legitimate value already present in the data (a SKU literally named "NULL" as a placeholder product code, say), silently merging two things that were never meant to be the same.

Worked example. left_table(id, key1, key2, value): (1, 'US', NULL, 'v1') (a wildcard row for all of key2 under 'US'), (2, 'US', 'east', 'v2'). right_table(key1, key2, price): ('US', 'east', 100), ('US', 'west', 120).

SELECT l.id, r.key2, r.price
FROM left_table l
JOIN right_table r
  ON l.key1 = r.key1
 AND (l.key2 = r.key2 OR l.key2 IS NULL)
ORDER BY l.id, r.key2;

Result: (1, 'east', 100), (1, 'west', 120), (2, 'east', 100). Row 1 (the wildcard, key2=NULL) correctly matches BOTH right-side rows since its key2 is NULL, while row 2 (key2='east') matches only the one exact right-side row it specifies.

Trade-offs and pitfalls. The danger case for the counterpoint: imagine a real SKU is legitimately coded as the literal string 'NULL' by an upstream system's bug or convention. If you'd instead written COALESCE(l.sku, 'NULL') = COALESCE(r.sku, 'NULL') to make actual NULLs match each other, that real SKU would now be silently treated as identical to every genuinely-missing SKU, merging unrelated rows. The wildcard pattern above avoids this entirely because it never manufactures a fake value to compare against; it just tests IS NULL directly. Before implementing either pattern, confirm with whoever owns the business rule which meaning NULL is actually supposed to carry here, since the two interpretations produce very different (and both silently plausible) results.

A strong candidate writes this directly:

SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Structured elaboration

The clause order to reason about, and the order the database actually evaluates it in, are different things worth keeping straight:

• FROM picks the table.
• WHERE filters rows before anything else happens (status = 'active').
• ORDER BY sorts the filtered rows.
• LIMIT trims the sorted result to the first N rows.

Selecting only the columns you need (id, name, email instead of *) keeps the result narrow and avoids breaking callers if the table later gains columns.

Worked example

Given rows (1,'Ann',...,'active','2025-01-05'), (2,'Bo',...,'inactive','2025-01-06'), (3,'Cy',...,'active','2025-02-01'), (4,'Di',...,'active','2025-03-01'), the query returns Di, Cy, Ann in that order: the inactive row (Bo) is filtered out first, then the three active rows are sorted newest-first.

Trade-offs and pitfalls

LIMIT without ORDER BY gives you an arbitrary 100 rows, not "the most recent 100"; the two clauses have to travel together. If created_at can be NULL, decide up front whether those rows should sort first or last (see NULLS LAST). At real scale, LIMIT 100 with no index on (status, created_at) still requires scanning and sorting every active row before truncating, so this is the point where an index recommendation is a reasonable follow-up, not a requirement of a correct answer.