Data Analysis Interview Questions and Answers

Answer

Profile before you analyse. The first thing to establish is the grain: run a count of rows against a count of distinct candidate keys, and if order_id is not unique then the file is one row per order line, not per order, and every revenue sum you write will be wrong. Second, establish coverage: min and max of the order date, plus a count by month, which immediately exposes a truncated export or a month where the pipeline silently dropped data.

Third, measure null rates per column and distinct value counts for every categorical column, because a city column with 4,800 distinct values is telling you nobody normalised it. Fourth, check the mechanical damage a CSV picks up on the way to you: encoding problems that turn Hindi or Tamil names into question marks, long numeric IDs that Excel converted into scientific notation, dates that got read as DD/MM in one row and MM/DD in another, and amounts stored as text with commas. Finally, reconcile one known number against a system of record, for example, total March GMV against the finance report, before you show anything to anyone.

Interviewers ask this to see whether you validate or whether you jump straight to a chart. The candidates who lose the round are the ones who start describing visualisations before they have checked whether the file even contains one row per order.

import pandas as pd

df = pd.read_csv('orders.csv', dtype={'order_id': 'string', 'customer_phone': 'string'})

# 1. Grain check
print(len(df), df['order_id'].nunique())

# 2. Coverage
print(df['order_date'].min(), df['order_date'].max())
print(df.groupby(df['order_date'].str[:7]).size())

# 3. Null rate and cardinality
print(df.isna().mean().sort_values(ascending=False).head(15))
print(df.nunique().sort_values())

# 4. Category sanity
print(df['city'].value_counts(dropna=False).head(30))

Answer

Use the mean when the distribution is roughly symmetric and every observation should pull the number equally. Use the median when the distribution is skewed or has outliers, which describes almost every commercially interesting variable: salary, order value, session duration, time to first purchase, loan ticket size. Indian ecommerce data is a good example, one bulk B2B order of two lakh rupees can lift the average order value for the day by fifteen percent while the typical customer experience is unchanged, so the mean answers a question nobody asked.

The median answers the question the business actually meant, which is what a typical order looks like. The mode matters for discrete or categorical data where an average is meaningless: the most common pincode, the most common payment method, the most frequently selected drop-off reason. It is also the honest answer when a distribution is multimodal, for example, delivery times that cluster at thirty minutes for dense city zones and at ninety minutes for outskirts, because reporting a single mean of sixty minutes describes a customer experience that literally nobody has.

The strong answer in an interview adds two things: report the median together with a spread measure such as the interquartile range or p90, and mention the trimmed mean as the middle ground when you need additivity but want to suppress a handful of extreme values. Averages are also the only one of the three that survive being summed back up, which is why finance keeps asking for them.

Answer

First decide what the missingness means, because that determines everything else. Missing completely at random means the gap is unrelated to anything, and dropping those rows costs you sample size but not correctness. Missing at random means the gap depends on other observed columns, for example, income is missing more often for younger users, so a model or imputation that conditions on age can recover it.

Missing not at random means the gap depends on the unobserved value itself, for example, high earners refuse to disclose salary, and no imputation fixes that honestly, you flag it as a limitation. Practically, the options are row deletion, column deletion when the null rate crosses something like sixty or seventy percent, constant imputation, mean or median imputation, forward fill for genuine time series carry-forward, and model-based imputation. The gotchas that interviewers probe for are these: mean imputation shrinks variance and weakens every correlation the column participates in, so it quietly biases downstream analysis.

Forward fill is only valid when the real world value genuinely persists, such as a subscription plan, and is nonsense for an event value like a transaction amount. And in SQL, a NULL produced by a LEFT JOIN usually means the event did not happen, which is information, not a gap, so replacing it with zero is correct while imputing a mean is a bug. Whatever you do, add a boolean is_missing indicator column, keep the raw values, and state the treatment in the deck.

-- LEFT JOIN nulls mean 'no purchase', so COALESCE to 0 is correct here
SELECT u.user_id,
       COALESCE(SUM(o.amount), 0) AS lifetime_value,
       COUNT(o.order_id)          AS order_count
FROM   users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

-- But a genuinely unknown survey answer must stay NULL,
-- because AVG() ignores NULLs and would be biased by a 0 fill
SELECT AVG(nps_score) AS avg_nps FROM survey_responses;

Answer

The difference follows directly from the logical order in which SQL evaluates a query: FROM, then JOIN, then WHERE, then GROUP BY, then HAVING, then window functions, then QUALIFY, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. WHERE filters individual rows before grouping happens, so it cannot reference an aggregate. HAVING filters groups after aggregation, so it can say HAVING SUM(amount) > 100000 but is the wrong place to filter on a plain column.

QUALIFY, supported in Snowflake, BigQuery, Databricks and Teradata, filters on the result of a window function without forcing you to wrap the query in a subquery, so QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1 gives you the latest row per user in one statement. Two practical points come up constantly. First, performance: pushing a predicate into WHERE lets the engine prune partitions and read less data, while the same predicate in HAVING forces a full aggregation first, so on a partitioned table WHERE order_date >= '2026-01-01' can be the difference between scanning two gigabytes and two hundred.

Second, aliases: most engines do not let you reference a SELECT alias in WHERE because SELECT is evaluated later, though MySQL and BigQuery are lenient in HAVING and ORDER BY, which is exactly the kind of engine specific behaviour an interviewer will ask you to name. If a filter can live in WHERE, put it there, and reserve HAVING for genuine aggregate conditions.

-- WHERE prunes rows first, HAVING filters the aggregate
SELECT city,
       COUNT(*)        AS orders,
       SUM(amount)     AS gmv
FROM   orders
WHERE  order_date >= DATE '2026-01-01'   -- row filter, prunes partitions
  AND  status = 'delivered'
GROUP BY city
HAVING SUM(amount) > 1000000             -- group filter
ORDER BY gmv DESC;

-- QUALIFY: latest order row per customer, no subquery needed
SELECT customer_id, order_id, amount, order_date
FROM   orders
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY customer_id ORDER BY order_date DESC
        ) = 1;

Answer

Almost certainly join fan-out. Joining an orders table (one row per order) to an order_items table (many rows per order) multiplies the order row once per item, so SUM(orders.order_total) now counts a three item order three times. The report does not error, it just quietly inflates, which is why this bug reaches leadership decks so often.

The fixes, in order of preference: aggregate the many side to the grain you need in a subquery or CTE and then join the single row result, or sum the item level amount instead of the order level amount so you are summing at the natural grain of the joined table. What you must not do is reach for SUM(DISTINCT order_total), because two genuinely different orders with the same total then collapse into one. The second, closely related bug is a LEFT JOIN with a filter on the right table placed in WHERE.

Writing LEFT JOIN payments p ON p.order_id = o.order_id WHERE p.status = 'success' silently converts the outer join into an inner join, because rows with no payment produce a NULL status which fails the predicate, so all your unpaid orders vanish. The condition belongs in the ON clause, or the WHERE needs an OR p.order_id IS NULL. Interviewers love this pair because both bugs produce plausible looking numbers rather than errors, and catching them is the difference between an analyst who is trusted and one who is checked.

-- WRONG: fan-out, order_total counted once per item
SELECT o.city, SUM(o.order_total) AS gmv
FROM   orders o
JOIN   order_items i ON i.order_id = o.order_id
GROUP BY o.city;

-- RIGHT: pre-aggregate the many side, then join at order grain
WITH item_rollup AS (
  SELECT order_id, SUM(qty) AS units
  FROM   order_items
  GROUP BY order_id
)
SELECT o.city,
       SUM(o.order_total) AS gmv,
       SUM(r.units)       AS units
FROM   orders o
LEFT JOIN item_rollup r ON r.order_id = o.order_id
GROUP BY o.city;

Answer

The grain is the business meaning of one row, stated in a single sentence: one row per order, one row per order line, one row per order status change, one row per user per day. Everything downstream depends on it. If you do not know the grain, you cannot know whether SUM is safe, whether COUNT(*) means orders or events, or whether a join will fan out.

The practical test is mechanical: pick your candidate key and compare COUNT(*) to COUNT(DISTINCT key). If they match, that key defines the grain. If they do not, you either have duplicates or the real grain is finer than you assumed, and both need investigating before you write another line.

Event tables are where analysts get burned most. A payments table often carries one row per attempt, not per successful payment, so counting rows over-reports transactions by the retry rate, which on Indian UPI and card flows can be substantial. A status history table has one row per transition, so joining it to orders without filtering to the latest status multiplies everything.

A daily snapshot table has one row per entity per day, so summing a balance column across a month gives you a meaningless number thirty times too large, and the correct aggregation is a point in time pick, not a sum. Interviewers frequently hand you a schema and ask what one row means in each table, because a candidate who answers that fluently will not produce a double counted dashboard.

Answer

XLOOKUP is the best function of the three and the wrong choice in some real environments, which is exactly the nuance interviewers are testing. VLOOKUP has three structural weaknesses: the lookup column must be the leftmost column of the range, the column index is a hard coded number that silently returns the wrong column the moment somebody inserts a column, and the fourth argument defaults to TRUE, meaning approximate match, so an unsorted table returns confidently wrong values rather than an error. If you must use it, always write FALSE explicitly.

