Data Analyst Interview Questions (2026): From SQL Joins to Dashboard Judgment Calls

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has already collapsed them, which is the only place you can reference an aggregate like SUM(revenue) > 10000 directly. Aggregate functions don't exist yet at the point WHERE runs, so putting one there just throws an error.

The efficiency argument matters more than it sounds: filtering with WHERE early, customers signed up this year, orders from the last 90 days, shrinks the row count before the database does the expensive work of grouping and aggregating. Push everything into HAVING instead and you're aggregating rows you were always going to throw away.

What the NULL actually represents. A NULL in shipping_date on an order that hasn't shipped yet is a real, meaningful state; coalescing it to 0 or to today's date would fabricate a shipping event that never happened. A NULL in a discount_pct column, on the other hand, plausibly does mean "no discount applied," and COALESCE(discount_pct, 0) is the right call there.

I don't have a clean rule that covers every table. The honest answer in an interview is that you'd check with whoever owns the source system, or read the ETL logic, before deciding, and say that out loud instead of guessing confidently.

A VLOOKUP pulls a matching value from another table based on a shared key. That's a LEFT JOIN, minus the spreadsheet's row-and-column mental model.

SELECT
 o.order_id,
 o.customer_id,
 c.customer_name,
 c.region
FROM orders o
LEFT JOIN customers c
 ON c.customer_id = o.customer_id;

The honest pitch to an Excel-only colleague: VLOOKUP only returns the first match it finds and silently ignores the rest if a key appears more than once in the lookup table. A LEFT JOIN returns every matching row, which is usually what you actually want, and is exactly why a VLOOKUP-based report sometimes undercounts something a SQL join wouldn't.

Any time a nested subquery is technically correct but takes two reads to parse. A WITH clause that names each step, filtered_orders, then customer_totals, then top_customers, reads top to bottom the way you'd explain it out loud. A stack of nested subqueries reads inside out, fine for the database and rough on the human debugging it six months later.

Most query engines optimize a CTE and an equivalent subquery identically. The value here is entirely for the next person reading the query, who might be you, at 4pm on a Friday, having forgotten what you were doing.

UNION removes duplicate rows across the combined result set; UNION ALL keeps everything, duplicates included. Reach for UNION ALL when you're combining data you already know is distinct, this month's orders from one region table and another, since UNION's dedup pass costs real performance on large tables for no benefit.

The failure runs the other direction too: use UNION ALL when two source tables can genuinely produce the same row (the same transaction landing in both a "completed" and an "archived" export, say), and your report double-counts it with no error, warning, or obviously wrong-looking output.

"If this change genuinely did nothing, we'd still see a result this big or bigger about 3 percent of the time from random noise alone. That's low enough that we're treating it as a real effect, not a coincidence." What that sentence deliberately avoids saying is "there's a 97 percent chance we're right." That's a different claim, about the hypothesis itself, and a p-value doesn't actually make it.

Most VPs don't need the distinction spelled out in exact words every time. They do need you to not accidentally say the wrong claim confidently, since it's the version that gets repeated in a board deck three weeks later.

Whether the number is plausible against something you already know, does this month's total roughly match last month's, does the year-over-year trend look like the business the company actually is. A query that returns a number ten times too large, while still technically the output of correct-looking SQL, usually means a join fanned out somewhere. "The query ran without an error" is a much lower bar than "the query is right."

Second check: does the row count match what you'd expect, and does a spot-check of two or three individual rows tell the same story the aggregate does. Both take under five minutes and catch a meaningful share of the mistakes that would otherwise reach a stakeholder's inbox first.

Whenever the underlying distribution has a long tail that a handful of extreme values can drag around. Average order value on an ecommerce site gets pulled upward by a handful of enormous B2B orders that aren't representative of a typical customer at all. The median order value tells a stakeholder what a typical customer actually spends; the mean tells them a number no typical customer is anywhere near.

The honest move, when it matters, is reporting both and explaining why they differ, rather than picking whichever one supports the story you already wanted to tell.

