Spotify Business Intelligence Analyst Interview Preparation Guide - Mid Level (2-5 years)

Direct answer

My first week is mostly about removing blockers and building context fast: get every access and tooling item sorted on day one, meet my manager and immediate teammates one on one, and aim to have one small, concrete thing done by Friday.

Framework

• Day 1: sort access and logistics (accounts, repo or tool access, calendar, communication channels), because every day without access is a day I can't be productive, and get a first 1:1 booked with my manager.
• Days 1-2: meet my manager first, asking what they most want me to understand this week, then my closest teammates individually rather than only in a group standup.
• Days 2-4: pair reading with doing, shadow a task or attempt a small real piece of work with someone available to unblock me when stuck, rather than only passively reading documentation.
• By Friday: something small and concrete, either a documented list of open questions and assumptions to confirm in next week's 1:1, or a genuinely tiny shipped item (a small bug fix, a first draft, a completed setup task). The goal isn't impact, it's proof I can navigate the environment and follow through.
• For a more junior hire, I'd set explicit measurable daily goals (day 1: access plus two intros, day 2: three more intros plus the onboarding doc, and so on), since more structure helps someone very new.

Worked example

Starting as a software engineer: day 1, repo access and laptop set up, first 1:1 booked for day 2. Day 2, manager 1:1 surfaces the team's current top priority. Days 2-3, individual meetings with all four teammates, asking each "what's something you wish you'd known your first week." Days 3-4, paired with a teammate on a small, well-scoped ticket. By Friday, that ticket is open as a draft pull request, even if not yet merged, alongside a written list of five open questions sent to my manager ahead of the following week's 1:1.

Trade-offs and pitfalls

Spending the whole week passively reading with nothing to show can read as low initiative even if you were genuinely working. Trying to ship something too ambitious before you understand the system risks breaking something you don't yet know about. A very senior hire might legitimately spend more of week one purely listening and producing fewer artifacts, the right balance shifts with seniority and how well-documented the team already is.

Direct answer

Connect each roadmap item to a company objective through a written causal chain: item, user behaviour it changes, team metric that moves, company-level number that moves as a result. If I cannot write that chain in two sentences with a plausible size, the item does not ship this cycle. I make this a routine step in planning, not a one-time exercise.

Terms

OKR (objectives and key results) is a planning format: an objective states a qualitative aim, and key results are measurable outcomes that show it was reached. A roadmap is the ordered list of initiatives the team will build. An item "ladders up" when each step of cause and effect leads to the next level above it, ending at a company objective. Expansion means existing customers spending more (extra seats, a higher plan); net revenue from existing customers is that expansion minus what is lost to cancellations and downgrades; a renewal is the date a customer decides whether to continue. A holdout is a group deliberately kept on the old experience as a comparison, and a guardrail is a metric that must not get worse while you chase the main one.

The process

1. Start from the company objective and its constraint, in words: for example, "Grow revenue from existing customers this year."
2. Identify the mechanism your team can influence. What user behaviour, if it changed, would move that objective?
3. Write the ladder for each candidate item and size it roughly.
4. Test each item against three questions: does it move the key result directly? Is the cause-and-effect plausible, with evidence (data or research) rather than hope? Is this the cheapest way to move it?
5. Rank, cut and write down the assumptions so leadership can see what the alignment depends on and revisit it when a number changes.

Worked example: a product team that owns customer onboarding for a business software product

Sizing the item (illustrative)

Assume 1,000 new accounts a month. Moving setup completion from 40% to 55% means 550 instead of 400 complete it: 150 more accounts a month. Suppose past data show 25% of accounts that complete setup expand within 90 days against 10% of those that do not, with an average expansion of $2,000 a year. Each extra completing account then adds 0.15 expansions: 150 x 0.15 = 22.5 expansions x $2,000 = $45,000 of annual expansion revenue per monthly cohort, about $540,000 across twelve cohorts. Accounts that set up well may have expanded anyway, so I would count only half until the holdout confirms the effect: about $270,000 a year. Against a build cost of 12 person-weeks at an assumed $2,400 loaded cost per week ($28,800), the item clears the bar; an item with no such chain has no number to set against its cost.