INDEX with MATCH removes both problems because the lookup and return ranges are independent, it can look leftwards, and it survives column insertion. XLOOKUP folds all of that into one function and adds three things worth knowing by name: an if_not_found argument so you stop wrapping everything in IFERROR, a match_mode argument for exact, next-smaller, next-larger or wildcard matching, and a search_mode of -1 that searches bottom up, which is how you fetch the most recent record without sorting. The catch is availability.

XLOOKUP needs Microsoft 365 or Excel 2021 and later, so a workbook shared with someone on Excel 2019 or an older LTSC build shows _xlfn.XLOOKUP and a #NAME? error across the sheet. In Indian finance and operations teams where locked down desktop builds are common, that is a real constraint, and INDEX/MATCH remains the safe portable answer.

' Exact match, explicit, with a fallback
=XLOOKUP(A2, Products[SKU], Products[Price], "Not found", 0)

' Most recent record: search bottom-up
=XLOOKUP(A2, Orders[CustomerID], Orders[OrderDate], "", 0, -1)

' Portable equivalent for Excel 2019 and earlier
=INDEX(Products[Price], MATCH(A2, Products[SKU], 0))

' VLOOKUP done safely: FALSE is not optional
=VLOOKUP(A2, Products!A:D, 4, FALSE)

Answer

Pivot tables are the fastest way to aggregate in Excel and the fastest way to publish a wrong number. The failure modes worth naming: first, a value field defaults to Count instead of Sum whenever the source column contains a single text entry or a blank, so an amount column with one stray 'NA' quietly reports a row count that looks plausible. Second, pivots do not refresh automatically, so a workbook mailed on Monday can show Friday's numbers unless somebody hits Refresh All or the connection is set to refresh on open.

Third, calculated fields operate on the aggregated totals, not row by row, so a calculated field of revenue divided by orders is fine, but a calculated field that multiplies price by quantity gives you sum of price times sum of quantity, which is wildly wrong. Compute row level derived columns in the source data instead. Fourth, Excel auto-groups date fields into years, quarters and months, which is convenient until you need raw dates back and cannot find the ungroup option.

Fifth, a standard pivot cannot do a distinct count, and analysts fake it with helper columns, when the correct answer is to add the data to the Data Model and use Distinct Count, which is available once the source is loaded as a Power Pivot table. Finally, GETPIVOTDATA auto-insertion breaks fill-down when you reference pivot cells in formulas, and the fix is to turn it off under PivotTable Analyze, Options.

Answer

SUMIFS and COUNTIFS handle the common case cleanly and are much faster on large ranges because they are optimised internally. Their limits are specific. Criteria are passed as text, so a date bound must be concatenated, for example, ">="&DATE(2026,4,1), and writing ">=01/04/2026" inside quotes fails silently in locale-dependent ways, which matters in India where the DD/MM convention differs from the US default in many templates.

Multiple criteria in SUMIFS are always combined with AND, so an OR condition across the same field means adding two SUMIFS together or passing an array constant. Text criteria are case-insensitive and treat asterisk and question mark as wildcards, so a SKU that genuinely contains an asterisk needs a tilde escape. And COUNTIFS returns zero rather than an error when numbers are stored as text, which is the single most common reason a spreadsheet total does not tie out.

SUMPRODUCT is the escape hatch: it multiplies arrays element by element and sums the result, so any boolean expression you can write becomes a condition. That buys you case-sensitive matching with EXACT, OR logic with addition, AND logic with multiplication, weighted sums such as price times quantity in one cell, and criteria that reference calculated arrays rather than plain ranges. The cost is performance, since SUMPRODUCT evaluates the whole array, so on a hundred thousand row sheet with a dozen such formulas the workbook becomes sluggish. Use SUMIFS by default and SUMPRODUCT when the logic genuinely cannot be expressed otherwise.

' Date bounds must be concatenated, not typed inside the quotes
=SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(2026,4,1),
                        Sales[Date], "<="&DATE(2026,6,30),
                        Sales[City], "Mumbai")

' OR across the same field: add two SUMIFS
=SUMIFS(Sales[Amount], Sales[City], "Mumbai") + SUMIFS(Sales[Amount], Sales[City], "Pune")

' Weighted total in one cell, impossible with SUMIFS
=SUMPRODUCT(Sales[Qty], Sales[UnitPrice])

' Case-sensitive count, impossible with COUNTIFS
=SUMPRODUCT(--EXACT(Sales[SKU], "ABC-100"))

Answer

Because the average describes an experience that a large minority of customers never have. Delivery time, page load time, API latency and support resolution time all have long right tails, so a mean of thirty two minutes can sit alongside a p90 of seventy eight minutes and a p99 of three hours. The people who churn, complain and post screenshots live in that tail, and the mean makes them invisible by design.

Reporting p50 alongside p90 gives you both the typical case and the bad case, and it is the reason service level agreements are written as percentile promises rather than averages, for example, ninety percent of orders delivered within forty five minutes. Three technical points earn credit here. First, percentiles do not average, so you cannot compute a weekly p90 by taking the mean of seven daily p90 values, you must recompute from the raw distribution, and this mistake appears in real dashboards constantly.

Second, know your functions: PERCENTILE.INC and PERCENTILE.EXC in Excel differ in whether the endpoints are included, PERCENTILE_CONT interpolates between values while PERCENTILE_DISC returns an actual observed value in SQL, and APPROX_QUANTILES in BigQuery trades exactness for cost on very large tables. Third, watch the denominator, because a p90 computed only on completed deliveries excludes the cancelled orders that were slow enough that the customer gave up, which flatters the number precisely where it matters most.

-- p50 and p90 recomputed from raw rows, never averaged across days
SELECT DATE_TRUNC('week', ordered_at) AS wk,
       COUNT(*)                                                             AS deliveries,
       PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY delivery_minutes)        AS p50,
       PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY delivery_minutes)        AS p90,
       AVG(delivery_minutes)                                                 AS mean_mins
FROM   deliveries
WHERE  ordered_at >= DATE '2026-01-01'
GROUP BY 1
ORDER BY 1;

Answer

Carefully, because the correlation is probably real and the causal claim is probably overstated. The three alternative explanations you should be able to name instantly are confounding, reverse causality and selection. Confounding: spend and revenue both rise during the festive season in India, so a chart of the two from September to November correlates beautifully without either causing the other.

Reverse causality: many performance teams set next month's budget from last month's revenue, so revenue is causing spend, not the other way round. Selection: campaigns are targeted at people already likely to buy, and retargeting in particular shows ads to users who added to cart, which means the attributed conversions would have happened anyway. The constructive follow-up matters more than the objection.

Offer the ladder of evidence you would actually build: start with a holdout, where a randomly chosen share of the geography or audience sees no ads for two weeks, and compare against the treated group. If a full holdout is politically impossible, propose a staggered geo rollout so you can run a difference in differences on cities that turned on at different times. Failing that, look for a natural experiment such as an unplanned budget pause or a payment failure that stopped delivery, since those are unplanned and therefore closer to random. Finally, quantify what the observational number can support, which is usually a correlation plus a bounded estimate, and say so plainly rather than letting a slide imply causation.

Answer

City and state are the worst. A single city arrives as Delhi, New Delhi, NCR, Dilli, DEL and Delhi NCR, and Bengaluru arrives as both Bangalore and Bengaluru because the rename never propagated to old records. The right fix is a mapping table keyed on a lowercased, whitespace-stripped, punctuation-stripped version of the raw string, maintained as data rather than as a chain of nested IFs, plus a fallback bucket that you review weekly so new spellings do not silently disappear.

Pincode is more reliable than a free text city field, so where you have both, derive the city from pincode. Phone numbers arrive as ten digits, with a 0 prefix, with +91, with 91 and no plus, with spaces and hyphens, and sometimes in scientific notation because the file passed through Excel. Normalise by stripping non-digits and keeping the last ten digits, then validate that the first digit is 6 to 9 for an Indian mobile.

Dates are the third trap: 03/04/2026 is 3 April in an Indian export and 4 March in a US-defaulted tool, and Excel converts ambiguous strings on paste without warning, so parse with an explicit format and reject rather than guess. Also watch amounts stored as text with commas in the Indian grouping style, trailing whitespace from form inputs, and mixed case emails. Keep the raw column, write the cleaned value to a new column, and log how many rows each rule touched so the cleaning itself is auditable.

import re
import pandas as pd

def clean_msisdn(v):
    digits = re.sub(r'\D', '', str(v))
    digits = digits[-10:] if len(digits) >= 10 else ''
    return digits if digits[:1] in tuple('6789') else None

df['phone_clean'] = df['customer_phone'].map(clean_msisdn)

df['city_key'] = (df['city'].str.strip().str.lower()
                            .str.replace(r'[^a-z ]', '', regex=True))
df = df.merge(city_map, on='city_key', how='left')
print(df.loc[df['city_std'].isna(), 'city'].value_counts().head(20))

# Never let the parser guess between DD/MM and MM/DD
df['order_date'] = pd.to_datetime(df['order_date'], format='%d/%m/%Y', errors='coerce')
print('unparsed dates:', df['order_date'].isna().sum())

Answer

