Microsoft Business Intelligence Analyst (Staff Level) Interview Preparation Guide

Direct answer

Data mesh is trying to solve an organizational bottleneck, not a technical one: in a centralized warehouse, one platform team becomes the sole gatekeeper for every domain's data, and as the company grows, that team cannot scale its domain knowledge or its throughput fast enough to keep every business unit unblocked. Data mesh fixes this by making each domain team responsible for publishing its own data as a well-defined, discoverable, quality-guaranteed "data product," coordinated only by shared interoperability standards rather than a single team's backlog. What you give up is exactly the thing centralization was good at: one team enforcing consistent modeling discipline and conformed dimensions everywhere, which a mesh instead has to achieve through governance and standards that are far easier to state than to actually enforce across many independent teams.

Structured elaboration

The problem data mesh solves. As a company adds domains (finance, marketing, logistics, each with deep, changing domain knowledge), a single centralized platform team increasingly becomes a queue: every new mart, every schema change, every new source integration waits on that one team's capacity, and that team's members are rarely domain experts in all the areas they are modeling data for. Data mesh addresses this by pushing ownership of the DATA out to the domain teams that already understand it best, while the platform team's job shifts to building and operating shared self-serve infrastructure (a common cataloging, access-control, and quality-testing layer) rather than owning every dataset itself.

What a well-run centralized warehouse already solves, and does not need mesh to fix. If an organization is small enough, or disciplined enough, that a single platform team can genuinely keep up with every domain's needs, a centralized warehouse's core advantage, one team enforcing conformed dimensions and one place to look for the "official" definition of a metric, is not a problem data mesh needs to solve, it is a benefit you would be trading away.

What you give up. Conformance discipline is the main casualty: in a centralized model, one team can simply refuse to publish a customer dimension that does not match the existing conformed one. In a mesh, each domain team owns and can independently evolve its own data product, so achieving the same cross-domain consistency requires the interoperability standards (naming conventions, shared identifiers, data contracts, quality SLAs) to be genuinely enforced, typically through automated checks in the shared platform rather than a single team's review, and that enforcement machinery is itself a significant, ongoing investment.

When to recommend against it. A small or early-stage company (the kind of organization discussed when deciding whether it needs a formal warehouse at all) does not have enough distinct domains or organizational scale for a mesh's coordination overhead to pay for itself; a single platform team can still serve everyone directly and faster than standing up domain teams and shared self-serve infrastructure would. A company without the engineering maturity to build and operate the shared interoperability platform a mesh depends on will end up with the worst of both worlds: decentralized ownership without the standards that were supposed to keep it consistent, which is functionally the four-un-conformed-dimensions governance failure, just organized around teams instead of ad-hoc mart builds.

Worked example

A company with one warehouse team serving five departments starts taking two weeks to review and approve every new mart request, and departments start building their own disconnected spreadsheets to route around the bottleneck, exactly the fragmentation a warehouse was meant to prevent in the first place. Moving to a mesh does not remove the need for a customer identifier every domain agrees on: it moves the enforcement of that agreement from "the one team reviews everything" to "the shared platform automatically validates every published data product against a data contract that specifies the agreed identifier, format, and quality checks," which only works if that contract-validation infrastructure actually gets built and maintained, not just proposed.

Trade-offs and pitfalls

The most common mistake is adopting the language of data mesh (domain ownership, data products) without building the shared self-serve platform underneath it, which produces decentralization with none of the standards enforcement that made the model viable in the literature; the result is usually worse consistency than the centralized warehouse it replaced. The second common mistake is treating this as an all-or-nothing choice: many organizations run a hybrid where a small number of genuinely cross-cutting dimensions (customer, date, product) stay centrally owned and conformed exactly as in a Kimball bus architecture, while domain-specific facts and less-shared dimensions are pushed out to domain ownership.

Direct answer

Resolve the conflict by treating "active user" as a versioned, explicitly-named metric in the conformed dimensional model rather than a single ambiguous column: define each team's variant as its own named metric with clear ownership and lineage, surface which definition each dashboard uses, and drive toward a single canonical default definition for genuinely cross-team reporting while allowing named exceptions where a team's variant is truly justified.

Structured elaboration