What I would drop, and why

Suppose the backlog also holds "redesign the settings page" and "add a dark theme". Neither has a link to the setup milestone or to expansion revenue; they are legitimate but belong to a different objective. I would drop or defer them from this cycle's commitments, tell the requesters why, and keep them in a visible parked list. Another to drop: an item that ladders up in a story but whose evidence is only an executive's preference. It stays on the list until someone supplies a test or data.

When team metrics conflict

Say a growth team is measured on sign-ups and a support team on ticket volume. More sign-ups bring more tickets, so each team's win hurts the other. Resolve by agreeing a shared outcome both feed (retained, active accounts), keeping each team's metric as a guardrail for the other, and escalating tie-breaks to the owner of the shared outcome. Document assumptions in the plan (what each number is expected to do and what would change our mind), so alignment can be validated later instead of re-argued.

What a well-aligned function looks like in concrete outcomes

Each team can name the one company number it moves; leadership can point to items that were cut because they did not ladder up; metrics are reviewed on a schedule and the roadmap changes when the evidence does; teams rarely argue about priorities because the ordering criteria are shared.

Pitfalls

Forcing every item to ladder (maintenance and reliability work has a case on risk, which should be stated, not hidden); key results that measure activity, not outcome; objectives so broad everything fits.

Situation: Leadership asked my team to both fix multiple broken executive reports immediately and invest in pipeline reliability to prevent repeat incidents. Both were high-visibility priorities from different stakeholders.

Task: I had to deliver urgent fixes so business users could make decisions today, while ensuring we reduced recurrence risk through longer-term pipeline improvements.

Action:

• I triaged issues within hours, classifying each as “blocker” (incorrect KPIs), “partial” (performance/latency), or “cosmetic.” For blockers I applied safe, reversible hotfixes — e.g., corrected aggregation logic in the transformation layer and reloaded affected datasets — then validated with a focused smoke-test and stakeholder sign-off.
• Simultaneously I created a short, visible reliability roadmap: root-cause analysis for each blocker, prioritized tech-debt items (idempotent loads, type-safe schemas, end-to-end tests), and estimated effort and impact.
• I negotiated with leadership for a split: 70% immediate operational response for 48–72 hours, then a protected 20% sprint capacity for strategic work each two-week sprint. I proposed measurable goals (reduce report incidents by 80% in 3 months, add CI tests covering 90% of critical transformations).
• Instituted quick wins: automated alerting for data freshness, added lineage metadata to reports, and a changelog for data-model updates so future hotfixes are faster.
• Communicated transparently: daily status to execs during remediation; weekly roadmap updates for reliability progress.

Result: Reports were restored within one business day; business decisions continued uninterrupted. Over three months, incidents dropped by ~70%, mean time to detect fell from 8 hours to under 1 hour, and stakeholder confidence improved. The balance of immediate fixes and protected investment created durable improvements while honoring urgent needs.

This approach ensures urgent business needs are met without deferring necessary investments in pipeline reliability, by using triage, measurable trade-offs, protected capacity, and transparent communication.

Direct answer

One practical way to detect early product-market fit signals is to look at 30-day retention within acquisition-week cohorts: if a meaningful and growing share of each new cohort is still active a month later, and that share holds up or improves as more cohorts are observed, that is a reasonable early heuristic that the product is delivering repeatable value rather than a one-time novelty.

Structured elaboration

The cohort dimension to use is acquisition week, because it lets you compare successive groups of new users on equal footing (same amount of elapsed time since joining) rather than comparing an aggregate metric that mixes users at very different points in their lifecycle. The metric to pair with it is either 30-day retention or the percentage of a cohort completing the product's core action at least once in a defined follow-up window, whichever better reflects genuine repeat value for that specific product.

A simple threshold heuristic: if 30-day retention for successive weekly cohorts is trending upward, or at minimum holding flat above a level the team considers meaningfully better than a typical unengaged baseline for the category, that is treated as an early positive signal. The threshold itself is necessarily product-specific (a reasonable bar for a daily habit product looks nothing like a reasonable bar for an infrequently-used utility), so a team usually calibrates it against comparable products in the same category rather than a universal number.

Worked example