A metric is something you measure and can aggregate: orders, gross merchandise value, seven day retention, average handling time. A dimension is something you slice by: city, channel, device, plan tier, acquisition month. A KPI is the small subset of metrics a team has agreed to be held accountable for in a given period, so every KPI is a metric but almost no metric is a KPI.

The north star is the single metric that best proxies delivered customer value and predicts durable revenue, chosen so that moving it is hard to fake. Picking one is a judgement exercise and interviewers score the reasoning, not the answer. Good candidates apply four tests.

Does it reflect value received by the customer rather than activity by the company, so weekly active buyers rather than app installs. Is it hard to game, since a support team measured on tickets closed will close tickets without solving them. Is it sensitive enough to move within a quarter, because annual revenue is a fine goal and a useless steering metric.

And can it be decomposed into inputs each team can own, for example, marketplace revenue splitting into buyers times orders per buyer times average order value times take rate. Then say what you would pair it with, because a north star alone invites abuse: pair growth metrics with a quality counter-metric such as return rate, refund rate or NPS, so nobody wins by shipping something that hurts the customer.

Answer

Match the encoding to the question. Comparison across categories is a bar chart, and horizontal bars when the labels are long, which they usually are for Indian city or brand names. Change over time is a line chart, with time always on the x axis.

Relationship between two continuous variables is a scatter, adding a trend line only when you can justify the functional form. Part to whole is a stacked bar or a treemap, and a pie chart only for two or three slices, because humans compare angles poorly. Distribution is a histogram or a box plot, and the moment somebody asks for the average of a skewed variable you should be showing a distribution instead.

Composition over time is a stacked area, but only when the total itself is meaningful. The misleading choices to call out are specific. A truncated y axis on a bar chart exaggerates small differences, and bars must start at zero even though lines need not.

A dual axis chart lets you manufacture any correlation you want by choosing the two scales, so use it only when both series share a natural relationship and label both axes explicitly. Sorting bars alphabetically instead of by value hides the story. Rainbow colour scales imply ordering that does not exist, and red-green pairs are unreadable for roughly one in twelve male viewers. Finally, a chart without a stated denominator or time window is not a chart, it is a decoration.

Answer

Both, and the distinction is exactly why the question is asked. The absolute change is one percentage point. The relative change is twenty five percent.

Saying conversion went up by one percent is wrong on both readings, and it is the single most common numerical sloppiness in analyst work. Report both when the audience is mixed, because relative change flatters small bases and absolute change flatters large ones, and stakeholders will unconsciously pick whichever framing supports their case. Two related traps belong in the same answer.

First, average of ratios versus ratio of averages: if Mumbai converts at 6% on 100,000 sessions and Kochi at 2% on 1,000 sessions, the unweighted mean of the two rates is 4% while the true blended rate is close to 5.96%. Always compute a rate as total numerator divided by total denominator, never as the mean of per-group rates, unless you deliberately want each group weighted equally and you say so. Second, percentage change on a small base is noise dressed as a result: going from 2 conversions to 3 is a fifty percent lift and means nothing, so quote the raw counts alongside every percentage. Finally, be precise about direction language, since a drop from 5% to 4% is a one point fall and a twenty percent relative fall, not twenty five, because the base changed.

-- Correct blended rate: ratio of sums, not mean of ratios
SELECT SUM(converted) * 1.0 / SUM(sessions)            AS blended_cvr,
       AVG(converted * 1.0 / NULLIF(sessions, 0))       AS misleading_avg_of_rates
FROM   city_daily_funnel
WHERE  dt BETWEEN DATE '2026-07-01' AND DATE '2026-07-31';

Answer

A cohort is a group of users bound together by when something first happened to them, most often their signup or first purchase month, and then tracked forward on a common clock. Cohort retention asks what share of the users who joined in March were still active in their second month, their third month, and so on. Overall retention asks what share of last month's active users were active this month, which mixes users at completely different lifecycle stages into one number.

The two diverge for a structural reason: overall retention is dominated by the mix of cohorts present, so a company acquiring aggressively looks like it is retaining worse simply because it has a higher share of brand new users, who always churn most in the first weeks. Conversely, when acquisition slows the blended number improves even though nothing about the product changed. This is why growth teams read the cohort triangle rather than the blended line.

Two definitional choices decide whether your cohort chart is honest. First, is retention calculated on calendar months or on rolling thirty day windows from each user's own signup date, because the calendar version penalises users who joined on the 28th. Second, what counts as retained: opening the app, taking a core action, or transacting, and only the last is meaningful for a commerce business. Also check that the most recent cohorts are not shown at full width, because a cohort that is only two weeks old has no month three number and plotting it as zero creates a fake cliff.

WITH first_month AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM   orders
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT o.user_id,
         DATE_TRUNC('month', o.order_date) AS active_month
  FROM   orders o
)
SELECT f.cohort_month,
       DATE_DIFF(a.active_month, f.cohort_month, MONTH) AS month_index,
       COUNT(DISTINCT a.user_id)                        AS retained_users
FROM   first_month f
JOIN   activity a ON a.user_id = f.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

Answer

All three assign a position within a partition according to an ORDER BY, and they differ only in how they treat ties. ROW_NUMBER always produces a strictly increasing sequence with no gaps and no duplicates, breaking ties arbitrarily unless your ORDER BY is fully deterministic. RANK gives tied rows the same number and then skips, so two rows tied at 1 are followed by 3.

DENSE_RANK gives tied rows the same number and does not skip, so the sequence continues at 2. The practical rule: use ROW_NUMBER to pick exactly one row, RANK when a business definition says tied entries share a position and the count of positions should reflect the skip, and DENSE_RANK for top-N-by-distinct-value questions such as the three highest distinct order values. Deduplication is the most common interview use.

Partition by the natural key, order by whatever defines the survivor (usually the latest updated_at, with a tiebreaker on the primary key so the result is reproducible), and keep row number 1. The subtlety interviewers push on is determinism: if two rows share the same updated_at and you have no tiebreaker, the query returns a different survivor on different runs, which turns into a dashboard that flickers. The second subtlety is that the window function runs after WHERE but before QUALIFY, so filtering on the result requires either QUALIFY or a wrapping subquery. On engines without QUALIFY, such as PostgreSQL and MySQL, wrap it in a CTE and filter outside.

-- Tie behaviour, side by side
SELECT product_id, revenue,
       ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn,   -- 1,2,3,4
       RANK()       OVER (ORDER BY revenue DESC) AS rnk,  -- 1,1,3,4
       DENSE_RANK() OVER (ORDER BY revenue DESC) AS drnk  -- 1,1,2,3
FROM   product_revenue;

-- Deterministic dedupe: keep the latest row per order_id
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, ingested_at DESC, source_row_id DESC
         ) AS rn
  FROM   raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

Answer

A running total is SUM(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), and a trailing seven day average is AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The frame clause is where candidates lose marks. If you write ORDER BY without a frame, most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is not the same as ROWS.

ROWS counts physical rows in the result set. RANGE counts logical values of the ordering expression, so all rows sharing the same ORDER BY value are treated as one peer group and get an identical result. On a table with one row per day the two are indistinguishable, which is why the bug hides, but on a table with several rows per day the default RANGE frame includes every row of the current day in the running total, and each of those rows shows the same end-of-day cumulative figure rather than an incremental one.

The second real trap is missing days. ROWS BETWEEN 6 PRECEDING counts six preceding rows, not six preceding days, so if the pipeline dropped a Sunday your seven day average silently becomes an eight day window. The robust pattern is to generate a complete date spine, left join the facts to it, coalesce to zero, and then window over the dense series. Some engines support RANGE BETWEEN INTERVAL '6' DAY PRECEDING, which solves it natively and is worth naming if you know your target warehouse supports it.