• Process: convene the disagreeing teams, surface each team's actual DEFINITION (not just their number) and why they need it that way (a "monthly active" definition for billing purposes is legitimately different from a "weekly active" definition for engagement tracking), and distinguish genuine business-need differences from accidental, historical inconsistency.
• Schema approach, versioned metric definitions: implement each surfaced definition as its own explicitly named metric (active_user_billing, active_user_engagement) in the semantic layer or metrics catalog, computed from the same underlying conformed user_dim and activity_fact, rather than as separately-maintained, potentially-diverging pipelines.
• Lineage: make each named metric's definition and its underlying SQL/logic visible and discoverable, so any dashboard consumer can see exactly which "active user" definition a given number represents, rather than a bare "Active Users" label with no indication of which team's definition it's using.
• Driving toward consensus: for reporting that genuinely needs ONE company-wide number (a board-level metric, for instance), lead a process to agree on a single canonical default definition, while still allowing the named team-specific variants to coexist for their legitimate internal purposes, clearly labeled as such.

Worked example

Marketing's "active user" counts anyone with a single app open in 30 days; billing's counts only paying customers with a qualifying usage event in the billing period. Rather than forcing one definition on both, the metrics catalog defines active_user_engagement and active_user_billing as two separate, explicitly named, independently governed metrics, both computed from the same activity_fact and user_dim, and every dashboard using either one displays which definition it's using. A quarterly board metric is separately agreed as a specific canonical definition, distinct from either team's operational metric, chosen deliberately for that purpose.

Trade-offs and pitfalls

The tempting shortcut, picking one team's definition and forcing everyone to use it, usually just pushes the disagreement underground (the losing team builds a shadow metric outside the governed system) rather than resolving it. Explicitly naming and governing multiple legitimate definitions, with visible lineage, costs more upfront design work but avoids both the shadow-metric problem and the "silent redefinition" problem that erodes trust in the metrics catalog over time.

Situation: As lead BI analyst for a company adopting Tableau, Power BI and Looker, I would establish a centralized BI data governance program to ensure trustworthy, discoverable, and well-governed metrics and datasets across tools.

Program components and policies:

• Metric stewardship
  • Appoint metric stewards (one per domain e.g., Sales, Finance) responsible for definitions, ownership, calculation SQL, and business context.
  • Enforce canonical metric registry: unique IDs, version, approved formula, owner, last-reviewed date.
• Metadata catalog & lineage
  • Deploy a catalog (e.g., Collibra/Alation or open-source + dbt docs) ingesting metadata from BI tools, data warehouse, and ETL.
  • Automatic lineage from source → transformation → dataset → dashboard; editable annotations for business context.
• Dataset certification
  • Certification tiers: Draft, Validated, Certified, Deprecated. Certification requires unit tests, data quality checks, steward approval, and documentation.
• Access request process
  • Self-serve requests via ticketing integrated with IAM; role-based access templates (viewer, analyst, admin). Justification, data classification and approval flow to steward/security.
• Freshness SLAs
  • Define SLA per dataset (real-time/near-real-time/daily/monthly) exposed in catalog; automated SLA monitoring and alerts to stewards.
• Change control
  • Change proposals for metric/dataset updates via PR-like process: impact analysis (downstream lineage), stakeholder review, staging preview, scheduled deployment and rollback plan.
• Adoption plan
  • Launch in phases: pilot with 2 domains → expand. Training (steward workshops, office hours), docs, templates, and a “trusted dataset” badge in BI tools. KPIs dashboards to show usage and quality.

Measuring success (KPIs):

• % of core metrics with certified definitions (target 90% in 12 months)
• Catalog coverage: % datasets inventoried and lineage captured
• Mean time to grant access and % automated approvals
• SLA compliance: % datasets meeting freshness SLAs
• Reduction in duplicated reports/datasets and time analysts spend finding trusted data
• User satisfaction (NPS) and adoption: active users of certified datasets, reduction in ad-hoc metric disputes

This program balances governance with analyst productivity by making trust and discoverability the default while keeping lightweight, automated processes and clear steward responsibilities.

Direct answer
Autonomy should track demonstrated judgment in a specific domain, not tenure or title, and it should be granted and withdrawn through visible, structural mechanisms, not just a private mental model of how much you trust someone. As someone grows from junior to senior, both the default level of guidance and the criteria for changing it should become more explicit, not less.

What determines the level, not just the person's level

• Domain-specific, not global: someone can have earned full autonomy in one area (their core service) and need more guidance in an adjacent one (security-sensitive changes) they haven't touched before. Treating autonomy as a single dial per person rather than per domain misjudges both directions.
• Base it on evidence: track record of decisions in that specific domain, not just general seniority or how long they've been on the team.

The conversation isn't enough, structure it

