Senior Level Business Intelligence Analyst Interview Preparation Guide - Spotify

Direct answer
Calibrate tone based on your relationship with the recipient, the channel you're using, how much is at stake, and the recipient's seniority or role relative to you, rather than defaulting to one register for everything.

Structured elaboration

• Relationship: a close, established working relationship generally tolerates more casual language than a first interaction or an external party you don't know well.
• Channel: a quick chat message naturally reads more casually than an email, and an email more casually than a formal memo or a document with a wide, unknown future readership.
• Stakes: a message tied to a real decision, a commitment, or something that could be read back later (a policy, a formal request) warrants more careful, formal wording than a routine day-to-day update.
• Audience seniority or external status: writing to a senior executive or an external customer generally calls for more formality than writing to a close peer, independent of how you'd naturally phrase it to a friend.
• When in doubt, err slightly more formal than you think you need to, especially for a first interaction or a written record that might be read by people beyond the immediate recipient; it's easier to loosen up in a follow-up than to walk back an overly casual first message.

Worked example
The same piece of information, "the deploy is delayed a day," phrased three ways depending on context: to a close peer over chat, "heads up, deploy's slipping a day, nothing dramatic;" in a status email to your manager, "I wanted to flag that the deploy is delayed by one day due to a failed test in staging; we expect to resolve it by tomorrow morning;" in a formal note to an external customer expecting the release, "We want to let you know that the release originally planned for tomorrow will now go out one day later than scheduled, due to an issue we identified during final testing. We apologize for the short delay and will confirm once it's live."

Same underlying fact, three different registers, each appropriate to who's actually reading it.

Trade-offs and pitfalls

• Being too formal with a close, established peer can read as distant or even passive-aggressive; matching the existing norm of that relationship matters as much as any general rule.
• Being too casual with someone senior, external, or in a first interaction can undercut how seriously your message is taken, regardless of the quality of the content.
• Tone is also a moving target within a single relationship over time; the right register for a new working relationship is often more formal than the register that same relationship settles into after months of regular contact.

Situation: Multiple stakeholders kept asking the same small tweak to an executive report (filter default, column rename). Each request went through ad-hoc Slack/email threads, causing repeated rework and delayed delivery.

Task: I needed an operational process to capture feedback reliably, prioritize recurring asks, and avoid duplicate iterations while keeping stakeholders satisfied.

Action:

• Introduced a lightweight Request Intake + Triage workflow:
  • Tooling: a simple Jira/ServiceNow ticket type "Report Change Request" with a templated form (business goal, intended audience, urgency, expected metric change) and an auto-link to the affected report (Tableau/Power BI/Looker).
  • Embedded Feedback: turned on in-report comments/annotations (Tableau/Looker Notes or Power BI comments) so users can request changes in context.
  • Governance: weekly 30-min Change Triage with product owners + BI to review new/duplicate requests using a prioritization rubric (impact, frequency, effort). Define SLAs: acknowledge 24h, schedule within sprint or backlog.
  • Change Log & Versioning: maintain a public confluence page and a small “report changelog” dashboard that surfaces recurring requests and their status.
  • Metricize: log duplicate requests and lead time in a BI dashboard.
• Piloted for 4 weeks and trained 10 stakeholders on submitting requests via the form.

Result:

• Reduced duplicate requests by ~60% in the first quarter (tracked via changelog).
• Mean time-to-schedule dropped from 8 days to 2 days.
• Improved transparency: stakeholders could see request status and rationale, reducing follow-ups.
• Learning: small upfront friction (templated form) paid off by enabling data-driven prioritization and fewer repetitive iterations.

This approach balances tooling (tickets + in-report comments), governance (triage + SLAs + prioritization rubric), and measurable outcomes (duplicates, lead time, stakeholder satisfaction).

Start by running EXPLAIN to get the planner’s estimated plan, then EXPLAIN (ANALYZE, BUFFERS) to execute the query and see real timings and buffer usage. Key steps and what to look for:

• Compare estimated rows vs actual rows: large discrepancies mean wrong statistics. If estimates << actual, add or increase ANALYZE frequency or create more specific statistics (ANALYZE, or CREATE STATISTICS for correlated columns), or rewrite predicates.
• Scan type: Sequential Scan vs Index Scan — seq scan is fine for large portions but unexpected seq scans on selective predicates suggest missing or non-selective indexes or poor statistics. Corrective actions: add or tune indexes (including expression or partial indexes), rewrite joins/filters, or force index usage cautiously.
• Join algorithms: Nested Loop vs Hash/ Merge Join — nested loops are cheap for small inner sets but costly when inner side is large. If you see nested loop with large actual rows, consider adding indexes on join keys, or restructuring query, or increasing work_mem to allow hash joins.
• Timing and cost hotspots: Look at actual time per node and total time — nodes with highest time are bottlenecks.
• Buffers and I/O: High shared read or temp usage indicates disk I/O or spill to disk. Increase work_mem to avoid temp files, add appropriate indexes, or improve vacuum/analyze to reduce bloat.
• Sorts: External sorts (temp files) indicate insufficient work_mem; increase work_mem or add indexes to avoid large ORDER BY sorts.
• Seq scan on partitioned tables: ensure partition pruning works (use constraints or rewrite query) or create indexes per partition.

Practical workflow: run EXPLAIN ANALYZE BUFFERS, identify node with biggest actual time and large estimate mismatch, check indexes/statistics, adjust schema or settings (ANALYZE, CREATE INDEX, increase work_mem), and re-run to confirm improvement. Document changes and add regular maintenance (VACUUM ANALYZE) for stable performance.

A single regex often does what several LIKE-with-OR patterns need multiple lines to express, at some cost to portability and, on some engines, index usage.

Structured elaboration

-- LIKE with OR
SELECT sku FROM products WHERE sku LIKE 'ABC-%-2024' OR sku LIKE '%-TEST';
-- Single regex (PostgreSQL)
SELECT sku FROM products WHERE sku ~ '^ABC-.*-2024$|-TEST$';

LIKE is standard SQL and portable across every engine; regex syntax (~ in Postgres, REGEXP/RLIKE in MySQL) varies by dialect and is not part of the ANSI SQL standard, so a regex-based query needs to be rewritten if you migrate engines.

Worked example

Given SKUs 'ABC-100-2024', 'XYZ-TEST', 'DEF-2023': both forms correctly return the first two SKUs and exclude the third.

Trade-offs and pitfalls

For two or three simple patterns, LIKE with OR is usually more readable and more portable; regex earns its complexity once you have many alternations or need character classes LIKE can't express (e.g., "starts with a letter, followed by exactly 3 digits"). Neither form can generally use a plain B-tree index efficiently unless the pattern has a fixed, anchored prefix ('ABC-%' can use an index; '%-TEST' cannot, since the wildcard is at the start).

I look at delivery confidence as a combination of evidence, not a gut feel.

Signals I check:

• Milestones: are we hitting key checkpoints on time, or slipping repeatedly?
• Critical path: are there unresolved items that could move the end date?
• Scope stability: is work still changing, or have requirements been settled?
• Dependency health: are product, design, platform, or vendor inputs arriving when needed?
• Team throughput: is the team burning down work (completing planned tasks at the rate the plan assumed, the way a sprint burndown chart tracks remaining work) at the expected pace?
• Risk trend: are risks being reduced, or are they aging without owners?

How I communicate it:
I avoid saying “we’re fine” or “we’re doomed.” I usually say, “Based on current scope, capacity, and dependency status, I have medium confidence in the date. The biggest variables are X and Y, and if they don’t move by Friday, confidence drops.” That is honest, specific, and actionable. In a real project, X and Y are concrete named risks rather than literal letters, for example: “The biggest variables are vendor API access and the pending legal review of the data-sharing agreement, and if they don’t move by Friday, confidence drops.”

What helps most:
I pair the status with the assumption behind it and the decision needed, so leadership understands both the probability and what would change it.