Suppose a new note-taking app tracks 30-day retention for its first six weekly signup cohorts: 8%, 11%, 14%, 13%, 17%, and 19%. Even though each individual number is modest in isolation, the upward trend across six consecutive cohorts, rather than a flat or declining line, is itself informative: it suggests something about the product or its onboarding is genuinely improving cohort quality over time, which is a stronger early signal than any single cohort's absolute retention number. By contrast, six cohorts showing 15%, 12%, 16%, 11%, 14%, 13%, hovering with no clear trend, would be a weaker signal even at a similar average level, since it looks more like noise around a stable (and possibly weak) baseline than evidence of improving fit.

Trade-offs and pitfalls

Early cohorts are small by definition, so a trend across only a handful of weekly cohorts can be noisy; treating six data points as a confirmed trend rather than a suggestive early read risks over-claiming certainty the sample size does not support. It is also easy to conflate a genuinely improving product with an improving ACQUISITION mix (later cohorts skewing toward higher-intent users because of a change in where signups are coming from), so a careful read checks whether the acquisition channel mix has stayed roughly constant across the cohorts being compared before crediting the product itself for the trend.

Direct answer

Store customer addresses as a small, append-only history table (one row per address version, with an effective date range) rather than a single mutable row, so "current address" and "address at time T" are both simple, indexed queries against the same table, and deletion/retention can target specific historical rows without losing the ability to reconstruct any point in time still within the retention window.

Structured elaboration

CREATE TABLE customer_addresses (
  address_history_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  line1 TEXT NOT NULL,
  city TEXT NOT NULL,
  postal_code TEXT NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP     -- NULL means this is the current address
);
CREATE INDEX idx_addr_customer_range ON customer_addresses (customer_id, valid_from, valid_to);

• Current-address lookup: SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_to IS NULL.
• Historical audit of changes: SELECT * FROM customer_addresses WHERE customer_id = ? ORDER BY valid_from, which shows every address the customer has ever had, in order.
• "Address at time T": SELECT * FROM customer_addresses WHERE customer_id = ? AND valid_from <= ? AND (valid_to IS NULL OR valid_to > ?), the same point-in-time range pattern used for any effective-dated attribute.
• Efficient deletion/retention: because each address version is its own row with its own valid_from/valid_to, a retention policy ("delete address history older than 7 years") can target and delete specific old rows directly (DELETE WHERE valid_to < now() - interval '7 years') without disturbing the current or more recent rows, which a single mutable-column design with no history at all could never support in the first place, since it never had the history to selectively retain or discard.

Worked example

When a customer updates their address, the write is two steps in one transaction: UPDATE customer_addresses SET valid_to = now() WHERE customer_id = ? AND valid_to IS NULL (close out the previous current row), then INSERT the new row with valid_from = now() and valid_to = NULL. This keeps exactly one "current" row per customer at all times (enforceable with a partial unique index, CREATE UNIQUE INDEX ON customer_addresses (customer_id) WHERE valid_to IS NULL), while preserving every prior address indefinitely (or until the retention policy removes it).

Trade-offs and pitfalls

• The storage and query cost of this design is proportional to how often a customer's address actually changes, which for most customers is rare; the cost is well justified given the alternative (a mutable single row) cannot answer "address at time T" or support any auditable history at all, both of which are commonly real requirements (fraud investigation, compliance, shipping-dispute resolution).
• The two-step "close out the old row, insert the new one" write must happen in a single transaction; if a crash or bug leaves a customer with two "current" rows (valid_to IS NULL on both) or zero, downstream queries assuming exactly one current row will misbehave, which is exactly why the partial unique index enforcing "at most one current row per customer" is worth adding as a schema-level safety net, not just relying on application-level discipline.
• Retention and deletion interact with the historical-audit requirement in tension: deleting old rows to satisfy a retention policy permanently forecloses any future "address at time T" query into the deleted range, so the retention window itself needs to be a deliberate compliance/business decision, not an arbitrary storage-saving default.

Direct answer

Lead with the outcome, not the buildup: state plainly, in one sentence, that the feature won't be ready, before you explain why. Burying the bad news inside context reads as either hiding it or not being sure of it. Then absorb the reaction without getting defensive, and move quickly to what you can actually offer instead of dwelling on what you can't.