• Guidance and autonomy shouldn't live only in how much you check in; they should be encoded in the system itself. Concretely: mandatory review gates on certain categories of change, feature flags that let risky work ship dark before it's fully trusted, and automated checks (tests, linting, policy gates) that catch the class of mistake a specific person is prone to, rather than relying on a human remembering to look for it.
• This matters especially early: a junior engineer with a mandatory review gate on production-config changes isn't being distrusted personally, the system is compensating for a domain they haven't yet built judgment in, and that's a much less fraught conversation than "I don't trust your judgment yet."

Moving the dial, in both directions

• Define, in advance, what "graduating" out of a guardrail looks like: a number of changes in that domain reviewed without a significant issue, or a specific type of decision made correctly under supervision. Vague criteria ("when I feel comfortable") makes the process feel arbitrary to the person on the other side of it.
• The dial also needs to move backward cleanly. If someone senior makes a judgment error in a domain, temporarily reintroducing a guardrail (an extra review, a smaller blast radius) shouldn't read as a permanent demotion; it should be scoped to the specific domain and have the same kind of explicit, objective path back out.

How this shifts junior to senior

• Junior: guidance is broad and mostly structural (required reviews, smaller scoped tasks, pairing), because there isn't yet enough track record to know where the real gaps are.
• Mid-level: guidance narrows to the specific domains where judgment hasn't been tested yet, while proven domains get real autonomy.
• Senior: guidance becomes mostly about the highest-blast-radius decisions (irreversible changes, cross-team commitments) rather than day-to-day execution, and the structural safeguards that remain exist because the stakes are higher, not because trust is lower.

Worked example
A mid-level engineer had strong judgment in their core service but hadn't touched the deployment pipeline before. Rather than a blanket "you need approval on everything" or "you're trusted, go ahead," the guidance was scoped to that specific gap: full autonomy on their usual work, a mandatory review plus a feature flag for anything touching the deploy pipeline, with an explicit criterion stated up front (three pipeline changes reviewed cleanly, then the mandatory review comes off for that category specifically). That made the guardrail feel like a scoped, temporary compensation for an actual gap rather than a general judgment about their competence, and removing it was a specific, visible moment rather than something that just quietly happened.

Trade-offs and pitfalls

• Treating autonomy as all-or-nothing per person, rather than per domain, either over-restricts someone who's earned trust in most areas or over-extends them into an area they haven't proven yet.
• Relying purely on personal judgment about who to trust, without structural backstops (review gates, flags, automated checks), doesn't scale past a small team and creates inconsistency that reads as favoritism.
• Leaving the criteria for regaining autonomy vague turns a guardrail into something that feels indefinite and punitive, even when it was scoped and reasonable at the start.

Direct answer. A correlation only tells you that two things move together; it says nothing about why. Three general mechanisms besides direct causation can produce a correlation: a shared confounder that drives both variables, reverse causation (the effect actually precedes the presumed cause), and plain coincidence in a noisy sample. The next concrete step is not to argue in the abstract but to ask what would happen to the correlation if you could intervene on one variable while holding everything else fixed, which is exactly what an experiment (or a well-designed quasi-experiment) lets you check.

Structured elaboration. Walk a stakeholder through each mechanism with a question they can ask themselves:

• Confounding: is there a third factor plausibly driving both series? (Ice cream sales and drowning both rise with summer heat.)
• Reverse causation: could the "outcome" actually be causing the "cause"? (Struggling users might seek out a help feature, so help-feature usage correlates with struggling, not the other way around.)
• Coincidence: how much data is this based on, and did you check other time periods or segments? A correlation computed on one quarter of noisy data is weak evidence on its own.

Once you've ruled these out qualitatively, the real test is whether the relationship survives an intervention: randomize who gets the treatment, or find a situation where assignment was effectively random (a policy cutoff, a staggered rollout) and see if the effect holds.

Worked example. Suppose weekly push notifications sent and weekly active users (WAU) are strongly positively correlated ($r = 0.82$) over the last year. A marketer wants to conclude notifications drive WAU. Before agreeing: check for a shared confounder (both notification volume and WAU tend to rise around product launches and holidays), check reverse causation (the team may deliberately send more notifications because WAU dipped, to try to recover it, which would produce a correlation with the wrong sign of causality), and check the sample (is $r=0.82$ stable across quarters, or driven by one holiday spike?). The decisive next step is a holdout: stop sending notifications to a random 10% of users for two weeks and compare WAU between that holdout and the rest.

Trade-offs and pitfalls. The most common mistake is treating "we found a plausible confounder" as proof the relationship isn't causal at all, when the correct conclusion is usually "we can't tell yet, here's how we'd find out." A second pitfall is stopping at qualitative reasoning when a cheap experiment or natural-experiment check is available; qualitative reasoning is a screening step, not a substitute for evidence.