A 10x regression (2 seconds to 20 seconds) with no obvious single cause needs a systematic sweep across every layer between the user's click and the number on screen, since the same symptom (a slow-feeling dashboard) can originate almost anywhere in that chain, and guessing which layer to start with wastes time when the guess is wrong.

Systematic debugging plan

1. Frontend rendering: rule this out first, it is the cheapest check. Open the browser's network tab and compare how long the actual data request takes versus how long the page takes to become interactive after the data arrives; if the request itself is fast but rendering is slow, the regression is a frontend problem (a new chart type, more rows rendered, a JavaScript issue), not a query problem, and the rest of this plan does not apply.
2. The BI tool's generated query: if the request itself is slow, capture the actual SQL the BI tool generated for this specific view. A subtle change (a new filter added to the dashboard, a join the tool auto-generates differently after a schema change) can silently make the generated query far more expensive without anyone directly editing SQL.
3. The query engine: run that captured query's EXPLAIN (or the engine's equivalent execution-plan view) and compare it, if possible, against a plan captured before the regression. Look specifically for a changed join strategy, a missing partition prune that used to fire, or a full scan where a smaller one used to happen.
4. The caching layer: check whether this specific query (or an equivalent one) used to hit a result cache or a materialized view and no longer does, since a cache/materialized-view miss alone can account for exactly this kind of sudden, large regression with no code change at all.
5. Upstream data changes: check whether the underlying table's size, cardinality, or statistics freshness changed recently (a data volume spike, a schema change, stale statistics after a large load) in a way that would explain the query planner making a different, worse choice than it used to.

Telemetry, logs, and quick experiments

Pull the query engine's own execution history/logs for this exact query text (or its generated variants) over the days spanning the regression, most warehouses retain enough query history to directly compare a slow run against a recent fast run's plan and stats. If historical plans are not available, a quick experiment (re-running the captured query directly against the warehouse, bypassing the BI tool entirely) isolates whether the BI layer or the query itself is responsible, in under a minute.

Reducing user impact while investigating

If the caching layer is suspect, a quick, low-risk mitigation is manually warming or refreshing the cache/materialized view for this specific dashboard while the deeper investigation continues, which can restore acceptable performance immediately even before the root cause is confirmed. If a specific recent change (a new filter, a schema change) is a strong suspect, temporarily reverting just that change while investigating is often faster and safer than a partial fix applied under time pressure.

Trade-offs and pitfalls

The instinct under pressure is to jump straight to the query engine and start tuning the SQL, since that is where the most familiar levers live, but skipping steps 1 and 2 risks spending real effort optimizing a query that was never the actual problem. Work the layers in order from cheapest-to-check to most-expensive-to-check, and confirm each layer is genuinely implicated with real evidence (a captured plan, a network-tab timing) before moving deeper, rather than jumping to whichever layer feels most likely.

Direct answer: Compute each user's rank with RANK() so tied scores share a rank, then paginate by a cursor on the score value itself (keyset pagination), and expand the page past its nominal size whenever the cut-off score is tied with rows just past it. A plain cursor without that lookahead has the same failure as OFFSET/LIMIT: if you take exactly page_size rows below the cursor, a tie sitting right at the cut can still get split, and because the next page's cursor becomes "everything strictly below the last score shown," the split-off row is skipped by every later page too, not just misplaced. The fix is to look one step past the nominal page size, find the score value sitting there, and pull in every row tied with it, so the page can grow past page_size but a tie group is never divided across pages.

Approach

-- naive OFFSET/LIMIT pagination (breaks on ties)
SELECT user_id, score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores
ORDER BY score DESC, user_id
LIMIT 3 OFFSET 3;

-- keyset pagination with boundary-tie lookahead: the page includes every
-- row tied with the row sitting at the page_size cut-off, so the page can
-- grow past page_size but a tie group is never split across pages
WITH ranked AS (
  SELECT user_id, score,
    RANK() OVER (ORDER BY score DESC) AS rnk
  FROM scores
  WHERE score < :last_score_cursor   -- omit this predicate for the first page
),
boundary AS (
  SELECT MIN(score) AS boundary_score
  FROM (SELECT score FROM ranked ORDER BY score DESC LIMIT :page_size) t
)
SELECT r.user_id, r.score, r.rnk
FROM ranked r, boundary b
WHERE r.score >= b.boundary_score
ORDER BY r.score DESC;