WITH spine AS (
  SELECT d FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2026-01-01', DATE '2026-08-01')) AS d
),
daily AS (
  SELECT s.d,
         COALESCE(SUM(o.amount), 0) AS revenue
  FROM   spine s
  LEFT JOIN orders o ON DATE(o.order_date) = s.d
  GROUP BY s.d
)
SELECT d,
       revenue,
       SUM(revenue) OVER (ORDER BY d
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(revenue) OVER (ORDER BY d
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS ma_7d
FROM   daily
ORDER BY d;

Answer

LAG(revenue) OVER (PARTITION BY city ORDER BY month) returns the previous row in the partition, not the previous month, and that distinction is the whole question. If Kochi has no orders in June, there is no June row, so the July row picks up May as its comparison base and reports a growth number that is arithmetically fine and factually wrong. Nothing errors, nothing looks odd on the chart, and the mistake survives review.

The fix is to build the full grid before you window: generate a month spine, cross join it to the distinct city list, left join the facts onto that grid, and COALESCE the revenue to zero. Now every city has a row for every month and LAG means what you think it means. Three further details interviewers listen for.

LAG takes an optional third argument for the default, so LAG(revenue, 1, 0) avoids a NULL on the first month of each partition, though a zero base then makes the growth ratio undefined, which is why you wrap the denominator in NULLIF and accept a NULL rather than printing infinity. LAG and LEAD ignore the frame clause entirely, so ROWS and RANGE are irrelevant to them, unlike SUM or AVG. And the ORDER BY inside OVER is independent of the query's final ORDER BY, so sorting the output differently does not change the computed values, which trips up people who think the window follows the display order. LEAD is the mirror image and is how you compute time to next order per customer for a repeat purchase analysis.

WITH months AS (
  SELECT d AS month
  FROM   UNNEST(GENERATE_DATE_ARRAY(DATE '2026-01-01', DATE '2026-08-01',
                                    INTERVAL 1 MONTH)) AS d
),
cities AS (SELECT DISTINCT city FROM orders),
grid   AS (SELECT c.city, m.month FROM cities c CROSS JOIN months m),
fact   AS (
  SELECT g.city,
         g.month,
         COALESCE(SUM(o.amount), 0) AS revenue
  FROM   grid g
  LEFT JOIN orders o
         ON o.city = g.city
        AND DATE_TRUNC(DATE(o.order_date), MONTH) = g.month
  GROUP BY 1, 2
)
SELECT city,
       month,
       revenue,
       LAG(revenue) OVER w AS prev_revenue,
       SAFE_DIVIDE(revenue - LAG(revenue) OVER w,
                   NULLIF(LAG(revenue) OVER w, 0)) AS mom_growth
FROM   fact
WINDOW w AS (PARTITION BY city ORDER BY month)
ORDER BY city, month;

Answer

Before writing SQL, settle three definitions, because every disagreement traces back to one of them. What is the unit, a user or a session, since a user who browses on Tuesday and buys on Friday counts as converted on a user funnel and as two sessions with one dropping out on a session funnel. What is the window, same session, twenty four hours, or seven days.

And must the steps happen in order, because if you simply count distinct users per event you can produce a funnel where checkout has more users than add to cart, which happens genuinely in India through push notification deep links and abandoned cart SMS that drop people mid funnel. The implementation that holds up is conditional aggregation to the first timestamp of each step per user, then enforcing monotonic ordering between those timestamps. Report both step to step conversion and conversion from the top of the funnel, and label which one each number is.

Then the operational gotchas: client SDKs retry on flaky mobile networks so the same event arrives twice and you must dedupe on the event id or insert id before counting; mobile apps queue events offline and flush on next launch, so yesterday's funnel keeps changing for two or three days and any alert on it needs a lag; and a release that renamed add_to_cart to cart_add creates a cliff on the release date that looks like a product disaster. Say all of that out loud in the interview, because the point of the question is whether you treat instrumentation as evidence or as truth.

WITH deduped AS (
  SELECT * EXCEPT(rn) FROM (
    SELECT e.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at) AS rn
    FROM   events e
    WHERE  event_ts >= TIMESTAMP '2026-07-01 00:00:00'
  ) WHERE rn = 1
),
steps AS (
  SELECT user_id,
         MIN(CASE WHEN event_name = 'view_item'      THEN event_ts END) AS t_view,
         MIN(CASE WHEN event_name = 'add_to_cart'    THEN event_ts END) AS t_cart,
         MIN(CASE WHEN event_name = 'begin_checkout' THEN event_ts END) AS t_chk,
         MIN(CASE WHEN event_name = 'purchase'       THEN event_ts END) AS t_buy
  FROM   deduped
  GROUP BY user_id
)
SELECT SUM(CASE WHEN t_view IS NOT NULL                     THEN 1 ELSE 0 END) AS s1_view,
       SUM(CASE WHEN t_cart >= t_view                       THEN 1 ELSE 0 END) AS s2_cart,
       SUM(CASE WHEN t_chk  >= t_cart                       THEN 1 ELSE 0 END) AS s3_checkout,
       SUM(CASE WHEN t_buy  >= t_chk                        THEN 1 ELSE 0 END) AS s4_purchase
FROM   steps;

Answer

GA4 has no pageview-centric model at all: everything is an event, and a session is a derived construct. A session_start event opens a session, every subsequent event carries a ga_session_id parameter, and the session closes after thirty minutes of inactivity by default, adjustable in the property's data settings. An engaged session is one that lasted at least ten seconds, or fired a key event, or had at least two screen or page views, and engagement rate is engaged sessions over sessions, with bounce rate defined as its complement rather than as the old single-pageview definition.

Users split into total users and active users, and the default reporting metric is active users, which is why GA4 will usually show a smaller user count than a naive distinct count of your own visitors. Then the reasons your database disagrees. Reporting identity blends user id, Google signals, device id and modelling, so cross-device users collapse into one where your logs see two.

Consent mode and browser restrictions mean some sessions are modelled rather than observed. Standard reports apply a cardinality limit and roll rare dimension values into an (other) row, explorations sample above a large event ceiling, and rows can be withheld entirely by data thresholding when demographics or Google signals are in play. Attribution is channel-scoped and last-click by default for key events, so a purchase your database credits to a coupon code lands under organic in GA4. The professional answer ends with the escape route: enable the BigQuery export and compute from event level rows, where a session is the concatenation of user_pseudo_id and ga_session_id, and reconcile to your own orders table on transaction_id.

-- GA4 BigQuery export: a session is user_pseudo_id + ga_session_id
SELECT COUNT(DISTINCT user_pseudo_id) AS users,
       COUNT(DISTINCT CONCAT(
         user_pseudo_id, '-',
         CAST((SELECT value.int_value FROM UNNEST(event_params)
               WHERE key = 'ga_session_id') AS STRING))) AS sessions,
       COUNTIF(event_name = 'purchase')  AS purchases
FROM   `myproject.analytics_123456789.events_*`
WHERE  _TABLE_SUFFIX BETWEEN '20260701' AND '20260731';

Answer

None of them is wrong, they are answering different questions, and the interviewer wants to hear you say that before you start diagnosing. Meta counts conversions it believes its ads caused, credited to the day of the ad interaction rather than the day of the purchase, inside its attribution setting, commonly seven day click plus one day view. View-through conversions alone can explain a large gap, since a user who saw the creative and later searched your brand name counts for Meta and counts as organic everywhere else.

Post ATT, part of Meta's number is modelled rather than observed. GA4 credits by channel using its own model, drops view-through entirely, loses the session when UTMs are missing or stripped by a redirect, loses cross-device journeys unless user id is set, and is subject to browser storage limits. Your database is the only system that knows how many orders truly exist, but even it needs a definition, since a raw order count includes cancellations, failed payments retried into duplicate rows and COD orders that will be returned.

Then the boring causes that account for a surprising share of the discrepancy: the Meta ad account timezone, the GA4 property timezone and your UTC database cut the day at three different moments, and currency or value fields may include GST in one place and exclude it in another. Also check Conversions API deduplication, because if the browser pixel and the server event do not share the same event_id, every purchase is counted twice. The recommendation to close with is operational: pick one system of record per decision, the database for revenue reporting, the platform for bid optimisation, and store fbclid or gclid at checkout so you can join click to order and measure the overlap directly.

-- Store the click id at checkout, then reconcile properly
SELECT DATE(o.created_at, 'Asia/Kolkata')                       AS order_day_ist,
       COUNT(*)                                                 AS db_orders,
       COUNTIF(o.fbclid IS NOT NULL)                            AS orders_with_meta_click,
       COUNTIF(o.gclid  IS NOT NULL)                            AS orders_with_google_click,
       COUNTIF(o.fbclid IS NULL AND o.gclid IS NULL)            AS unattributed
FROM   orders o
WHERE  o.status = 'confirmed'
  AND  o.created_at >= TIMESTAMP '2026-07-01 00:00:00'
GROUP BY 1
ORDER BY 1;

Answer

Start by separating blended CAC, total sales and marketing spend divided by all new customers, from paid CAC, paid media spend divided by customers attributed to paid. The gap between them tells you how much organic and word of mouth is subsidising the paid engine, and a founder who only quotes blended CAC while scaling spend is hiding a problem. Include everything in the numerator that finance will include: agency retainers, creative production, tooling, the marketing team's salaries when the CFO defines it that way, and acquisition coupons.

On LTV, the mistake that gets analysts corrected in the meeting is using revenue. LTV must be contribution: revenue minus cost of goods, payment gateway charges, shipping, returns and RTO, and support cost. In Indian ecommerce, return to origin on cash on delivery orders can wipe out an entire cohort's apparent margin, so RTO belongs in the model, not in a footnote.

Prefer cohort LTV measured at fixed horizons, contribution per acquired customer at month three, six and twelve, over the textbook ARPU divided by churn formula, which assumes a constant hazard rate and explodes as churn approaches zero. Payback is the month at which cumulative contribution crosses CAC, and it is the metric that actually governs how fast you can spend, because a healthy three to one LTV to CAC ratio with a thirty month payback still runs the company out of cash. Finally, be explicit about censoring: a cohort acquired in May 2026 cannot have a twelve month number yet, so either leave the cell blank or state the maturation assumption you used to project it.

WITH first_order AS (
  SELECT user_id,
         DATE_TRUNC(MIN(order_date), MONTH) AS cohort_month
  FROM   orders GROUP BY user_id
),
contribution AS (
  SELECT f.cohort_month,
         DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), f.cohort_month, MONTH) AS m_idx,
         SUM(o.net_revenue - o.cogs - o.shipping_cost - o.gateway_fee
             - o.rto_cost)                                                 AS contrib
  FROM   orders o JOIN first_order f USING (user_id)
  GROUP BY 1, 2
),
spend AS (SELECT month, paid_spend, new_paid_customers FROM marketing_monthly)
SELECT c.cohort_month,
       s.paid_spend / NULLIF(s.new_paid_customers, 0) AS paid_cac,
       SUM(c.contrib) OVER (PARTITION BY c.cohort_month
                            ORDER BY c.m_idx)         AS cum_contribution,
       c.m_idx