Analytical warehouses prefer columnar formats like Parquet or ORC because most analytical queries touch a small number of columns across a huge number of rows, and columnar storage is built specifically to exploit that pattern. Row-oriented storage, by contrast, is built for the opposite pattern: reading or writing one whole record at a time.

Why columnar wins for analytics

In row-oriented storage, all the columns for a single row sit next to each other on disk, so reading even one column means reading the whole row. In columnar storage, all the values for a single column sit together instead. That single layout change explains most of the benefit:

Less data read off disk. If a query only needs three columns out of fifty, columnar storage lets the engine read only those three columns' worth of bytes; row storage would have to read all fifty columns for every row, then discard the ones it didn't need.

Compression. Values within a single column tend to be far more similar to each other than a mixed row of different columns is (a status column might have only a handful of distinct values, for instance). That similarity compresses much better, and formats like Parquet and ORC take advantage of it with column-specific encodings, which further reduces the bytes actually read from disk.

Skipping data that can't match a filter. Columnar file formats keep summary statistics (like the minimum and maximum value) for chunks of each column. If a query filters on a date range, the engine can check those statistics and skip entire chunks that fall outside the range without reading them at all, on top of the column-level savings already described.

Worked example

Consider a query that computes total revenue for one product category last month, against a wide events table with fifty columns. In row-oriented storage, computing that aggregate still means reading every column of every row in the relevant time range, most of which the query never uses. In columnar storage, the engine reads only the category and revenue columns (plus whatever it needs to apply the date filter), and if the data is organized so most chunks clearly fall outside last month's date range, it can skip those chunks entirely. The net effect is that a query touching two or three columns out of fifty can end up reading a small fraction of the bytes a row-oriented equivalent would.

Trade-offs and pitfalls

Columnar formats aren't universally better: for a workload that reads or writes entire records at a time, like an online transaction touching every field of a single order, row-oriented storage is the better fit, because columnar formats pay a real cost when you need to reconstruct a whole row from scattered column files. This is exactly why transactional (OLTP) systems still use row-oriented storage even though analytical (OLAP) systems have moved to columnar: the two are optimized for genuinely different access patterns, not one being an outdated version of the other.

Direct answer

Test whether missingness is informative by comparing the outcome (or another variable of interest) between rows where the field is missing and rows where it's present; if the missing group looks systematically different, the missingness itself is carrying signal, not just noise to be discarded. If the field is missing much more for one subgroup (a demographic group, a customer segment) than another, that's worth flagging as a potential bias risk before anyone builds on the data, not just a modeling nuisance to route around.

What this finding implies