The boundary CTE finds the score value sitting at the page_size-th position (or, if fewer rows remain than page_size, the lowest score still available). The outer query then returns every row scoring at least that high, which pulls in any rows tied with the one at the cut point, even though that pushes the page past page_size. The next page's cursor is the lowest score value actually returned on the current page, so its WHERE score < :cursor predicate always starts at the next strictly lower distinct score, never mid-tie.

Key points

• RANK() (not ROW_NUMBER()) is what makes tied scores actually show the same rank; ROW_NUMBER() would silently assign a different number to each tied user, hiding the tie from the leaderboard's display.
• The cursor for the next page is the lowest score value returned on the current page, not a row offset; the next page's WHERE score < :cursor predicate always starts at the next lower distinct score, never mid-tie.
• A page's row count is a soft target, not an exact one: the boundary CTE is what actually enforces this, by looking at the score sitting at the page_size cut-off and pulling in every row tied with it. Without that lookahead, a bare WHERE score < :cursor ... LIMIT :page_size cursor query has exactly the same tie-splitting failure as OFFSET/LIMIT, and it is worse: once the cursor moves past a split-off row, that row is permanently unreachable, not just displaced to a later page.

Worked example

Verified in DuckDB against scores(user_id, score) = (1,100), (2,90), (3,90), (4,90), (5,80), (6,70), (7,70), (8,60), with page_size = 3:

Naive OFFSET 0 LIMIT 3 (page 1) returns users 1, 2, 3 (scores 100, 90, 90; ranks 1, 2, 2). Naive OFFSET 3 LIMIT 3 (page 2) returns users 4, 5, 6 (scores 90, 80, 70): user 4, tied at score 90 with users 2 and 3, lands on page 2 while its tie group already appeared on page 1. A client rendering "rank 2" once at the bottom of page 1 and again at the top of page 2 has effectively shown the same rank position split across two pages, which is exactly the "tie spans a page boundary" failure. A bare cursor query without the lookahead (WHERE score < :cursor ORDER BY score DESC LIMIT 3, no boundary step) makes it worse: page 1 still returns users 1, 2, 3, and page 2's cursor becomes score < 90, which returns users 5, 6, 7 and never returns user 4 at all. The union of pages 1 and 2 is {1,2,3,5,6,7}, permanently missing user 4.

The boundary-lookahead query fixes this. Page 1 (WHERE predicate omitted, page_size = 3): the boundary CTE looks at the top 3 rows by score (100, 90, 90) and finds boundary_score = 90; the outer query then returns every row with score >= 90, which is users 1, 2, 3, and 4 (scores 100, 90, 90, 90). The page grew from 3 to 4 rows specifically because the tie group at the cut point is kept whole. The next cursor is the lowest score just returned, 90.

Page 2 uses WHERE score < 90. The remaining rows are users 5, 6, 7, 8 (scores 80, 70, 70, 60); the boundary CTE looks at the top 3 of those (80, 70, 70) and finds boundary_score = 70, so the outer query returns users 5, 6, 7 (scores 80, 70, 70): the entire score=70 tie group (users 6, 7) stays together, and the page happens to land at exactly 3 rows this time because the tie group didn't straddle the cut. The next cursor is 70.

Page 3 uses WHERE score < 70 and returns only user 8 (score 60), fewer than page_size because it's the last page. The union across all three pages is {1,2,3,4,5,6,7,8}, the complete table, each user exactly once: no row is lost or duplicated.

Why naive OFFSET/LIMIT breaks down here: OFFSET counts physical output rows, with no concept of which rows are tied on the ranking key; it simply draws a line after N rows regardless of whether row N and row N+1 have identical scores. RANK() computing the display value doesn't change this: the ranking and the pagination mechanism are two separate concerns, and OFFSET/LIMIT operates purely on row position, blind to what RANK() assigned. A bare score-cursor without the boundary lookahead inherits the identical blindness. It just relocates where the cut happens; it still cuts through a tie, and because the cursor only ever moves forward, the row stranded on the wrong side of that cut is gone for good, not merely reordered.