Structured elaboration

The move is to separate acknowledging the reaction from agreeing with the demand. You can validate that someone's frustration is legitimate without agreeing to whatever they're asking for in the heat of the moment.

1. State the headline first: what's not going to be ready, and by when you'll know more if anything is still uncertain.
2. Acknowledge the reaction directly and specifically, not with a generic "I understand." Name what's actually at stake for them.
3. Give the real reason in one or two sentences, without turning it into a justification essay or blaming a teammate. A reason that reads as an excuse invites more escalation, not less.
4. Pivot to what you can offer: a partial version, a working prototype, a firm revised date, something concrete they can take back to whoever they answer to. Showing up with nothing but the bad news forces them to do the problem-solving themselves, which is usually what produces the "I'm escalating this" reaction in the first place.
5. If they still want to escalate after a real alternative is on the table, don't fight it, help them escalate with the same facts and options you just gave them, rather than let a separate, less accurate version of the story reach leadership first.

Worked example

You have to tell a stakeholder, two days before a customer demo, that a promised integration isn't stable enough to show live. They say they're going to "take this straight to your VP." You open with: "the integration isn't going to be demo-ready by Thursday, the failure rate is too high under load to show it live." You acknowledge the stake: "I know this demo was the reason the customer meeting got scheduled this week." You give the reason in one sentence: an upstream API's rate limits turned out to be lower than documented. You offer the alternative: a recorded walkthrough of the working parts plus a live Q&A instead of a live demo, with a firm date for the real thing. If they still want to escalate, you say "that's fair, let's loop in [VP] together so the facts are consistent," rather than letting them go alone.

Trade-offs and pitfalls

• Softening the headline into vague language ("there might be some challenges") to delay the reaction usually makes the eventual reaction worse, because it reads as having known longer than you admitted.
• Offering an alternative you can't actually deliver, just to make the moment easier, creates a second, bigger version of the same conversation later.
• Treating "they threatened to escalate" as something to prevent at all costs can push you into overpromising. Sometimes escalation is the right outcome, and your job is making sure it happens with accurate facts.
• Getting pulled into re-litigating whose fault the delay is turns a bad-news conversation into a blame conversation, which rarely changes the timeline and always costs trust.

An exact `COUNT(DISTINCT email) = COUNT(*)` check requires a full scan and a large in-memory or spill-to-disk distinct set, which doesn't hold up at billions of rows; approximate cardinality algorithms trade a small, bounded error for a check that runs in a fraction of the time and memory.

Approaches

• HyperLogLog / APPROX_COUNT_DISTINCT: gives an estimated distinct count with typically well under 2% error using a small fixed-size sketch, regardless of table size; compare it against the total row count to estimate a duplication rate without ever materializing the full distinct set.
• Bloom filters: build one incrementally as you scan; a bloom filter answers "have we seen this exact value before" with no false negatives (only a tunable false-positive rate), letting you flag CANDIDATE duplicates cheaply and confirm the smaller candidate set exactly.
• Partitioned verification: if the table is partitioned, verify uniqueness within each partition cheaply and only check across-partition uniqueness for keys that could plausibly repeat (for example if the partition key is unrelated to the uniqueness key).

Worked example

On a 5 billion row table, `APPROX_COUNT_DISTINCT(email)` typically returns an estimate within roughly 1-2% of the true distinct count while scanning the same data far faster than an exact `GROUP BY`, because it never needs to hold the full set of distinct values in memory.

Trade-offs and pitfalls

An approximate check can tell you "roughly how much duplication exists" but cannot point to which specific rows are the duplicates; if you need to actually remediate, you still need an exact pass, just possibly limited to the range the approximate check flagged as suspicious. The same underlying sketch techniques (HyperLogLog for cardinality, t-digest for quantiles) generalize well beyond uniqueness checks, into any large-scale distribution comparison where an exact answer is too expensive to compute routinely.

Direct answer
A strong answer names a specific dashboard, states clearly who the audience was and what business question it answered, walks through the concrete visualization and validation choices made, and ends with a specific decision the dashboard influenced and what changed after stakeholder feedback.

Structured elaboration