The question is rarely which tool is "better". It is what is already true of the team. Power BI tends to win in Microsoft-heavy shops already living in Excel and Azure, since licensing and data connections come closer to free. Tableau tends to win where visual polish and ad hoc exploration matter more than governed, single-source-of-truth reporting. Looker's LookML semantic layer wins when a company wants one governed definition of "active user" that every dashboard inherits, at the cost of a steeper setup and a dedicated modeling step most small teams skip.

An interviewer asking this is rarely testing tool trivia. They're testing whether you think about organizational fit at all, or just default to whatever you personally used last.

When the audience needs to look up an exact value, not spot a trend. A finance team reconciling numbers wants precise figures they can check against their own spreadsheet, not a bar chart they'd have to squint at and estimate from. Charts are for pattern recognition, up, down, or flat, tables are for precision and lookup.

A common mistake goes the other way too: a giant table of forty rows and twelve columns, when the actual question anyone's asking is "is this metric trending up," which a single line chart answers in half a second.

Using a line chart to connect categories that have no inherent order. Region A, region B, region C plotted as a connected line implies a progression between categories that don't actually progress into each other. A bar chart, one bar per category with no connecting line, doesn't make that false implication.

Line charts earn their keep specifically for a value changing across a continuous axis, almost always time. The moment the x-axis stops being time or another true continuum, a line chart is usually the wrong choice, however common it is to see one anyway.

Using red and green as the only signal for good and bad, with no other visual cue. Roughly 1 in 12 men has some form of red-green color vision deficiency, and a dashboard that leans purely on that color pair to communicate "this metric is fine" versus "this metric needs attention" is unreadable to a real share of any audience.

The fix is simple and usually free: pair color with a shape, an icon, or a text label, an arrow direction, a checkmark versus an exclamation mark, so the signal survives even when the color itself doesn't land for a given viewer.

SQL wins for aggregation and filtering on structured, relational data sitting in a warehouse. It's usually faster to write, and the database engine is optimized specifically for that kind of operation. Python earns its place for statistical modeling, anything with a real machine learning component, and multi-step transformations that would turn into an unreadable stack of nested subqueries in SQL.

The practical pattern most analysts settle into: pull and shape data with SQL as close to the warehouse as possible, since that's where the compute is cheapest and fastest, then hand off to Python only for the specific step, a regression, a clustering pass, a statistical test, that SQL genuinely can't do well.

VLOOKUP only looks to the right of its lookup column and breaks silently if someone inserts a new column in between, since it references column position by number. INDEX(MATCH()) looks up a value against any column, in either direction, and references columns by name rather than a hardcoded position, so it survives a colleague inserting a column without anyone noticing the report broke.

It's a small habit that saves a real, recurring class of "why did this report suddenly show wrong numbers" incidents, the kind that show up right before a deadline and take an hour to trace back to one inserted column three sheets over.

Goal Seek works backward from a target output to find the input that produces it. "What conversion rate would we need to hit $500,000 in monthly revenue, given our current traffic and average order value" is a Goal Seek question, not a forward calculation. Instead of guessing at conversion rates and recalculating each time, you set the target cell and the input cell to solve for, and Excel iterates to the answer.

It's a narrow tool, useful for exactly this kind of single-variable backward question, and most analysts reach for it a handful of times a year rather than routinely. Knowing it exists, and knowing when it doesn't apply, anything with more than one variable to solve for needs a different approach, matters more than deep fluency with it.

How much the finding changes what someone would do next, and how many people need to act on it consistently. A finding that confirms an existing plan, or that only one person needs to see and act on immediately, is an email. A finding that changes a roadmap decision, needs buy-in across multiple teams, or contradicts something leadership currently believes usually earns the deck, mostly because it needs room for the "how do you know" questions a quick email can't anticipate.

Erring toward the email by default is usually the safer mistake. A deck for a decision nobody was going to reconsider anyway wastes more time than an email that turns out to need one follow-up question.

An INNER JOIN between customers and orders drops any customer with no matching order row, which is exactly the group this question is checking for. The fix is a LEFT JOIN from customers to orders, with COALESCE wrapping the aggregate so a customer with no orders shows 0 instead of NULL, not a blank row that sorts unpredictably.