If missingness is informative (say, customers who churned are missing "last login date" far more often than customers who didn't), the fact that it's missing is itself a usable signal, and naively dropping or blindly imputing those rows would throw that signal away. If the missingness concentrates in a specific demographic group, that's a fairness and data-collection concern that goes beyond modeling: it might mean that group is measured worse, served worse, or systematically opts out of whatever generates that field, any of which deserves its own investigation before you treat the rest of the analysis as representative of that group.

Worked example

In a churn dataset, last_login is missing far more often for customers who eventually churned than for those who didn't. Testing this concretely: compute the churn rate separately for rows with last_login present versus missing. If the missing group churns at a meaningfully higher rate than the non-missing group, that's evidence the missingness itself is informative, likely because churned customers simply stopped generating login events, which is precisely the signal a model needs, not noise to impute away. Separately, checking last_login missingness by demographic group and finding it's twice as common in one segment would be a distinct finding worth its own investigation into whether that segment is tracked less reliably.

Trade-offs and pitfalls

This is a discovery step, not a fix: once you've established that missingness is informative, deciding HOW to encode that (an explicit "missing" category, a missingness-indicator feature, or a different treatment entirely) is a downstream handling decision. Don't conflate "informative missingness" with "therefore fine to leave unaddressed": informative missingness that concentrates in a protected group is a red flag worth escalating, not a convenient feature to exploit without further thought.

Situation: Marketing and Finance disagree on conversion attribution—marketing prefers last-click, finance prefers first-touch—blocking a single company-wide conversion metric used in executive dashboards and budget decisions.

Task: As BI analyst I needed to facilitate alignment, quantify impact of each method on reported conversion rates and revenue, recommend a standard, and document the definition and process.

Action:

• Convene a short cross-functional workshop with reps from Marketing, Finance, Product and a data engineer. Clarify business questions each cares about (e.g., tactical campaign performance vs. budgeting/forecasting).
• Define candidate attribution rules explicitly: last-click within 30 days, first-touch on initial channel, and a third multi-touch (linear) for sensitivity.
• Produce an impact analysis: run SQL/ETL to compute conversion_rate and revenue_by_channel under each rule for the last 6 months. Example SQL skeleton:

WITH touches AS (...), conversions AS (...)
SELECT channel, COUNT(DISTINCT conversion_id) AS conversions,
       COUNT(DISTINCT user_id) AS users,
       COUNT(DISTINCT conversion_id)::float / NULLIF(COUNT(DISTINCT users),0) AS conv_rate
FROM attribution_logic -- parameterized for first/last/linear
GROUP BY channel;

• Visualize differences in a comparative dashboard (side-by-side charts, delta tables, and cohort drilldowns) and highlight material divergences affecting budget or KPIs.
• Facilitate decision criteria: alignment to business use-case, auditability, reproducibility, and systems constraints (data retention, touch granularity).
• Recommend a pragmatic standard: adopt last-click for campaign-level tactical reporting (marketing dashboards) and first-touch for high-level financial cohort/revenue recognition, or—preferably—declare a single canonical "company conversion" (e.g., last-click 14-day window) used in executive reports, while exposing both views for specific needs. I include a proposal that if differences materially change decisions (>10% delta), finance and marketing revisit attribution cadence.

Result / Documentation:

• Produce a one-page Attribution Policy: definition, time window, tie-break rules, SQL/LookML snippets, data sources, refresh cadence, owner (BI), and governance process for future changes.
• Add the chosen attribution logic as a parameterized view in the data warehouse and build a dashboard toggle to compare attribution models.
• Schedule quarterly review and ensure changes require stakeholder sign-off and versioned documentation in the analytics wiki.

This approach balances quantitative evidence, stakeholder needs, and operational feasibility while creating a transparent, auditable single source of truth.

Direct answer. Compare Snowflake, BigQuery, and Redshift on five axes: architecture model (how compute and storage separate), concurrency handling, pricing model, storage format support, and operational overhead. There is no universal winner; the right choice depends on your team's existing cloud, your query concurrency profile, and how predictable your workload is.

Structured elaboration.

Three platform-specific units in that table are worth defining plainly, since the question is explicitly asking about concurrency handling and pricing: a Snowflake compute credit is its per-second billing unit for warehouse compute, so a bigger or longer-running warehouse simply burns credits faster. A BigQuery slot is the platform's unit of parallel query-processing capacity; the "shared slot pool" is the pot of these units Google draws from to run your query, and a slot reservation just reserves a guaranteed number of them for you instead of sharing the pool with every other BigQuery customer. A Redshift WLM (Workload Management) queue is a named lane that routes a query to a specific, bounded share of the cluster's memory and concurrency; hitting a concurrency limit means that particular queue's lane is full, not that the whole cluster is out of capacity.

Worked example. For a fast-growing team with roughly 500 analysts running around 10,000 BI queries a day against a 10TB active dataset, concurrency handling is the deciding factor more than raw performance: Snowflake's ability to spin up independent warehouses per team or workload avoids one group's heavy queries starving another's dashboard, and its per-second billing means idle warehouses cost nothing when auto-suspended. BigQuery is an equally strong fit if the team is already GCP-native and wants zero cluster management, especially if the query pattern is bursty rather than continuously heavy, since on-demand pricing avoids paying for idle capacity at all. Redshift becomes the stronger choice when the workload is large and steady enough that reserved/provisioned capacity is cheaper than pay-per-use, or when the team already has deep AWS-ecosystem integration (IAM, Glue, Lake Formation) that reduces the value of switching platforms. At petabyte scale with a high-concurrency BI user base, total cost of ownership becomes the deciding axis rather than raw price-per-query, since the storage-versus-compute separation and auto-scaling behavior of Snowflake or BigQuery tend to avoid the manual capacity-planning overhead that a large provisioned Redshift cluster requires, while a spiky, bursty query pattern specifically favors either platform's auto-scaling over a fixed-size cluster.

Trade-offs and pitfalls. Benchmarking these platforms fairly is hard: comparing default settings without tuning distribution/clustering keys, using a dataset too small to expose real concurrency behavior, or ignoring egress and data-transfer cost between your existing systems and the new platform will all produce misleading conclusions. Vendor lock-in is real in all three directions (proprietary SQL extensions, proprietary storage formats, ecosystem integrations), so weigh switching cost alongside today's price and performance, not just today's benchmark numbers.

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.