FROM   contribution c JOIN spend s ON s.month = c.cohort_month
ORDER BY 1, 4;

Answer

Most breakages come from steps the UI generated for you that hard code column names or positions. The Changed Type step lists every column by name, so a supplier who renames Invoice Amt to Invoice Amount produces the error message that the column of the table was not found. Removed Columns and Reordered Columns behave the same way.

The defensive versions are Table.SelectColumns with MissingField.UseNull so an absent column becomes nulls rather than a failure, Table.PromoteHeaders applied explicitly rather than relying on the sample file, and selecting columns by name only, never by index. For a file that arrives monthly, do not point at a single path. Use Folder.Files against the drop folder, filter out temporary lock files whose names start with a tilde and dollar sign, and let the sample file function apply the same transform to every workbook so a new month needs no edit.

Unpivot Other Columns rather than Unpivot Columns, because the former adapts when a new month column appears and the latter pins the names it saw once. Dates need a locale on the type change, since an Indian file written as DD/MM/YYYY parsed under en-US turns 05/07/2026 into 7 May without complaint, and Table.TransformColumnTypes accepts a culture argument for exactly this. Two more things worth naming: query folding, where transformations are pushed down to a SQL source until you use something that cannot be translated, and you check by looking at whether View Native Query is greyed out; and the Formula.Firewall error about a query referencing other queries, which comes from mixed privacy levels and is fixed by setting the sources to the same level or restructuring the query rather than by disabling checks blindly.

let
    Source     = Folder.Files("C:\finance\monthly"),
    RealFiles  = Table.SelectRows(Source, each
                   Text.EndsWith([Name], ".xlsx")
                   and not Text.StartsWith([Name], "~$")),
    Loaded     = Table.AddColumn(RealFiles, "Data", each
                   Excel.Workbook([Content], true)),
    Expanded   = Table.ExpandTableColumn(Loaded, "Data", {"Data"}, {"Sheet"}),
    Combined   = Table.Combine(Expanded[Sheet]),
    Safe       = Table.SelectColumns(Combined,
                   {"InvoiceDate", "City", "Amount"}, MissingField.UseNull),
    Typed      = Table.TransformColumnTypes(Safe,
                   {{"InvoiceDate", type date}, {"Amount", type number}}, "en-IN")
in
    Typed

Answer

A DAX measure is not summed down the column. It is re-evaluated once per cell in whatever filter context that cell carries, and the total row is simply the same measure evaluated with the row-level filters removed. So a measure that returns a ratio, a MAX, or an IF over an aggregate will produce a total that is mathematically correct for the total's filter context and looks wrong next to the rows.

The fix depends on intent. If the total should be the sum of the row results, iterate explicitly with SUMX over the dimension table so the measure is evaluated per row and then added. If the total is genuinely undefined, detect it with HASONEVALUE or ISINSCOPE and return BLANK rather than a misleading figure.

CALCULATE is the function that makes all of this tractable, because it evaluates an expression in a modified filter context. Its filter arguments replace any existing filter on the same column, which surprises people, and KEEPFILTERS is how you intersect instead of replace. REMOVEFILTERS, the modern name for the ALL pattern, clears filters for percent of total calculations, while ALLSELECTED respects what the user picked in slicers but ignores the visual's own grouping.

The concept interviewers test hardest is context transition: when CALCULATE appears inside an iterator such as SUMX, the current row context is converted into an equivalent filter context, which is also why a measure referenced inside an iterator behaves as if it were wrapped in CALCULATE. Two practical notes: use DIVIDE rather than the slash operator so division by zero returns BLANK instead of an error, and mark a contiguous date table as the date table or time intelligence functions like SAMEPERIODLASTYEAR quietly return blank.

-- Wrong at the total: a ratio of totals is not the total of ratios
Margin % = DIVIDE ( [Total Margin], [Total Revenue] )

-- Correct when the total must equal the sum of row results
Weighted Margin =
SUMX (
    VALUES ( 'Product'[Category] ),
    CALCULATE ( DIVIDE ( [Total Margin], [Total Revenue] ) ) * [Total Revenue]
)

-- Percent of total, ignoring the visual's own grouping
Pct of All Categories =
DIVIDE ( [Total Revenue],
         CALCULATE ( [Total Revenue], REMOVEFILTERS ( 'Product'[Category] ) ) )

-- Suppress a meaningless total instead of printing a confusing one
Latest Price = IF ( HASONEVALUE ( 'Product'[SKU] ), [Price], BLANK () )

Answer

Level of detail expressions let a calculation run at a granularity different from the view. FIXED computes at the dimensions you name and ignores the view entirely, so {FIXED [Customer ID] : MIN([Order Date])} gives each customer's acquisition date no matter what is on rows and columns, which is the standard way to build cohorts. INCLUDE adds dimensions below the view's level, so AVG({INCLUDE [Customer ID] : SUM([Sales])}) gives the average customer spend even when the view is aggregated by region.

EXCLUDE removes dimensions from the view's level and is how you show each row against its category total. The part that separates people who have used Tableau in anger from people who have read about it is the order of operations. Extract filters run first, then data source filters, then context filters, then FIXED expressions, then dimension filters, then INCLUDE and EXCLUDE, then measure filters, then table calculations and their filters.

The consequence is concrete: a quick filter on Region does not affect a FIXED calculation, so your customer count stays at the national figure while every other number responds to the filter, and users report the dashboard as broken. Promoting that filter to a context filter puts it ahead of the FIXED expression and restores the behaviour people expect. Two further points earn credit.

An LOD returns a value that is then aggregated again in the view, so you wrap it in SUM, AVG or ATTR deliberately rather than accidentally. And FIXED over a very high cardinality dimension against a live connection generates a large subquery per refresh, so on big tables an extract or a pre-aggregated view in the warehouse is the better answer.

// Acquisition cohort, immune to the view's dimensions
{ FIXED [Customer ID] : MIN([Order Date]) }

// Average spend per customer, even in a region level view
AVG( { INCLUDE [Customer ID] : SUM([Sales]) } )

// Each product against its category total
SUM([Sales]) / SUM( { EXCLUDE [Product] : SUM([Sales]) } )

// Repeat buyers: count customers with more than one order
COUNTD( IF { FIXED [Customer ID] : COUNTD([Order ID]) } > 1
        THEN [Customer ID] END )

Answer

agg collapses each group to a single row, so it answers questions about groups. transform returns a Series aligned to the original index, so it answers questions about rows relative to their group, which is what you need for share of category revenue, days since a user's first order, or z-scoring within a city. Reaching for apply instead is the common weakness: it is slower, and its return shape depends on what the function returns, so the same code produces a Series in one dataset and a DataFrame in another. On merges, three defaults cause real damage.

First, merge does not check key uniqueness, so a many to many join fans out and your revenue triples, which is why validate='m:1' or 'one_to_one' belongs on every merge you write, because it raises MergeError at the point of the mistake rather than producing a plausible number. Second, indicator=True adds a _merge column so you can count left_only and right_only rows and actually see what failed to match, instead of discovering a twenty percent match rate three slides later. Third, dtype mismatch: a key read as int64 in one frame and object in the other either raises or matches nothing, and NaN keys never match anything, including other NaNs.

Finally, chained assignment. Writing df[df.city == 'Pune']['discount'] = 0 operates on a temporary copy, and historically pandas warned with SettingWithCopyWarning while sometimes appearing to work. Copy on write, available from pandas 2.0 and the default in 3.0, removes the ambiguity by making the write never propagate, so the fix is the same as it always was: use .loc with a row mask and a column label in one call.

import pandas as pd

# agg: one row per group
by_city = orders.groupby('city', as_index=False).agg(
    gmv=('amount', 'sum'), orders=('order_id', 'nunique'))

# transform: one value per original row, aligned to the index
orders['city_gmv']   = orders.groupby('city')['amount'].transform('sum')
orders['share_of_city'] = orders['amount'] / orders['city_gmv']
orders['first_order'] = orders.groupby('user_id')['order_date'].transform('min')

# merge defaults that hide bugs
joined = orders.merge(
    customers,
    on='user_id',
    how='left',
    validate='m:1',      # raises MergeError if customers.user_id is not unique
    indicator=True,      # adds _merge so you can audit the match rate
)
print(joined['_merge'].value_counts())

# assign through .loc, never through a chained selection
joined.loc[joined['city'] == 'Pune', 'discount'] = 0

Answer

That column holds the tier as of today, so joining it rewrites history. A customer who was Silver through 2024 and was upgraded to Gold last month has every one of their 2024 orders relabelled as Gold revenue, which makes Gold look like it was always the profitable segment and makes Silver look worse than it was. The effect is a form of survivorship: the segments that get credit are the ones people graduated into.