SELECT
 c.customer_id,
 c.customer_name,
 COALESCE(SUM(o.revenue), 0) AS total_revenue
FROM customers c
LEFT JOIN orders o
 ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_revenue DESC
LIMIT 5;

Worth saying out loud in the interview: zero-order customers will only land in this top-5 list if every real customer happens to have unusually low revenue, which is rare. The point of the question isn't that they'll rank highly. It's whether you'd have excluded them without noticing.

LAG() pulls the previous row's value into the current row without a self-join. Ordered by month, LAG(revenue, 1) grabs last month's number right next to this month's, and the growth rate is just the difference divided by the prior value.

SELECT
 month,
 revenue,
 LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
 ROUND(
  (revenue - LAG(revenue, 1) OVER (ORDER BY month))
  / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100,
  1
 ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

NULLIF guards against dividing by zero for a month with no prior revenue, the first row in the series or a brand-new product line. The number this returns isn't the whole answer, though. A 14 percent jump the month after a product launch and a 14 percent jump in a random October mean different things, and a strong answer says so before moving on.

Conditional aggregation with CASE WHEN inside SUM does the reshaping without a dedicated PIVOT keyword, which not every SQL dialect supports anyway.

SELECT
 region,
 SUM(CASE WHEN MONTH(event_date) = 1 THEN revenue ELSE 0 END) AS jan,
 SUM(CASE WHEN MONTH(event_date) = 2 THEN revenue ELSE 0 END) AS feb,
 SUM(CASE WHEN MONTH(event_date) = 3 THEN revenue ELSE 0 END) AS mar
FROM events
GROUP BY region;

It's verbose past four or five months, and most analysts eventually pivot in the BI tool or a spreadsheet instead of writing thirteen CASE WHEN lines. Knowing the SQL version still matters. It's the same logic a stakeholder's ad hoc "can you also break this out by month" request needs, five minutes before a meeting, with no BI tool open.

Roll the daily table up to monthly first, SUM or AVG by month depending on what the metric represents, then join that rollup to the budget table on month, one row to one row. Plotting daily actuals against a monthly budget on the same axis without this step produces a chart that's technically correct and still misleading, since the budget line repeats the same flat value thirty times in a row.

A cleaner alternative for the chart itself: show daily actuals as a line and the monthly budget as a single reference line or shaded band, instead of forcing both series into matching granularity.

"Our best single estimate sits around 7 percent, the midpoint, but the honest answer is anywhere from 4.2 to 9.8 percent is consistent with what we measured. If the decision changes depending on whether the true number is 4 percent or 10 percent, that's worth knowing before we commit to a plan built on 7."

The failure mode here isn't math, it's the instinct to round a range down to one clean number because a range feels like a weaker answer in the room. It's actually the more complete one, and burying it does the stakeholder a disservice they won't notice until the number moves.

Start by naming the confound before anyone else does: users who are already more engaged are both more likely to explore the help center and more likely to stick around, for reasons that have nothing to do with the help center itself. The correlation is real. The causal story, "send more people to the help center and retention will rise," doesn't automatically follow from it.

To actually answer the causal question, you'd want an experiment, randomly prompt one group to visit the help center and compare retention against a group that wasn't prompted, rather than comparing users who happened to visit on their own against users who didn't. Self-selection is the whole problem, and only randomization removes it.

Likely regression to the mean, not a real turnaround. Extreme results, on either end, tend to include a chunk of ordinary bad or good luck alongside whatever signal is actually in there. The stores that had a rough quarter were never going to stay at that extreme; some bounce back toward average purely from randomness settling out, with zero credit due to anything a regional manager did differently.

The way to actually check: see whether the middle-performing stores also improved by a similar amount, or whether the gain is specific to the bottom group. If it's specific to the extreme group and nothing else changed, regression to the mean is the more defensible explanation than a genuine operational win, however much a regional manager would like to take credit for it.

Depends entirely on what else moved that month. A 14 percent jump the same month as a product launch, a pricing change, or a seasonal spike, holiday retail, tax season for a fintech product, tells a different story than a 14 percent jump in an otherwise ordinary month. The number alone doesn't distinguish "we did something that worked" from "this always happens in this month."

The check that actually answers it: compare against the same month last year, not just last month, and look for anything else that changed around the same date. A single month-over-month comparison, without that context, is one of the easier ways to accidentally tell an executive a story that isn't true.

Check whether the spike sits outside the metric's normal day-to-day variation, not just whether today's number looks bigger than yesterday's. A metric that normally swings between 800 and 1,200 signups a day isn't doing anything unusual at 1,150; that same 1,150 on a metric that's sat at 400 plus or minus 30 for three months is a genuine anomaly worth investigating.

Then look for an obvious cause before treating it as either good news or a data problem: a marketing campaign that launched that day, a bug in the signup counter, a bot wave hitting a form with no CAPTCHA. Most single-day spikes have a boring, findable explanation. The ones that don't are usually the interesting ones, and also the ones worth checking against a second data source before reporting them as fact.

Because the second y-axis's scale is a choice, and that choice can make two unrelated lines appear to move together, or apart, purely from where you set the axis bounds, not from anything real in the data. Stretch or compress the second axis and you can make almost any two metrics look correlated or anti-correlated on the same chart.

Dual-axis charts aren't always wrong. Occasionally they're the clearest way to show two genuinely related metrics on different scales. The warning sign is how easy they are to misuse, intentionally or not, and how rarely an audience checks the axis labels closely enough to catch it.

An operational dashboard needs to answer "is anything broken right now" fast, current-day numbers, clear thresholds, minimal historical context, built for someone checking it three times a day between other tasks. An executive dashboard needs trend context over months or quarters, comparisons against goals or prior periods, and far less granularity, since nobody in that meeting needs today's hourly breakdown.

One dashboard trying to serve both audiences usually serves neither well. The operational team drowns in quarterly trend lines they don't need in the moment, and the executive scrolls past real-time detail hunting for the one number that tells them whether the quarter is on track.

Ask what decision the dashboard is meant to support, specifically. A page trying to answer every possible question ends up answering none of them well, and the one number that actually matters for a given decision gets buried among fifteen others that don't. "What would you do differently based on what this page shows you" is usually the question that narrows a forty-metric wishlist down to the five that matter.

Where I'd push back less: if the real need is a reference page for occasional lookup rather than a decision-support tool, a dense, everything-in-one-place page genuinely serves that differently than a focused one would. The pushback is about matching design to the actual use case, not a fixed belief that fewer metrics always wins.

A calculated column computes a value for every row when the data refreshes, and that value gets stored, taking up memory in the model. A measure computes on the fly, at query time, in response to whatever filter context is currently applied, and isn't stored anywhere.

The practical rule: reach for a calculated column when you need to slice or filter by the result, a category derived from another column, and reach for a measure for anything aggregated, a total, an average, a ratio, since it correctly recalculates for whatever's currently filtered instead of baking in one fixed context. LastRoundAI's Power BI interview questions page covers DAX filter context and CALCULATE in the depth a dedicated BI role actually needs.

Lead with the finding itself, then the business implication, then a recommendation, then how confident you actually are. "Signups from paid search dropped 12 percent since the pricing change, which is costing us roughly $30,000 a month in lost trial starts. I'd recommend reverting the change for that channel specifically while we test a smaller increase. I'm fairly confident on the direction, less confident on the exact dollar figure, since it depends on an assumption about conversion rate."

What that structure deliberately leaves out, unless asked, is the query, the join logic, or the statistical test behind it. Executives need something actionable by Monday, not a methods section. Naming the specific limitation, "less confident on the exact dollar figure," builds more credibility than pretending the estimate is precise when it isn't.

Four things, roughly in this order: completeness, what share of rows actually have values in the fields you need, accuracy, do the values fall in a plausible range, a negative age or an order date in 2099 says something's wrong upstream, consistency, do the same IDs join cleanly across every related table or does a suspicious number silently fail to match, and freshness, when was this actually last updated, and does that match what the label claims.

Whatever you find, write it down before building anything on top of the dataset. A stakeholder who later asks why a number looks off deserves to know upfront that 8 percent of rows were missing a region field, rather than discovering that limitation after they've already presented your analysis to their own boss.

Frequency of the underlying pain point, how often does this actually come up, across how many distinct users, not just how loudly one account complains, correlation between existing workaround behavior and retention or revenue, support ticket volume tied to the gap, and the revenue at risk if it stays unaddressed. A churned enterprise account naming this specifically in an exit interview carries different weight than a single low-tier user's one-off request.

What I'd actually do first, before pulling any of that: ask clarifying questions about what "prioritize" means here, engineering effort available, whether this competes against an already-committed roadmap, whether there's a deadline tied to a specific customer or renewal. Interviewers watch for whether you ask those questions before diving into a data pull that might answer the wrong version of the problem.

Leaving the default aggregation on a numeric field as SUM when the actual question calls for AVERAGE, or the reverse. A pivot table summing an already-averaged rate column, an average conversion rate across regions, say, produces a number that looks plausible and means nothing real, since summing averages isn't a meaningful operation to begin with.

The other common one: filtering the source data after building the pivot rather than before, and forgetting the pivot table needs a manual refresh to reflect it. The table keeps showing stale totals from before the filter, confidently, with nothing on screen suggesting the numbers are out of date.

When another day of digging is more likely to shift your confidence level than to change the actual recommendation. If you're already fairly sure paid search underperforms organic, and the next round of analysis would just tighten the exact percentage, that's a good time to ship with an honest confidence caveat attached. If the next data pull could plausibly flip the recommendation entirely, that's a sign you're not actually done yet.

I don't think there's a clean rule that covers every case here, and I'd be skeptical of anyone who claims there is. The judgment call, ship now with a caveat or spend two more days narrowing the range, is genuinely the harder skill, and it's the one that's hardest to fake in an interview because it doesn't have a textbook answer to recite.

All three number rows within a partition based on an ORDER BY, and they only disagree when there's a tie. ROW_NUMBER hands out a unique sequential integer no matter what, so two tied rows get split arbitrarily into consecutive numbers. RANK gives tied rows the same number and then skips ahead, so 1, 1, 3. DENSE_RANK gives tied rows the same number with no gap, so 1, 1, 2.

The break happens most often on "top N" requests. If a stakeholder asks for the top 10 products by sales and three products are tied for 10th place, ROW_NUMBER will arbitrarily pick one of the three and silently drop the other two from the report, and running the query again without a fully deterministic ORDER BY can return a different one of the three each time. RANK would correctly return all three tied rows, but then your result set has 12 rows instead of 10, which breaks anything downstream expecting exactly 10.

The practical fix is knowing which one the ask actually implies. If you truly need exactly N rows, use ROW_NUMBER but add a tiebreaker column to the ORDER BY so the result is reproducible. If you need "every row in the top N by rank," use RANK and let the row count vary. DENSE_RANK is for when you care about distinct rank buckets, like assigning customers to price tiers, not row counts at all.

SELECT product_id, sales,
    ROW_NUMBER() OVER (ORDER BY sales DESC, product_id) AS rn,
    RANK()    OVER (ORDER BY sales DESC)       AS rnk
FROM product_sales;

Checking the primary key won't tell you anything, a surrogate key is unique by definition even if the row underneath it is a duplicate. You have to group by the natural key, the combination of columns that should identify one real-world record, like order_id plus line_item_id, or customer_id plus event_timestamp for an events table, and look for any group with COUNT(*) greater than one.

The usual causes are an ETL job that appends on every run instead of upserting, so a full reload after a partial failure doubles up whatever already landed. Event tracking that fires twice on a page reload, a double click, or a retried API call after a timeout also produces true duplicate rows, as opposed to the join-fanout case where the row count multiplies but no single row is actually a copy.

Once you've found the natural key that's colliding, the standard cleanup is a window function that ranks duplicates by load timestamp and keeps only the latest.

WITH ranked AS (
 SELECT *, ROW_NUMBER() OVER (
  PARTITION BY order_id, line_item_id ORDER BY loaded_at DESC
 ) AS rn
 FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

It depends on whether the value is a genuine extreme case or a data error, and you can't tell that from the number alone, you have to check plausibility against the business. A $50,000 single order is normal for an enterprise B2B account and would be wrong to touch, the same number on a consumer app with a $20 average order is almost certainly a refund double-count, a test transaction, or a currency mismatch, and it should be fixed or excluded.

If the value is real but the metric is sensitive to it, deleting the row is usually the wrong move because it also throws away every other legitimate signal that row carries, like whether that customer converted or churned. A single eight-hour session from someone who left a tab open will blow up an average time-on-page number even though the session itself is real. In that case switch the headline metric to a median or a trimmed mean, or winsorize, capping values at something like the 1st and 99th percentile instead of deleting them outright, so extreme values still pull the metric a little without one row dominating the whole result.

Whatever you pick, write down the rule you used and apply it consistently across time periods, otherwise a month-over-month comparison ends up comparing two different definitions of the metric without anyone noticing.

That phrase hides several different metrics that would all get reported as one number. Time from what starting event, first site visit ever, first product view in this session, or add to cart? Does "purchase" mean the first purchase this customer ever made, or any purchase, which changes the population entirely for a repeat-buyer business. And whose clock stops the count, only customers who have actually purchased, or does a customer who's still shopping and hasn't bought yet get included somehow.

That last question matters more than it sounds like. If you only measure time-to-purchase for people who've already converted, you're computing the number on a right-censored, survivorship-biased sample, the people who take the longest to decide are disproportionately the ones who haven't converted yet at the time you pull the data, so your average will always look faster than reality, and it'll look artificially faster the more recent your date range is, since recent shoppers who are still deciding simply aren't in the denominator yet.

Once the start event, the purchase definition, and the population are pinned down, then decide mean versus median, this kind of distribution is almost always right-skewed by a small number of long research-heavy purchases, so the mean alone will overstate typical behavior.

A fact table stores events, transactions, or measurements, one row per thing that happened, mostly foreign keys plus numeric measures, like order_id, customer_key, date_key, quantity, and revenue. A dimension table stores descriptive attributes about an entity that changes slowly, one row per entity, or one row per version of that entity if it's a slowly changing dimension, like customer name, region, or product category.

The practical join rule that falls out of this: joining a fact table to a dimension table should be many-to-one and should never inflate your row count. If your revenue total jumps after joining orders to the customer dimension, the dimension table has duplicate keys, usually because a slowly changing dimension wasn't filtered down to the current version and you picked up two or three historical versions of the same customer_id.

Fact-to-fact joins are the other common mistake. Two fact tables at different grains, say order-level revenue and daily ad spend, share a date key but aren't naturally joinable row for row. Aggregate each fact table up to the grain you actually need first, then join on the shared dimension key, instead of joining raw and hoping the grain lines up.

A live connection queries the underlying database every time someone opens or interacts with the dashboard, always current, at the cost of hitting the source database on every view and every filter click. An extract pulls a snapshot into the BI tool's own compressed format on a schedule, much faster to interact with, current only as of the last refresh.

The design implication: a live connection on a heavily used dashboard against a large table can genuinely slow down a production database if enough people open it during a busy hour. An extract refreshed nightly is usually the right trade for anything that doesn't need up-to-the-minute freshness, which, honestly, is most executive and operational reporting once you actually ask how current it needs to be.

The trick: number each customer's distinct purchase months with ROW_NUMBER, then subtract that row number from the actual month number. For genuinely consecutive months, that difference stays constant across the run, which turns a sequence problem into an ordinary GROUP BY and COUNT.

WITH customer_months AS (
 SELECT DISTINCT
  customer_id,
  DATE_TRUNC('month', order_date) AS purchase_month
 FROM orders
),
numbered AS (
 SELECT
  customer_id,
  purchase_month,
  ROW_NUMBER() OVER (
   PARTITION BY customer_id ORDER BY purchase_month
  ) AS rn,
  DATEDIFF('month', DATE '2020-01-01', purchase_month) AS month_seq
 FROM customer_months
)
SELECT customer_id
FROM numbered
GROUP BY customer_id, month_seq - rn
HAVING COUNT(*) >= 3;

This is the one question on this page where most candidates need a hint on the grouping trick itself, and that's fine. The DISTINCT in the first CTE matters too. Without it, a customer with two orders in the same month gets double-counted and can throw off the streak length entirely.

Grain mismatch. If daily_transactions has 30 rows for March and monthly_budget has 1 row for March, joining them directly on month produces 30 rows, each one carrying the full monthly budget figure. Sum the budget column after that join and you've multiplied it by 30, not because the SQL is wrong, but because the join fanned out rows that were never meant to multiply.

The fix is to aggregate one side down to the matching grain before joining, roll the daily table up to monthly first, then join one row to one row, or pull the budget figure from a pre-aggregated subquery instead of the raw join output.

That the test as designed can't answer the question, and running it anyway just produces a number that looks like an answer without being one. The honest options: widen the minimum detectable effect you're willing to accept, maybe only a 5-point lift or larger actually matters here, extend the test to accumulate more users over time, or accept that this specific question needs a different method, a longer observational study, a proxy metric with more volume, or a qualitative read instead of a quantitative one.

What doesn't work, and what I've seen teams do anyway under deadline pressure, is running the underpowered test and reading a non-significant result as "no effect," when the honest read is "we couldn't tell, one way or the other." Those are different conclusions dressed up as the same sentence.

Usually the labeling and the framing, not the underlying numbers. "Revenue" without a note on whether it's gross or net, before or after refunds, invites everyone to assume their own definition, and most people assume the one that confirms what they already believed. Adding a short definition directly on the dashboard, not buried in a wiki page nobody opens, closes most of that gap.

The other common cause: color or ordering that implies a judgment the data doesn't support, red for a metric that's actually within normal range, or a bar chart sorted by something other than what the eye expects, alphabetical instead of by value. Correct data presented with implicit signals that contradict the actual message gets misread every time, and it's rarely the stakeholder's fault when it does.

Build a small reconciliation check that compares a dashboard's headline number against a fresh query straight against the source table, on a schedule, and alerts if they diverge past some threshold. Most drift doesn't happen all at once. It happens gradually, a schema change upstream that a dashboard's extract silently stops picking up, a filter that used to be correct and stopped matching after a new product line launched with a different naming convention.

Absent that kind of automated check, the low-effort version is a standing calendar reminder, monthly, to manually spot-check two or three headline dashboard numbers against a raw query. It isn't elegant. It catches real drift that nobody notices otherwise until a stakeholder asks why their own spreadsheet disagrees with the dashboard by a wide enough margin to raise an eyebrow.

Rule out a tracking or measurement problem before concluding anything about user behavior. Check whether the metric's definition changed, whether an event stopped firing correctly, whether the drop is isolated to one platform, one geography, or one traffic source rather than showing up evenly everywhere. A drop confined to iOS, for instance, points at an app release or a tracking SDK issue well before it points at a genuine behavior shift.

Only once instrumentation checks out clean would I segment by acquisition channel, device, cohort, and feature usage, and cross-reference the timing against anything the product or marketing team shipped that day. Jumping straight to "users don't like the new feature" before confirming the drop is even real wastes the stakeholder's time and, worse, sometimes produces a confident wrong answer that gets acted on.

Get both actual queries or both actual spreadsheets in front of you before speculating about the cause. Nine times out of ten the discrepancy traces back to a filter, a date range, or a definition that quietly differs between the two, "active user" defined as logged in this month versus logged in the last 30 days, a date filter set to calendar month versus a rolling 30-day window, rather than a genuine data error in either version.

Once you've found the actual difference, the harder part is deciding, with whoever owns each definition, which one should become the standard going forward, and documenting it somewhere both teams will actually see again. Fixing the immediate discrepancy without agreeing on a single go-forward definition just guarantees the same argument happens again next quarter.

Start with EXPLAIN, or EXPLAIN ANALYZE if the engine supports it, and look at the actual plan instead of guessing. The most common finding is a sequential scan where you'd expect an index scan. That can mean the index still exists but the optimizer's row-count estimates are stale after a 50x growth in table size and haven't been refreshed, or it can mean a function got applied to the indexed column in the WHERE clause, something like WHERE DATE(created_at) = '2026-07-01', which makes a plain index on created_at unusable no matter how big the table gets.

Also check what changed about the join. At 1 million rows a hash join might fit entirely in memory, at 50 million it can spill to disk, and disk-spilled joins are routinely 10 to 100 times slower, which alone can explain a jump from seconds to minutes.

It's also possible the optimizer is making the right call and the real problem is the query shape. Past a certain selectivity, scanning the whole table actually is faster than an index lookup, and no amount of index tuning fixes that, the fix is restructuring the query, pre-aggregating a summary table, partitioning by date, or adding a covering index that avoids a second lookup back to the table. Before any of that, just running ANALYZE to refresh statistics fixes it more often than people expect.

Simpson's paradox is when a trend holds in every individual group you look at, but reverses or vanishes once you combine those groups into one number. A common version in product analytics: a redesigned onboarding flow converts better than the old flow for mobile users, and it also converts better for desktop users, looked at separately. But the old flow shows a higher overall conversion rate once you combine both device types.

That happens when the mix between groups shifts at the same time as the thing you're measuring. If the new flow launched during a stretch where mobile traffic share jumped, and mobile converts worse than desktop for this product regardless of which flow it sees, the aggregate number gets dragged down by the change in traffic composition even though the flow genuinely improved outcomes in both segments.

You catch it by never trusting a topline number without breaking it down by whatever confounder is most likely to be shifting at the same time, device, channel, cohort, or region depending on the business. If the direction of the effect flips or disappears at any level of that breakdown, the segment weights are doing the work, not the thing you're claiming caused the change, and that's what gets reported instead.

Almost always a timezone mismatch between where the timestamp is stored and where the report truncates it to a date. Timestamps are usually stored in UTC, which is correct practice, but if the report groups by DATE(created_at) without converting to the business's local timezone first, anything that happened after roughly 4pm or 7pm local time, depending on the offset, gets bucketed into the following UTC calendar day.

It gets worse around daylight saving transitions, because the UTC offset itself changes twice a year, and a hardcoded fixed offset like UTC-5 will be off by an hour for half the year even after you think you've fixed it.

The correct fix is to convert at query time using a timezone-aware function, CONVERT_TIMEZONE, AT TIME ZONE, or the warehouse's equivalent, with the actual IANA name like America/New_York rather than a fixed offset, and truncate to a date only after that conversion, never before it. It's also worth confirming what the business actually wants, one canonical reporting timezone is the normal ask, each user's own local timezone is a much more expensive request that looks identical in a ticket but requires storing and joining a timezone per user.

Window functions like AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) count rows, not calendar days. If a date is missing from the table entirely, say a slow Sunday with zero orders and no row generated for it, the window quietly reaches back an extra calendar day to fill 7 rows, so the average silently spans 8 or 9 real days instead of 7 without changing the frame definition at all.

The fix is to build a complete calendar spine first, a date series from your minimum to maximum date, one row per day, and left join your real data onto it, filling missing days with zero. Only then apply the window function, so it's guaranteed to be operating over actual consecutive calendar days.

WITH spine AS (
 SELECT generate_series(MIN(order_date), MAX(order_date), interval '1 day')::date AS d
 FROM orders
),
daily AS (
 SELECT s.d, COALESCE(SUM(o.revenue), 0) AS revenue
 FROM spine s
 LEFT JOIN orders o ON o.order_date = s.d
 GROUP BY s.d
)
SELECT d, revenue,
    AVG(revenue) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily;

Some engines support a RANGE frame with a real date interval instead of a row count, but that only helps if a row already exists for every relevant day, so the calendar spine is the more portable fix regardless of engine.

Start with definitions, not code. Does finance's number include tax, refunds, or in-transit orders that your dashboard counts differently? A surprising share of these discrepancies trace back to two teams using the word "revenue" to mean two different things, not to a bug in either query.

If definitions genuinely match, check the date boundary next, timezone handling on a timestamp column is a classic source of a few thousand dollars sliding across a quarter boundary, then check for duplicate rows from a join that fanned out somewhere upstream. Only after ruling those out would I start comparing row counts and spot-checking individual transactions. Jumping straight to "let me pull ten orders and check them by hand" wastes an hour finding nothing wrong with ten correctly counted orders.