Trade-offs & pitfalls

Keyset pagination with boundary lookahead trades a guaranteed exact page size for correctness on ties: a page can come back larger than requested (occasionally much larger, if a huge number of users are tied on one score), so client code has to treat page_size as a maximum-ish target rather than a hard contract. If a product requirement genuinely needs exactly N rows per page no matter what, the only way to keep that guarantee without splitting ties is to accept ambiguity elsewhere (e.g., breaking ties with a secondary, always-unique column like user_id, and accepting that users with identical scores are shown in an arbitrary but stable order, which is a different trade-off than the one this question asks for). Keyset pagination is also more resilient to new rows arriving between page loads than OFFSET-based pagination: since a new higher score only affects pages earlier than the cursor position, it never reshuffles rows already shown on a page a user has already viewed, avoiding the classic "duplicate or skipped row" bug that plain OFFSET pagination has under concurrent writes, independent of the tie-splitting problem this question focuses on.

Diagnosis plan
I would first verify whether the coverage metric is defined correctly: qualified pipeline for the correct period, with realistic close dates and no duplicated opportunities. Healthy coverage on paper can hide weak quality.

What I’d check next

• Funnel health: Are there enough opportunities entering the funnel, or is the issue later-stage conversion?
• Stage conversion: Are deals moving from stage to stage at historical rates, or are they stalling?
• Deal aging: Are opportunities sitting too long in key stages, especially late stage?
• Rep activity: Are reps holding meaningful meetings, advancing next steps, and creating new pipeline, or just logging activity?

How I’d isolate root cause
If early-stage conversion is fine but late-stage conversion is weak, the problem is likely deal execution, pricing, or competition. If stage aging is rising, the team may be pushing close dates or overcommitting. If activity is high but conversions are flat, the issue is not effort but effectiveness. If coverage is concentrated in a few large deals, concentration risk may explain the miss.

I would present leadership with a simple split: volume problem, quality problem, or forecast discipline problem. That makes the fix actionable instead of treating bookings misses as one vague pipeline issue.

Direct answer

Small cohorts produce noisy retention rates because a rate computed from a handful of users has wide sampling variation even when nothing about underlying behavior has changed, and two standard techniques address this directly: bootstrapped confidence intervals, which quantify how much a small cohort's rate could plausibly have varied by chance, and empirical Bayes (beta-binomial) smoothing, which pulls each small cohort's estimate partway toward a shared, more stable estimate borrowed from other similar cohorts.

Structured elaboration

A bootstrapped confidence interval works by resampling the observed users within a cohort with replacement many times, recomputing the retention rate on each resample, and using the spread of those resampled rates to build an interval around the observed rate; it does not change the point estimate itself, only communicates how much uncertainty surrounds it, which is often enough on its own to stop a team from over-reacting to a single small cohort's number. Empirical Bayes smoothing goes further and actually adjusts the point estimate: it treats each cohort's true retention rate as drawn from a shared distribution across cohorts, estimates that shared distribution's parameters from the pooled data, and then shrinks each individual cohort's noisy observed rate toward the pooled mean, weighted by how much data that cohort has (a cohort with very few users gets pulled hard toward the pooled estimate; a cohort with many users is barely adjusted).

A minimum cohort-size threshold is the simplest and cheapest of the three: below some chosen size, a cohort's individual rate is simply not reported on its own, and is instead rolled into a combined bucket with neighboring cohorts until the combined size clears the threshold.

Worked example

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

true_rate = 0.25
cohort_sizes = [30, 35, 40, 45, 50, 55, 60, 32, 48, 38]
observed = [np.random.binomial(n, true_rate) for n in cohort_sizes]
raw_rates = [o / n for o, n in zip(observed, cohort_sizes)]