The first thing to do is ask the stakeholder which question they actually mean, because both are legitimate. Revenue by tier at the time of the order tells you how the business performed. Revenue by current tier tells you how much historical revenue today's Gold customers represent, which is a valid input to a retention pitch.

They are different numbers and must be labelled differently. To answer the first one you need history, which means a slowly changing dimension of type 2: one row per customer per tier period, with valid_from and valid_to columns and an is_current flag, and a point in time join where the order date falls inside the interval. Use half-open intervals, valid_from inclusive and valid_to exclusive, so an order placed on the switching day is counted exactly once, and use a far-future sentinel such as 9999-12-31 for the live row so the between condition needs no special case.

Test the dimension for gaps and overlaps, because a broken SCD duplicates revenue quietly. If the warehouse only overwrites the tier, say plainly that history cannot be reconstructed, and start snapshotting the dimension daily from now, which is what dbt snapshots exist for.

-- Point in time join against a type 2 dimension
SELECT DATE_TRUNC(o.order_date, MONTH) AS month,
       d.tier                           AS tier_at_order_time,
       SUM(o.amount)                    AS revenue
FROM   orders o
JOIN   customer_tier_history d
       ON  d.customer_id = o.customer_id
       AND o.order_date >= d.valid_from
       AND o.order_date <  d.valid_to      -- half open, no double counting
GROUP BY 1, 2
ORDER BY 1, 2;

-- Integrity test: any customer with overlapping validity windows is a bug
SELECT customer_id, COUNT(*) AS overlaps
FROM   customer_tier_history a
JOIN   customer_tier_history b USING (customer_id)
WHERE  a.valid_from < b.valid_to
  AND  b.valid_from < a.valid_to
  AND  a.valid_from <> b.valid_from
GROUP BY customer_id;

Answer

IST is UTC plus five hours thirty minutes, so DATE(created_at) on a UTC column cuts the day at 05:30 IST. Everything that happened between midnight and half past five in the morning India time is filed under the previous date. For a food delivery or gaming business that late night window is not small, and for a payments business the reconciliation will not tie out with the bank's own day.

The fix is to convert once, explicitly, at the reporting boundary, and to keep storage in UTC forever: DATE(ts, 'Asia/Kolkata') in BigQuery, CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', ts) in Snowflake, ts AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' in PostgreSQL. Use the IANA name rather than a hard coded plus five thirty, because a literal offset is invisible to anyone reading the query and does not generalise when the same model runs for another market. Several second order problems come with it.

The half hour offset breaks tools and libraries that quietly assume whole hour zones. India has no daylight saving, which helps, but any comparison against a US or European counterpart shifts twice a year, so a fixed offset mapping drifts. Week boundaries differ by engine and by convention, since DATE_TRUNC to week starts on Monday in PostgreSQL and BigQuery's WEEK defaults to Sunday, so two teams can produce different week on week numbers from the same data and both be right.

And your GA4 property, your ad accounts and your warehouse may each be set to different zones, which alone explains a few percent of daily discrepancy. Agree one reporting timezone for the whole company and put the conversion in the semantic layer, not in forty separate dashboards.

-- BigQuery: IST calendar day from a UTC timestamp column
SELECT DATE(created_at, 'Asia/Kolkata')            AS day_ist,
       COUNT(*)                                    AS orders,
       SUM(amount)                                 AS gmv
FROM   orders
WHERE  created_at >= TIMESTAMP('2026-07-01 00:00:00', 'Asia/Kolkata')
  AND  created_at <  TIMESTAMP('2026-08-01 00:00:00', 'Asia/Kolkata')
GROUP BY 1 ORDER BY 1;

-- Snowflake equivalent
SELECT TO_DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', created_at)) AS day_ist,
       COUNT(*) AS orders
FROM   orders GROUP BY 1;

-- PostgreSQL, and an explicit Monday week start
SELECT DATE_TRUNC('week', (created_at AT TIME ZONE 'UTC')
                          AT TIME ZONE 'Asia/Kolkata')::date AS week_start_mon
FROM   orders;

Answer

On BigQuery in on-demand pricing you pay for bytes scanned, so the levers are all about reading less. Stop selecting star, because BigQuery is columnar and every unused column is money; naming twelve columns instead of two hundred can cut a query by an order of magnitude. Partition the fact table on the event or order date and filter on the partition column with a literal or a constant expression, because wrapping it in a function, for example filtering on DATE(created_at) when the partition is on the timestamp, can prevent pruning entirely and scan the full table.

Cluster on the columns you filter and group by most, typically a high cardinality id or city. Note the trap that LIMIT does not reduce bytes scanned, it only truncates the output, which surprises people who think they are testing cheaply. Use the dry run estimate before executing anything large, prefer APPROX_COUNT_DISTINCT over exact distinct counts on hundreds of millions of rows, and materialise the daily aggregate that the dashboard reads instead of pointing thirty tiles at a raw event table.

Snowflake bills warehouse time rather than bytes, so the same query costs you seconds of compute. There the levers are right-sizing the virtual warehouse, setting auto suspend low, relying on the result cache which serves an identical query for twenty four hours if the underlying data has not changed, defining cluster keys on very large tables, and separating the BI workload onto its own warehouse so an analyst's ad hoc scan does not queue behind it. Both platforms reward the same architectural move: precompute the aggregate, let dashboards hit a small table, and reserve raw scans for genuine exploration.

-- Expensive: reads every column and defeats partition pruning
SELECT * FROM `proj.ds.events`
WHERE DATE(event_timestamp) BETWEEN '2026-07-01' AND '2026-07-31';

-- Cheap: two columns, filter directly on the partition column
SELECT user_pseudo_id, event_name
FROM   `proj.ds.events`
WHERE  event_timestamp >= TIMESTAMP '2026-07-01'
  AND  event_timestamp <  TIMESTAMP '2026-08-01'
  AND  event_name IN ('purchase', 'begin_checkout');

-- Estimate before you run
-- bq query --dry_run --use_legacy_sql=false 'SELECT ...'

-- Approximate distinct on very large tables
SELECT APPROX_COUNT_DISTINCT(user_pseudo_id) AS approx_users
FROM   `proj.ds.events`
WHERE  event_timestamp >= TIMESTAMP '2026-07-01';

Answer

Work a fixed order, because panic reordering is how analysts waste the hour. First, is the drop real. Check pipeline freshness and hourly row counts, whether the ETL job completed, and whether an upstream schema change dropped rows silently.

A meaningful share of dramatic dashboard drops are broken pipelines, and confirming that first saves everyone. Second, did the definition change. Look at the last deploy of the transformation models and the dashboard's own edit history, because a filter someone added at 6pm yesterday looks exactly like a business collapse.

Third, cut by dimension rather than theorising: platform and app version, payment method, city, new versus repeat, and traffic source. If the drop is concentrated in one cut, for example Android app version 8.4.1 or UPI transactions on one PSP, you have a technical cause and can hand it to engineering with evidence. If it is spread evenly across every cut, the cause is upstream of the product, in demand or acquisition.

Fourth, locate the funnel step: sessions flat with checkout down points at payments or pricing, sessions down points at marketing spend, an app store issue, or a search ranking change. Fifth, check the outside world, since in India a single evening can be explained by a marquee cricket match, a heavy rain day in Mumbai, a bank or UPI outage, or a competitor's sale going live. Sixth, compare like with like, day of week against the same weekday last week rather than against yesterday, and check whether the comparison day was itself inflated by a campaign. Then communicate before you have the answer: a short note saying what moved, what is ruled out and when the next update lands buys you the time to be right.

-- Fast dimensional cut: which slice moved, and is it mix or rate
WITH d AS (
  SELECT DATE(created_at, 'Asia/Kolkata') AS day_ist,
         platform, app_version, payment_method, city,
         COUNT(*) AS orders, SUM(amount) AS gmv
  FROM   orders
  WHERE  created_at >= TIMESTAMP('2026-08-01', 'Asia/Kolkata')
  GROUP BY 1,2,3,4,5
)
SELECT platform, app_version, payment_method,
       SUM(CASE WHEN day_ist = DATE '2026-08-10' THEN gmv END) AS gmv_yday,
       SUM(CASE WHEN day_ist = DATE '2026-08-03' THEN gmv END) AS gmv_same_dow_lw,
       SAFE_DIVIDE(
         SUM(CASE WHEN day_ist = DATE '2026-08-10' THEN gmv END),
         SUM(CASE WHEN day_ist = DATE '2026-08-03' THEN gmv END)) - 1 AS delta
FROM   d
GROUP BY 1,2,3
ORDER BY gmv_same_dow_lw DESC
LIMIT 30;

Answer

Start with RFM, because it is interpretable, cheap and actionable: recency as days since the last order, frequency as orders in a defined window, and monetary as contribution rather than revenue. Score each dimension into quintiles with NTILE(5) computed within the active base, then name the cells in business language, champions, loyal, at risk, hibernating, so a marketer can build a campaign from the definition without asking you what it means. RFM also has the property that the same customer's movement between cells over time is itself a metric.