• Audience and business question: name the actual stakeholders (e.g. a regional sales leadership team) and the specific question the dashboard needed to answer (e.g. "where is pipeline coverage insufficient for next quarter's target").
• Visualization and validation choices: describe why a particular chart type was chosen over alternatives, and what steps were taken to validate the underlying data (reconciling against a source system, catching an early data-quality issue) before it shipped.
• A decision it influenced: state a specific action taken because of the dashboard (e.g. reallocating budget to an underperforming region, or catching a pricing error before it compounded), not just "it was viewed a lot."
• A change after feedback: describe one concrete iteration (e.g. adding a filter stakeholders repeatedly asked for, or simplifying a chart that was initially too dense) that shows the dashboard evolved based on real usage, not that it was built once and left untouched.

Worked example
A regional sales pipeline dashboard built for a VP of sales: chose a bullet-chart layout for pipeline-coverage-vs-target per region (chosen over a plain bar chart because it needed to show both the actual value and a qualitative threshold band at once); validated the pipeline totals against the CRM's own reporting before launch and caught a stage-mapping bug in the process; the VP used it to reallocate a rep from an over-covered region to an under-covered one the following week; after initial feedback that the page was too dense, cut it from 12 tiles to 6 and added a region filter.

Trade-offs and pitfalls
A generic answer ("I built a dashboard and people liked it") reads as unpracticed; the strongest version of this answer is specific about the CHOICE made and the ALTERNATIVE rejected at each step, not just a description of the finished artifact.

Approach summary: treat this as an optimization project — identify hotspots, choose the minimal-invasive rewrite with best ROI, implement iteratively, and validate both correctness and performance with benchmarks and tests.

1. Diagnose first

• Use Tableau Performance Recorder and workbook “Start Performance Recording” to capture query times, VizQL and rendering breakdown.
• Identify slow queries in Tableau Server/DB logs and the specific worksheets using nested table calcs/LOD expressions.

2. Move to extracts (when to use)

• Good if source DB latency or concurrency is an issue and data freshness can be relaxed (daily/hourly).
• Use hyper extracts with incremental refresh and proper indexing; enable aggregate tables in extract options.
• Trade-off: extracts reduce live-query cost and improve render time, but add latency to freshness and extra storage/refresh jobs.

3. Pre-compute aggregates in the warehouse

• Create materialized views or aggregated tables that match dashboard grain (e.g., daily customer-channel aggregates).
• Best when heavy joins, wide tables, or expensive calculations are repeated across dashboards.
• Trade-off: more ETL work, maintenance overhead, possible schema proliferation; but gives deterministic SQL performance and easier query plans.

4. Replace nested table calculations with LODs / database aggregations

• Use FIXED LODs when the logic is independent of viz partitioning; use INCLUDE/EXCLUDE to match required grain.
• Avoid table calcs for business logic because they depend on view layout and sorting — fragile and client-side.
• Trade-off: LODs can be expensive on live connections over huge tables; prefer when run on extracts or materialized aggregates.

5. Push calculations upstream

• Implement complex logic (e.g., cohort windows, running sums) in SQL/ETL using window functions; expose as ready fields.
• Trade-off: requires engineering effort and testing but simplifies Tableau and reduces client-side work.

Validation and testing

• Build an A/B benchmark: copy workbook, apply one optimization at a time (extract, then pre-agg, then LOD rewrite) and measure:
  • Load time percentiles (p50, p90, p99), query execution time, VizQL and rendering times.
• Functional correctness:
  • Create unit tests: compare key metric outputs (sampled rows and aggregates) between original and optimized workbook across multiple filter combinations and sorts.
  • Use controlled datasets (small synthetic dataset) where expected results are known, then run full-scale comparisons via SQL queries to assert equality/tolerance.
• Monitor in production: instrument Tableau Server metrics and set SLAs (e.g., dashboard loads <3s p90). Roll back if discrepancies detected.

Final notes

• Prioritize low-effort, high-impact changes: extracts + pre-aggregates for repeated heavy queries; move business logic upstream for long-term maintainability; replace view-dependent table calcs with LODs only when semantics align. Document assumptions and maintain test suite for dashboard validation.

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.