pooled_rate = sum(observed) / sum(cohort_sizes)
k = 20  # prior strength, in "pseudo-users"
shrunk_rates = [(o + k * pooled_rate) / (n + k) for o, n in zip(observed, cohort_sizes)]

print('raw_rate range:', round(min(raw_rates), 3), '-', round(max(raw_rates), 3))
print('raw std:', round(np.std(raw_rates), 4), '  shrunk std:', round(np.std(shrunk_rates), 4))
print('pooled_rate:', round(pooled_rate, 3))

Simulating 10 small cohorts (sizes 30 to 60 users) that ALL share the same true 25% retention rate, with a fixed seed, the raw observed rates range from 14.6% to 32.7% purely from sampling noise, a spread of over 18 percentage points despite there being zero real difference in the underlying rate. Applying beta-binomial shrinkage (pooling the 10 cohorts to estimate a shared prior, then blending each cohort's own count with that prior) reduces the standard deviation of the estimates across the 10 cohorts from 0.058 to 0.041, and every shrunk estimate moves closer to the pooled rate of 24.0%, which itself is close to the true 25% rate used to generate the data. A team looking only at the raw rates might have concluded the 32.7% cohort was meaningfully outperforming the 14.6% cohort; the shrinkage result shows both were consistent with the same underlying rate once sampling noise is accounted for.

Trade-offs and pitfalls

Displaying a smoothed value rather than the raw observed rate is appropriate when the audience's decision depends on comparing many small cohorts against each other or against a benchmark, since the raw numbers would otherwise mislead by amplifying noise into apparent differences; displaying the raw value alongside a wide confidence interval is more appropriate when the audience needs to see the actual observed data and understand its uncertainty rather than a blended estimate. A pitfall specific to shrinkage is applying it uniformly without disclosing that it has happened, since a stakeholder unaware that a number has been smoothed may draw conclusions as if it were the raw observed rate.

Direct answer

Rank by a deliberate criterion, not gut feel. Name the criterion you're using, which skills unlock the most future scope, which have the highest impact weighed against feasibility, or which close the gap between your current level and the next one, and include both technical and non-technical or soft skills rather than defaulting to an all-technical list, since most next-level gaps involve at least one of each.

Structured elaboration

Pick and state your ranking criterion explicitly before naming the skills, since the same three skills can be justified very differently depending on whether you're optimizing for near-term impact, long-term career growth potential, or impact weighed against feasibility. Naming the criterion is itself part of a strong answer.

Include at least one non-technical or soft skill alongside technical ones. An all-technical list usually signals either an early-career stage where that's genuinely the right focus, or a blind spot at a more senior stage, where communication, prioritization, or influence often matter more than additional technical depth.

Give each skill a brief, honest reason it made the cut over an alternative you considered and rejected. A ranked list without visible trade-offs reads as a wish list, naming what you left off, and why, shows the ranking was real.

Tie each skill back to a concrete situation where its absence cost you something or its presence would have helped, rather than justifying it in the abstract.

Worked example

"Using what most limits my scope right now as my ranking criterion, my list was: first, a specific domain depth I'm missing that currently forces me to hand off certain problems to someone else, second, clearer stakeholder communication, because I've noticed my updates sometimes need a follow-up conversation to clarify what I actually meant, third, prioritization under competing demands, since I've occasionally said yes to too much and delivered several things late rather than a few things well. I considered adding a fourth, negotiation, but ranked it below the other three because I have fewer real situations right now where it's the binding constraint, so investing there first would be lower leverage. If I ranked by long-term career growth potential instead of near-term scope, prioritization and communication would likely move above the technical depth item, since those compound more as scope grows."

Trade-offs & pitfalls

• A list with no explicit ranking criterion invites the interviewer to wonder whether it was thought through or assembled on the spot.
• An all-technical list at a more senior stage often signals a blind spot, since interpersonal and organizational skills tend to become the actual constraint past a certain level.
• An all-soft-skills list with no technical or domain component can read as avoiding the harder, more measurable half of growth.
• Naming skills with no honest reason for the ranking, or no example of where the gap actually showed up, turns a specific answer into a generic one that could apply to almost anyone.