Reach for clustering when you have many correlated behavioural features that no rule set captures well, for example browsing depth, category spread, discount sensitivity, return rate and session timing. If you do, the preparation matters more than the algorithm: scale the features, or monetary value in rupees will dominate recency in days entirely, log transform the skewed ones, use k-means++ initialisation with a fixed random seed so the result is reproducible, and pick k using silhouette or the elbow only as a starting point, then choose the k whose clusters you can describe in a sentence each. k-means is the wrong tool in several common situations: when the features are mostly categorical, since Euclidean distance on one hot columns is close to meaningless; when the segments must stay stable across a campaign calendar, because retraining reshuffles cluster membership and last month's Segment 3 is not this month's Segment 3; and when the real question is a prediction, such as who will churn or who will respond, where a supervised model with a proper holdout is the honest answer. Validate any segmentation by running one campaign against it and measuring differential response, because a segmentation nobody acts on has not been delivered.

-- RFM scoring with NTILE, recency reversed so 5 is always good
WITH base AS (
  SELECT customer_id,
         DATE_DIFF(DATE '2026-08-11', MAX(order_date), DAY) AS recency_days,
         COUNT(DISTINCT order_id)                           AS frequency,
         SUM(contribution)                                  AS monetary
  FROM   orders
  WHERE  order_date >= DATE '2025-08-11'
  GROUP BY customer_id
)
SELECT customer_id, recency_days, frequency, monetary,
       NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
       NTILE(5) OVER (ORDER BY frequency)         AS f_score,
       NTILE(5) OVER (ORDER BY monetary)          AS m_score
FROM   base;

Answer

This is Simpson's paradox, and the mechanism is mix. The blended rate is a weighted average of segment rates, with weights equal to each segment's share of traffic. If volume shifts toward a structurally lower converting segment, the blended rate can fall even when every segment improved.

Concrete Indian version: a marketplace pushes a large upper funnel campaign that brings cheap tier 2 and tier 3 traffic which converts at one percent, while metro paid search converts at six percent and improved to six point three. Every channel got better, the mix got heavier in the weak one, and the blended number fell. The professional response is not to explain the paradox in words but to decompose the change arithmetically and show it as a waterfall.

Split the total delta into a rate effect, holding the old mix constant and applying the new rates, and a mix effect, holding old rates constant and applying the new weights, with the small residual as an interaction term. Now the conversation becomes concrete: the rate effect is plus forty basis points, the mix effect is minus ninety, and the question for the business is whether the new traffic is a deliberate acquisition investment with a longer payback or an inefficiency to cut. The same decomposition works on average order value split into units per order and price per unit, and on revenue split into buyers times frequency times basket.

Two guardrails to state. First, never report a blended rate without either the mix or a mix-adjusted version alongside it. Second, when someone asks whether a change is real, check whether the segment definitions themselves changed in the period, because a redefinition of channel produces the same signature as a genuine mix shift.

WITH s AS (
  SELECT channel,
         SUM(CASE WHEN period = 'Q1' THEN sessions  END) AS n0,
         SUM(CASE WHEN period = 'Q1' THEN converted END) AS c0,
         SUM(CASE WHEN period = 'Q2' THEN sessions  END) AS n1,
         SUM(CASE WHEN period = 'Q2' THEN converted END) AS c1
  FROM   channel_funnel GROUP BY channel
),
m AS (
  SELECT channel,
         c0 / n0 AS r0, c1 / n1 AS r1,
         n0 / SUM(n0) OVER () AS w0,
         n1 / SUM(n1) OVER () AS w1
  FROM   s
)
SELECT SUM(w0 * (r1 - r0))            AS rate_effect,
       SUM(r0 * (w1 - w0))            AS mix_effect,
       SUM((w1 - w0) * (r1 - r0))     AS interaction,
       SUM(w1 * r1) - SUM(w0 * r0)    AS total_change
FROM   m;

Answer

Randomise at the unit you can actually control, which for most ad platforms is geography, so the design is a geo holdout. Take your city or pincode cluster list, exclude the ones too small to measure, and form matched pairs or matched groups on the pre-period weekly sales trend rather than on level alone, because two cities with similar revenue but different seasonality are not comparable. Hold out roughly ten to twenty percent of geos for four to six weeks, long enough to cover the purchase cycle and at least one full weekly seasonality cycle.

The power calculation is the part candidates skip and interviewers care about: your sample size is the number of geos, not the number of users, so the variance that matters is between-geo weekly sales variance. This is why most geo tests are underpowered, and why you need a long pre-period and often a larger holdout than the business wants to give you. Analyse with difference in differences, regressing weekly sales on a treated indicator, a post indicator and their interaction, with standard errors clustered at the geo level, or with a synthetic control that builds a weighted blend of control geos matching the treated pre-trend when your groups are unbalanced.

Validate parallel trends with a placebo switch date inside the pre-period. Name the threats: spillover, since brand campaigns and organic word of mouth are not geo-clean and people travel; contamination from other campaigns still running in the holdout; and regional festivals such as Onam or Durga Puja hitting one group only. Finally, agree the decision rule and the acceptable incremental cost per acquisition before the test starts, because incremental ROAS almost always comes in well below platform reported ROAS, and the argument you want to avoid is the one about whether the test was fair.

-- Difference in differences on weekly geo sales
-- effect = (treated_post - treated_pre) - (control_post - control_pre)
WITH agg AS (
  SELECT geo_id,
         MAX(is_treated)                                   AS treated,
         SUM(CASE WHEN week >= DATE '2026-07-06' THEN sales END) AS post_sales,
         SUM(CASE WHEN week <  DATE '2026-07-06' THEN sales END) AS pre_sales
  FROM   geo_weekly_sales
  WHERE  week BETWEEN DATE '2026-05-04' AND DATE '2026-08-09'
  GROUP BY geo_id
)
SELECT AVG(CASE WHEN treated = 1 THEN post_sales - pre_sales END)
     - AVG(CASE WHEN treated = 0 THEN post_sales - pre_sales END) AS did_estimate,
       COUNT(CASE WHEN treated = 1 THEN 1 END) AS n_treated_geos,
       COUNT(CASE WHEN treated = 0 THEN 1 END) AS n_control_geos
FROM   agg;

Answer

Multi touch attribution needs a stitched user level path across sites and devices, and that is precisely what App Tracking Transparency, Safari's storage limits, consent frameworks and walled gardens have taken away. Even where the path exists, MTA is a credit allocation rule, not a causal estimate, whether it is last click, linear, time decay, position based or a Shapley style algorithmic model. Its structural bias is well known: it over-credits the bottom of the funnel, brand search and retargeting, which sit closest to the conversion, and under-credits awareness spend that created the demand in the first place.

Marketing mix modelling works on aggregate weekly series instead, regressing sales on spend by channel plus price, promotions, distribution, seasonality and competitor activity, with adstock transforms for carryover and a saturation curve for diminishing returns. Open implementations such as Meta's Robyn and Google's Meridian have made it reachable for mid-sized advertisers. Its limits are equally real: it wants two to three years of weekly history, it suffers badly from collinearity because budgets across channels move together, and it can only learn a response curve from variation that exists in the data, so an advertiser who never changes spend cannot get a useful saturation estimate.

The 2026 answer is triangulation, and saying so is the point of the question. Use geo experiments as the ground truth on a few channels, use those results to calibrate the MMM priors, which is now standard practice in the open source tools, use the MMM for cross-channel budget allocation on a quarterly cadence, and use platform reporting only for in-flight optimisation where its bias is less harmful. For an Indian advertiser below roughly five crore of annual media, a light MMM plus two geo tests per quarter is proportionate, and a full attribution rebuild is not.

Answer

Sizing comes from four inputs: the baseline rate, the minimum detectable effect you would actually act on, the significance level and the power. For a four percent checkout conversion baseline and a relative MDE of five percent at alpha 0.05 and eighty percent power, you need roughly a hundred and fifty thousand users per arm, and being able to state that order of magnitude in the room kills a lot of unrunnable test ideas before anyone builds them. On stopping early, the answer is no, and the reason is that a fixed horizon p-value is only valid if you evaluate it once at the planned end.

Checking daily and stopping at the first significant reading inflates the false positive rate far above the nominal five percent, because you are taking the maximum over many looks. There are two legitimate ways out. Pre-register the horizon and look only at guardrails until it is reached, or switch to always valid inference, meaning a sequential test such as mSPRT or a group sequential design with an alpha spending function, which is what modern experimentation platforms implement and which lets you peek by construction.

Before reading any result, check sample ratio mismatch: if assignment was fifty fifty and the observed split fails a chi squared test on a large sample, the experiment is invalid, and the usual causes are a redirect that loses one arm, bot filtering applied unevenly, or a logging failure. Then guard against novelty and primacy by running at least one full week and comparing week one against week two. On multiple comparisons, twenty metrics at alpha 0.05 guarantees a false winner, so declare one primary metric up front, treat the rest as guardrails, and apply Benjamini-Hochberg if you must test many. And an underpowered flat result is not proof of no effect: report the confidence interval and state the smallest effect you could have detected.

from statsmodels.stats.power import NormalIndPower
from statsmodels.stats.proportion import proportion_effectsize

baseline = 0.040          # 4% checkout conversion
mde_rel  = 0.05           # detect a 5% relative lift
treat    = baseline * (1 + mde_rel)

effect = proportion_effectsize(treat, baseline)
n_per_arm = NormalIndPower().solve_power(
    effect_size=effect, alpha=0.05, power=0.80, ratio=1.0, alternative='two-sided')
print(round(n_per_arm))   # users needed in EACH arm

# Sample ratio mismatch check before reading any result
from scipy.stats import chisquare
observed = [50_812, 49_188]
print(chisquare(observed, f_exp=[sum(observed)/2] * 2))

Answer

Because the randomisation unit and the analysis unit do not match. You assigned users to arms, but you are computing a statistic over sessions, and sessions belonging to the same user are correlated: a heavy user contributes twenty sessions that all share their behaviour and their treatment assignment. A t-test over sessions treats those twenty as twenty independent observations, so it understates the variance, narrows the confidence interval and manufactures significance.

Teams that get this wrong ship neutral changes as wins repeatedly before anyone notices. There are two defensible fixes and they answer slightly different questions. Aggregate to the randomisation unit, computing items per session for each user and then testing the mean across users, which gives every user equal weight.

Or keep the ratio of sums estimand, total items over total sessions, which weights users by activity, and compute its variance with the delta method, or by a cluster bootstrap that resamples users rather than sessions. Say out loud which estimand the business wants, because user-weighted and session-weighted results can point in opposite directions when heavy users respond differently. Three more things earn credit at this level.

Trigger analysis: include only users who actually reached the surface being tested, otherwise the effect is diluted by people who never saw it, and the dilution scales with how rare the surface is. Outliers: one bulk buyer can move the mean by more than the treatment does, so pre-declare a winsorisation rule at something like the 99th percentile rather than deciding after seeing the data. And variance reduction: CUPED, using each user's pre-period value of the same metric as a covariate, commonly cuts variance substantially and is close to free once implemented.

import numpy as np

def ratio_delta_method(items, sessions):
    """Variance of sum(items)/sum(sessions) with users as the cluster unit."""
    n  = len(items)
    mx, my = items.mean(), sessions.mean()
    r  = mx / my
    vx, vy = items.var(ddof=1), sessions.var(ddof=1)
    cxy = np.cov(items, sessions, ddof=1)[0, 1]
    var_r = (vx - 2 * r * cxy + r**2 * vy) / (n * my**2)
    return r, np.sqrt(var_r)

# items and sessions are per USER arrays, one row per randomised user
r_c, se_c = ratio_delta_method(items_ctrl, sess_ctrl)
r_t, se_t = ratio_delta_method(items_trt,  sess_trt)

lift = r_t - r_c
se   = np.sqrt(se_c**2 + se_t**2)
print(lift, lift - 1.96 * se, lift + 1.96 * se)

Answer

Start with cohort maturity, because it produces exactly this shape artificially. If the curve is an average across all cohorts by month index, then the month six point is computed only from cohorts old enough to have a month six, which is a different and usually older population than the one behind the month one point. Older cohorts often came from a period with a different acquisition mix, frequently more organic and more early adopter, and those users genuinely retain better.

So the tail is not the product's steady state, it is a vintage effect, and the fix is triangle discipline: for each month index include only cohorts that have fully matured for that index, leave immature cells blank rather than plotting them, and label the chart with an as-of date. Second, right censoring. A user who has not churned yet is not a permanently retained user, they are an incomplete observation, and averaging them in as successes overstates the plateau.

Survival analysis handles this correctly, with Kaplan-Meier estimating the retention function without discarding censored subjects, and it is worth noting that median lifetime is simply not estimable when fewer than half the cohort has churned. Third, the definition. A thirty day rolling active window mechanically produces a flatter curve than a seven day window, and counting any app open rather than a core action inflates the plateau in a way that has no commercial meaning.

Fourth, resurrection: if month N counts anyone active in that month regardless of the months between, a curve can rise again and confuse everyone, so state whether you are measuring continuous or point-in-time retention. A genuine flattening tail does exist and is the most valuable thing in the chart, but you have to earn the right to claim it.

-- Only show cells where the cohort is old enough to have that month index
WITH cohorts AS (
  SELECT user_id, DATE_TRUNC(MIN(order_date), MONTH) AS cohort_month
  FROM   orders GROUP BY user_id
),
act AS (
  SELECT c.cohort_month,
         DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), c.cohort_month, MONTH) AS m_idx,
         COUNT(DISTINCT o.user_id) AS retained
  FROM   orders o JOIN cohorts c USING (user_id)
  GROUP BY 1, 2
),
size AS (SELECT cohort_month, COUNT(*) AS cohort_size FROM cohorts GROUP BY 1)
SELECT a.cohort_month, a.m_idx,
       a.retained / s.cohort_size AS retention
FROM   act a JOIN size s USING (cohort_month)
-- maturity guard: the cohort must have completed m_idx whole months
WHERE  DATE_ADD(a.cohort_month, INTERVAL a.m_idx MONTH)
         < DATE_TRUNC(CURRENT_DATE('Asia/Kolkata'), MONTH)
ORDER BY 1, 2;

Answer

Do not argue about which number is right, build a bridge. Start from the product figure and subtract each identified difference in sequence until you land on finance's, then publish that bridge as the artefact. The differences are almost always from a known list.

GST included in one and excluded in the other. Gross order value versus net of discounts, coupons and cashback. Cancellations and returns, where finance recognises the credit note in the month it was issued and the product team nets it against the original order month.

Cash on delivery, where finance recognises on collection and product counts at order placement, which on Indian ecommerce volumes with meaningful return to origin is often the single biggest line. Marketplace revenue recognised as take rate while the dashboard shows GMV. Failed payments retried into duplicate order rows.

Internal and test orders that were never excluded. And the day boundary, where one system cuts at UTC and the other at IST. Once the bridge reconciles, the permanent fix is governance rather than another query.

Define each metric exactly once in a semantic layer, whether that is dbt semantic models, LookML or a similar layer, so gmv, net_revenue and recognised_revenue exist as three separate named metrics instead of three dashboards each calling its own number revenue. Give every certified metric a named owner and a written definition including the timezone and the exclusion rules. Flag internal users centrally on the user table rather than filtering by email pattern in each query. Then schedule the reconciliation as a job that runs monthly and alerts when the bridge exceeds a tolerance, so the next divergence is caught by a test instead of by a director in a board meeting.

-- The bridge, published as a table rather than argued in a thread
WITH b AS (
  SELECT 'product dashboard GMV'      AS line, 46000000 AS amount, 1 AS ord UNION ALL
  SELECT 'less GST collected',            -7020000, 2 UNION ALL
  SELECT 'less coupons and cashback',     -1850000, 3 UNION ALL
  SELECT 'less cancellations and RTO',    -2340000, 4 UNION ALL
  SELECT 'less internal and test orders',   -210000, 5 UNION ALL
  SELECT 'add COD collected from prior month', 6420000, 6
)
SELECT line, amount,
       SUM(amount) OVER (ORDER BY ord) AS running_to_finance
FROM   b ORDER BY ord;

Answer

Establish baselines first, because a forecast that cannot beat them has no business shipping: the naive forecast of last week, the seasonal naive of the same week last year, and a simple moving average. Then validate with rolling origin backtesting, training up to week t, predicting t plus one to t plus four, sliding forward and repeating, so the evaluation mirrors how the model will actually be used. Never use a random train test split on a time series, since it leaks the future into training.

Choose the error metric to match the decision: MAPE is undefined at zero and penalises over and under forecasting asymmetrically, so use weighted MAPE for aggregate demand and MASE when comparing across SKUs of different scale. If the output feeds inventory, forecast quantiles rather than the mean and evaluate with pinball loss, because a buyer needs the ninetieth percentile, not the expectation. The India specific failure is festival timing.

Diwali follows the lunar calendar, so it lands in October in one year and November in another, and any model with a plain annual seasonality term, whether that is a Fourier seasonality in Prophet or a seasonal ARIMA lag, will place the peak in the wrong week and then be wrong twice, once by missing the spike and once by predicting one that does not come. Handle festivals as explicit holiday regressors keyed to their actual dates, with a pre-festival ramp window and a post-festival slump window, because the dip afterwards is as real as the peak. Two more structural issues: platform sale events are business decisions rather than seasonality, so they belong in the model as regressors with known future dates; and stock-outs censor demand, since you observe sales and not what customers wanted, so mask or impute out-of-stock weeks before training.

import pandas as pd
import numpy as np

def rolling_origin_backtest(series, fit_predict, initial=104, horizon=4, step=4):
    """Walk-forward evaluation. series is a weekly pd.Series indexed by date."""
    rows = []
    for cut in range(initial, len(series) - horizon, step):
        train = series.iloc[:cut]
        truth = series.iloc[cut:cut + horizon]
        pred  = fit_predict(train, horizon)
        rows.append({
            'origin': series.index[cut],
            'wape': np.abs(truth.values - pred).sum() / truth.values.sum(),
        })
    return pd.DataFrame(rows)

# Festivals as dated regressors, never as annual seasonality
festivals = pd.DataFrame({
    'holiday': 'diwali',
    'ds': pd.to_datetime(['2023-11-12', '2024-11-01', '2025-10-20', '2026-11-08']),
    'lower_window': -21,   # pre-festival ramp
    'upper_window': 10,    # post-festival slump
})