Data Interview Questions

Note: Lead with the question, not the tools. "I used Python and SQL" describes a toolkit; "we could not tell which customers were about to churn" describes a problem worth solving.

Structure it as:

• The business question and who was asking it. Someone had to be waiting on the answer — say who, and what decision depended on it.
• The data. Where it came from, how much, and what was wrong with it. Realistically most of the work was here, and saying so is credible rather than a weakness.
• Your approach, briefly. The analysis or model, and — importantly — why that approach rather than a simpler one.
• The finding and what changed. An analysis nobody acted on is not a result. If it led nowhere, be honest about why, because that is a common and instructive experience.

If you found the data was unreliable and that was the real finding, tell that story. It happens constantly and few people report it.

Show a checking habit, because a confident wrong number is worse than no number.

• Sanity-check the totals against something independent. If your query says 4,200 orders last month, does the finance report agree? Reconciling against a source people already trust catches most errors immediately.
• Check the row count after every join. A join that silently multiplies rows is the single most common cause of inflated numbers in SQL analysis, and it produces results that look plausible.
• Look at the raw data, not just the aggregate. Averages hide duplicates, nulls, test records, and outliers. Spot-check individual rows.
• Question a surprising result before celebrating it. An unexpectedly strong finding is more often a bug than a discovery.
• Have someone else review the logic, particularly the filters and date ranges, which is where assumptions hide.
• Document your assumptions — which date field, which definition of "active", what was excluded — so a reviewer can challenge them.

Note: Being willing to say "I checked and my first number was wrong" is what earns trust over time. Analysts who never report an error are usually not checking.

This is a test of independence, so choose a story where you held your position.

• The finding and why it was unwelcome. A campaign that did not work, a product feature nobody used, a process assumed efficient that was not, or growth that came entirely from one source rather than the strategy everyone credited.
• How you verified it before presenting. This matters most. Being challenged and having already checked the tracking, the definitions, and alternative explanations is what makes the finding survive.
• How you presented it. Without drama, focused on what to do next rather than on who was wrong. Framing a negative finding as an opportunity — "we can stop spending here and redirect it" — makes it far easier to accept.
• The outcome, including if it was ignored. That is a legitimate ending, and how you handled it is the interesting part.

Note: Separating what the data shows from what you think it means, and being clear which is which, is what makes an unwelcome finding defensible. Overstating a conclusion gives people a reason to dismiss the whole thing.

Show that you interrogate the request rather than guessing and producing something unusable.

• Ask what decision it will inform. This single question resolves most vagueness. If the answer is "nothing specific", the request may not be worth doing, and saying so politely saves everyone time.
• Ask what they expect to see, and what they would do if the answer were the opposite. It reveals the real question, which is often narrower than what was asked.
• Agree definitions explicitly. "Active user", "revenue", and "customer" mean different things to different teams, and a disagreement discovered after presenting destroys the work.
• Confirm scope and timeframe — which period, which segment, which markets.
• Show something rough early. People cannot specify an analysis in the abstract but react immediately to a draft. A quick first cut with caveats surfaces the misunderstanding while it is cheap to fix.

Note: Mentioning that you write the agreed question back to the requester in one sentence before starting is a simple, concrete practice that prevents most rework — and demonstrates that you have been burned by this before.

How you learn: working on real problems rather than tutorials, because clean tutorial datasets teach none of the skills that matter. Beyond that, documentation and release notes for the tools you use, and reading other people's analysis critically.

How you decide what to learn:

• Fundamentals repay more than tools. SQL, statistics, and knowing how to frame a question have not changed and will not. Most analytical errors are reasoning errors, not tooling errors.
• Learn what your bottleneck is. If you are limited by data access, learn more SQL and data modelling. If you are limited by people not acting on your work, learn communication and visualisation. Learning a new modelling technique rarely addresses either.
• Follow the direction of the role. Analytics engineering, version control, and testing practices have moved from software into data work, and they are increasingly expected.

Note: A credible and refreshing answer is that most business value comes from clean data and well-framed simple analysis rather than sophisticated methods. Being able to say you deliberately chose the simpler approach shows judgement, which is harder to teach than technique.

The interviewer is checking whether you prioritise by business impact rather than by who shouts loudest, and whether you communicate trade-offs openly. Use a STAR structure and make your prioritisation logic explicit.

• Situation — set the scene briefly: “In the week before the quarterly business review I had three requests at once: a revenue bridge for the CFO, a campaign readout for marketing and a churn cut for the product team.”
• Task — you were the only analyst supporting those teams and every request was labelled urgent.
• Action — walk through the framework you used:
  • Asked each requester what decision the analysis fed and when that decision was actually being made. The churn cut was for a planning meeting two weeks away.
  • Scored the requests on impact and effort. The CFO bridge unblocked a board-level decision, so it went first.
  • Negotiated scope: marketing received a quick directional readout from an existing dashboard the same day, with the full analysis promised for the following week.
  • Made the queue visible in a shared tracker, so everyone could see where their request stood and why.
• Result — every request landed before the decision it supported, and the tracker became the team's standard intake process.

Strong candidates also mention escalation: if two senior stakeholders both insist on going first, ask your manager to arbitrate instead of silently choosing a winner.

Note: Avoid answers that boil down to “I worked late and did everything”. Interviewers want to see judgement, negotiation and transparency, not heroics.

This question tests integrity and ownership. The worst answer is one where the error was quietly fixed and nobody was told. Structure your reply around four moves: contain, communicate, correct, prevent.

• Contain — once you confirmed the error, you sized it quickly. Which numbers were wrong, since when, and by how much? Example: “A join on a non-unique customer key had been double-counting repeat orders for six weeks, overstating revenue in one region by about 8 percent.”
• Communicate — you told the people relying on the report before they found it themselves. Give the magnitude, the period affected and whether any decisions were at risk. Put a clear banner on the dashboard until it was fixed.
• Correct — you fixed the logic, restated the historical figures and had a peer review the change. Share a short note showing old versus corrected values.
• Prevent — you added a safeguard so it cannot recur: a row-count check before and after the join, a reconciliation against the finance ledger, or an automated test in the pipeline.

Close with the result and what you learned: stakeholders trusted the dashboard more afterwards because the issue was handled transparently, and the new check caught a similar issue three months later.

Note: Own the mistake even if a colleague or an upstream system caused it. Interviewers listen for “I” statements about fixing it, not blame.

Interviewers want to see that you can translate analysis into a decision. Describe your approach, then back it with a real example.

1. Lead with the answer. Start with the headline and the recommendation, not the method. “Delivery delays, not pricing, drove most of last quarter's churn. I recommend we fix the two worst courier routes first.”
2. Give two or three supporting points. Use numbers that anchor the claim: customers with a late delivery churned at 18 percent against 6 percent for everyone else, and the two routes account for 60 percent of late deliveries.
3. Show one simple visual. A single bar chart or before-and-after comparison beats a dashboard tour.
4. Be honest about confidence. State what the data does and does not prove in one sentence, for example that this is correlational but consistent across three months and two cities.
5. End with the ask. The decision, owner or budget you need.

Keep the methodology, caveats and extra cuts in an appendix or a follow-up note, so you can answer detailed questions without leading with them. Avoid jargon such as p-values or confidence intervals unless the leader asks; say “we are fairly confident” and explain the size of the effect in rupees or customers.

In your example, mention what happened next: the leader approved the fix, and churn on those routes fell the following quarter.

Note: The pyramid principle — answer first, then the supporting evidence — is the single most useful habit for executive communication.

This question checks initiative and whether you measure the value of your own work. Choose an example with a clear before and after, and quantify both.

• Situation — describe the manual process: “Every Monday the sales ops team downloaded five CSV exports, pasted them into an Excel workbook and fixed formulas by hand. It took about six hours and errors crept in most weeks.”
• Task — you noticed the cost and volunteered to fix it, or were asked to.
• Action — explain the technical choices briefly and the stakeholder work clearly:
  • Mapped every step and confirmed which outputs people actually used; two of the eight tabs were never opened.
  • Rebuilt the logic in SQL views or a Python script with pandas, scheduled to refresh automatically.
  • Replaced the workbook with a Power BI or Tableau dashboard, with the same layout at first so users were not disrupted.
  • Added validation checks and an alert if a source file was missing or row counts dropped sharply.
  • Ran old and new versions in parallel for two weeks until the numbers matched.
• Result — six hours a week saved, figures available by 9 a.m. instead of mid-afternoon, and no manual errors since.

Add what you did with the freed-up time, such as deeper analysis the team had never had capacity for. That shows the automation created value, not just convenience.

Note: Mention documentation and handover. An automation that only you understand is a new risk, not a solution.

Conflicting numbers are one of the most common problems an analyst faces, so interviewers want a methodical, diplomatic approach. Show that you treated it as a definitions problem first and a data problem second.

• Situation — “Marketing reported 42,000 new customers for the month; finance reported 35,500. Leadership did not know which to trust.”
• Action — walk through the investigation:
  • Compare definitions. Marketing counted sign-ups; finance counted customers with a first paid order. That explained most of the gap.
  • Compare filters and timing. One team used IST dates and the other UTC, and one excluded test and internal accounts.
  • Compare sources. Trace both numbers back to the raw tables and reconcile the remaining difference row by row on a sample.
  • Build a bridge. Present a simple walk from 42,000 to 35,500 showing each cause and its size, so nobody felt their number was simply wrong.
• Resolution — facilitate agreement on one official definition for each use, write it into a metric glossary, and build a single certified dataset both teams read from.
• Result — the monthly argument disappeared and leadership decks used consistent figures from then on.

Stress the soft skills: you stayed neutral, avoided declaring a winner in public, and involved both team leads in choosing the definition.

Note: Often both numbers are correct for their own purpose. The fix is usually a clearly labelled metric pair, such as sign-ups and paying customers, rather than forcing one number.

A join decides which rows survive when two tables are matched.

• INNER JOIN — only rows matching on both sides. Non-matching rows from either table disappear.
• LEFT JOIN — every row from the left table, with NULLs where the right has no match. Use it when you must not lose rows from your primary table.
• RIGHT JOIN — the mirror image. Rarely used, because swapping the tables and using LEFT reads more naturally.
• FULL OUTER JOIN — every row from both sides, with NULLs filling the gaps. Useful for reconciliation, finding records present in one system and not the other.
• CROSS JOIN — every combination of both tables. Occasionally deliberate, for generating a date-by-product grid; usually an accident.

The two traps that matter in real analysis:

• A WHERE condition on the right table turns a LEFT JOIN into an INNER JOIN. WHERE b.status = 'active' discards the NULL rows you were trying to keep. Put the condition in the ON clause instead.
• Joining on a non-unique key multiplies rows. If the right table has three rows per key, your row count triples and every SUM is inflated. Always check the row count before and after a join — this is the most common source of wrong numbers in analysis.

GROUP BY collapses rows sharing a value into one row per group, so aggregate functions — COUNT, SUM, AVG, MIN, MAX — can be applied per group.

The difference between WHERE and HAVING is when they run:

• WHERE filters rows before grouping. It cannot reference an aggregate, because the aggregates do not exist yet.
• HAVING filters groups after aggregation. This is the only place you can filter on an aggregate value.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01'   -- filters rows first
GROUP BY customer_id
HAVING SUM(amount) > 50000          -- filters groups after
ORDER BY total DESC;

The full logical order of execution — which explains most SQL confusion — is FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. Because SELECT runs after HAVING, you generally cannot use a column alias in HAVING, though some databases permit it.

Note: Prefer WHERE for anything it can do, since filtering rows before grouping is cheaper than aggregating and discarding. And remember that COUNT(*) counts rows while COUNT(column) skips NULLs — a distinction that silently changes results.

A window function performs a calculation across a set of rows related to the current row, without collapsing them the way GROUP BY does. You keep every row and gain an aggregate alongside it.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

• PARTITION BY divides rows into groups — like GROUP BY, but without collapsing them.
• ORDER BY inside OVER determines the ordering for running calculations and ranking.

The main families:

• Ranking — ROW_NUMBER (always unique), RANK (ties share a rank, leaving gaps), DENSE_RANK (ties share, no gaps).
• Offset — LAG and LEAD to reach previous or next rows, which is how you calculate period-over-period change without a self-join.
• Aggregate — SUM, AVG, COUNT over a window, for running totals and moving averages.

The classic use is "get the most recent row per group": number rows with ROW_NUMBER() partitioned by the group and ordered by date descending, then filter to rn = 1 in an outer query — because window functions cannot be used in WHERE.

Duplicates:

• Find them by grouping on the columns that should be unique and filtering to HAVING COUNT(*) > 1. First decide what "duplicate" means — a full row copy is different from two records for the same customer with different spellings.
• Handle by deduplicating to the most recent or most complete record, usually with ROW_NUMBER(). Investigate the cause: duplicates from a broken pipeline should be fixed upstream, not cleaned repeatedly.

Missing values — understand why before deciding:

• Missing at random can be imputed with a median or mode, or the rows dropped if few.
• Missing for a reason is informative. A blank "cancellation date" means the order was not cancelled, not that the data is missing. Imputing it would be nonsense.
• Adding a flag indicating the value was missing often preserves useful signal.

Outliers:

• Find them with the IQR rule, z-scores, or simply by sorting and looking at the extremes.
• Do not delete them by default. Decide whether each is an error (a typo, a test record, a sensor fault) or a genuine extreme value. Removing real high-value customers because they are statistically unusual destroys the most important part of the data.
• Options are correcting, excluding with documentation, capping, or transforming — and using a median rather than a mean where the distribution is skewed.

Note: Document every cleaning decision. An analysis where nobody knows what was excluded cannot be trusted or reproduced.

• Mean — the arithmetic average. Uses every value, which makes it efficient but highly sensitive to outliers.
• Median — the middle value when sorted. Robust to outliers, because moving the largest value further out does not change it.
• Mode — the most frequent value. The only one usable for categorical data.

When to use each:

• Use the mean for roughly symmetric distributions without extreme values, and when you need the total to be recoverable — mean × count gives the sum, which matters for revenue.
• Use the median for skewed distributions. Income, house prices, session duration, and order values are all right-skewed, and the mean overstates the typical case. Median salary and mean salary can differ enormously in the same organisation, which is exactly why the choice matters.
• Use the mode for categories — the most common product, region, or plan.

The relationship tells you about shape: in a symmetric distribution all three roughly coincide. Mean above median indicates right skew; mean below median indicates left skew.

Note: The strongest point is that reporting a central value alone is usually insufficient. Two datasets with identical means can be completely different, so pairing it with a spread measure — standard deviation, IQR, or percentiles — is what makes the summary honest. For latency and response time, percentiles matter far more than the average, because the average hides the worst experiences.

Correlation means two variables move together. Causation means one produces the change in the other. Correlation is necessary but nowhere near sufficient.

Why correlation appears without causation:

• A confounding variable drives both. Ice cream sales correlate with drownings; temperature causes both. In business, the classic case is that customers who use a feature retain better — because engaged customers both use features and retain, so the feature may cause nothing.
• Reverse causation — the arrow points the other way.
• Selection bias — the way the sample was chosen created the pattern.
• Coincidence, particularly when many variables are tested until something correlates.

How to establish causation:

• A randomised controlled experiment is the gold standard. Random assignment makes the groups equivalent on everything, including factors you did not think of, so a difference in outcome can be attributed to the treatment. This is what A/B testing is.
• Where randomisation is impossible, quasi-experimental methods help: difference-in-differences, regression discontinuity, instrumental variables, or a matched control group. All are weaker and rest on assumptions that must be stated.

Note: The practically valuable habit is asking "what else could explain this?" before presenting a causal claim — and being explicit when you are reporting an association rather than a cause, because stakeholders will act on it as a cause unless you say otherwise.

A p-value is the probability of observing a result at least as extreme as yours if the null hypothesis were true. A small p-value means the data would be surprising under the assumption of no effect.

Statistical significance means the p-value falls below a threshold chosen in advance, conventionally 0.05. That number is a convention, not a law of nature.

The common misinterpretations — this is what interviewers are testing:

• It is not the probability the null hypothesis is true. It is the probability of the data given the null, which is a different conditional.
• It is not the probability your result is a fluke.
• Significance is not importance. With a large enough sample, a commercially meaningless 0.01% difference becomes statistically significant. Always report the effect size and a confidence interval alongside it.
• Non-significant does not mean no effect. It may mean the sample was too small to detect one — absence of evidence is not evidence of absence.
• Stopping a test when it becomes significant invalidates it. Repeatedly checking and stopping at the first significant moment produces false positives at a much higher rate than 5%. This is the most costly mistake in practical A/B testing.
• Testing many variables guarantees false positives. Twenty independent tests at p < 0.05 produce roughly one significant result by chance alone.

Note: Saying you prefer to report confidence intervals and effect sizes rather than a bare significant/not-significant verdict shows genuine statistical literacy.

OLTP (Online Transaction Processing) systems run the business. They handle many small, fast reads and writes — placing an order, updating a profile. They are normalised to avoid update anomalies, optimised for write throughput and row-level access, and hold current state.

OLAP (Online Analytical Processing) systems analyse the business. They handle fewer, much larger queries scanning millions of rows to aggregate. They are denormalised for query simplicity and speed, often column-oriented, and hold history.

Why they must be separate: running a heavy analytical query against the production transactional database competes for resources with the application, and can slow or block real customers. Beyond a small scale, this is not optional.

A data warehouse is the OLAP store — a central repository consolidating data from multiple source systems, cleaned and structured for analysis, holding history rather than just current state. Modern examples are BigQuery, Snowflake, and Redshift.

Related concepts:

• A data lake stores raw data in its native format, structured or not, cheaply and at scale. Flexible, but without governance it becomes a data swamp nobody can use.
• A data mart is a subset of a warehouse serving one team or function.
• A lakehouse combines lake storage economics with warehouse structure and transactions.

Note: ETL versus ELT is the natural follow-up — modern cloud warehouses are powerful enough to transform after loading, so ELT has largely replaced ETL.

Choose the chart from the relationship you are showing:

• Change over time — line chart. Area chart for cumulative totals.
• Comparison across categories — bar chart. Horizontal bars when labels are long, and sorted by value unless there is a natural order.
• Part of a whole — stacked bar, or a pie chart only with very few slices. Beyond three or four segments a pie is unreadable and a bar chart is better.
• Relationship between two variables — scatter plot.
• Distribution — histogram or box plot. This is important and underused: an average alone hides the shape entirely.
• Two dimensions plus intensity — heatmap.
• A single key number — just show the number, large. A gauge chart adds nothing.

What makes it good:

• Title it with the finding, not the contents. "Mobile conversion fell 40% after the redesign" beats "Conversion by Device".
• Start bar chart axes at zero. Truncating exaggerates differences and is genuinely misleading; line charts showing change may reasonably not.
• Remove everything that is not information — 3D effects, heavy gridlines, decorative colour, and redundant legends.
• Use colour to mean something, and check it works for colour-blind readers and in greyscale.
• Label directly rather than forcing a trip to a legend.

Three roles that overlap but answer different questions.

• Data engineer — builds and maintains the infrastructure that makes data available. Pipelines, warehouses, ETL and ELT, orchestration, and data quality. Skills: SQL, Python or Scala, cloud platforms, and increasingly software engineering practice — version control, testing, CI. They answer "how do we get reliable data to the people who need it?"
• Data analyst — turns data into decisions. Exploratory analysis, dashboards, reporting, and working with stakeholders to define the question. Skills: strong SQL, a BI tool, spreadsheets, statistics, and — most importantly — communication. They answer "what happened, and why?"
• Data scientist — builds models to predict or optimise. Statistical modelling, machine learning, experiment design. Skills: Python or R, statistics, ML libraries, and enough engineering to deploy. They answer "what will happen, and what should we do?"

Analytics engineer is the newer role between engineer and analyst — modelling data inside the warehouse with tools like dbt, applying software practices to transformation logic.

Note: The honest observation worth making is that titles vary enormously between organisations, and in smaller companies one person does all three. It is also worth saying that most business value comes from reliable data and clear analysis rather than sophisticated models — a well-built pipeline and a good dashboard usually beat a machine learning project that nobody deploys.

A primary key uniquely identifies each row in a table. It must be unique and not NULL, for example order_id in an orders table. A foreign key is a column in one table that refers to the primary key of another, for example orders.customer_id pointing to customers.customer_id. It enforces that every order belongs to a customer who exists.

Why an analyst should care:

• Joins depend on them. Joining on a true primary key keeps row counts stable. Joining on a column you assumed was unique, but is not, multiplies rows and inflates totals.
• They define the grain. Knowing the key tells you what one row represents — one order, one order line, one customer per day — which is the first thing to establish before any aggregation.
• They reveal orphan records. In analytics databases foreign keys are often not enforced, so orders can reference customers that do not exist. A LEFT JOIN with a NULL check finds them.

-- Is order_id really unique?
SELECT order_id, COUNT(*) FROM orders
GROUP BY order_id HAVING COUNT(*) > 1;

-- Orders with no matching customer
SELECT o.order_id FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

A composite key uses more than one column together, such as (order_id, line_number). A natural key comes from the business, like a PAN or email, while a surrogate key is a system-generated integer with no business meaning.

Note: Never assume uniqueness in a warehouse or data extract. Test it with a quick GROUP BY before you trust a join.

Both let you build a query from an intermediate result, but they differ in readability and reuse.

• Subquery — a query nested inside another, in the FROM, WHERE or SELECT clause. A correlated subquery references the outer query and conceptually runs once per outer row, which can be slow on large tables.
• CTE (Common Table Expression) — a named result defined with WITH at the top of the query. It can be referenced several times and read top to bottom like steps in a recipe. A recursive CTE can walk hierarchies such as an org chart or a category tree.

WITH monthly AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders GROUP BY 1, 2
),
avg_cust AS (
  SELECT month, AVG(revenue) AS avg_rev FROM monthly GROUP BY month
)
SELECT m.customer_id, m.month, m.revenue
FROM monthly m JOIN avg_cust a ON a.month = m.month
WHERE m.revenue > 2 * a.avg_rev;

When to use which:

• Use a CTE for multi-step logic, when the same intermediate result is needed more than once, and whenever someone else must review the query.
• Use a subquery for a short, one-off filter such as WHERE customer_id IN (SELECT ...).
• For performance, most modern engines optimise both similarly. Some databases materialise a CTE once, which helps reuse but can block optimisations; check the execution plan rather than guessing.

Note: A temporary table is the next step up — useful when an intermediate result is expensive and reused across several separate queries.

This is a classic test of window functions and of how you think about ties. The cleanest approach ranks salaries within each department and filters on the rank.

WITH ranked AS (
  SELECT department_id, employee_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rnk = 2;

Why DENSE_RANK and not the others:

• ROW_NUMBER gives unique numbers even for ties. If two people share the top salary, one of them would be labelled “second”, which is wrong.
• RANK leaves gaps after ties: two people at rank 1 means the next salary gets rank 3, so rnk = 2 returns nothing.
• DENSE_RANK has no gaps, so rank 2 is always the second-highest distinct salary.

Clarify the requirement aloud: does the interviewer want the second-highest distinct salary, or the second person in order? That choice decides the function.

Alternative without window functions, for older databases:

SELECT department_id, MAX(salary) AS second_highest
FROM employees e
WHERE salary < (SELECT MAX(salary) FROM employees
                WHERE department_id = e.department_id)
GROUP BY department_id;

Mention edge cases: a department with only one employee, or where everyone earns the same, will return no row, and NULL salaries should be excluded explicitly.

Note: The same pattern solves any “top N per group” problem — top three products per region, latest order per customer — by changing the ORDER BY and the rank filter.

Both use aggregate window functions with an ordered frame, so every row keeps its detail while gaining a cumulative or rolling value.

SELECT order_date, daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Key points to explain:

• The frame clause matters. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW covers seven rows: today plus the six before it.
• ROWS versus RANGE. If you omit the frame, many databases default to RANGE ... CURRENT ROW, which includes every row tied on the same date. That silently changes a running total when dates repeat. Specify ROWS explicitly.
• Missing dates break the logic. Seven rows only equal seven days if every date is present. Join to a calendar or date dimension first so days with no sales appear as zero.
• Reset per group with PARTITION BY, for example a running total per store per financial year: OVER (PARTITION BY store_id, fiscal_year ORDER BY order_date ...).
• The first six rows average fewer than seven days. Either flag them or show the moving average only from day seven onwards.

Moving averages smooth daily noise and weekday patterns, which is why a 7-day window is common for consumer businesses whose sales vary by day of the week.

Note: Aggregate first to one row per day, then apply the window. Running a window over raw order lines gives a running total per order, not per day.

CASE WHEN is SQL's if-then-else. It returns a value based on conditions evaluated in order, and it can appear in SELECT, WHERE, GROUP BY and ORDER BY.

Common analytical uses:

• Bucketing — turning a number into bands, such as order value into Low, Medium and High, or age into age groups.
• Cleaning — mapping inconsistent labels (Bengaluru, Bangalore, BLR) into one value.
• Custom sort orders — ordering stages of a funnel logically instead of alphabetically.
• Conditional aggregation — counting or summing only rows that meet a condition, several at once, in a single pass.

SELECT region,
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns,
  SUM(CASE WHEN channel = 'app' THEN amount ELSE 0 END) AS app_revenue,
  ROUND(100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS return_rate_pct
FROM orders
GROUP BY region;

Conditional aggregation is how you pivot rows into columns — for example one column per month or per channel — without a separate query for each. Some databases offer a FILTER (WHERE ...) clause or a PIVOT operator that does the same job.

Pitfalls:

• Conditions are checked top to bottom and the first match wins, so put narrower conditions first.
• Without an ELSE, unmatched rows return NULL, which can hide data you forgot to categorise.
• Use 100.0 rather than 100 in ratios to avoid integer division returning zero.

Note: Add an ELSE 'Unmapped' bucket while developing. If it is not empty, your mapping is incomplete.

NULL means “unknown or missing”, and SQL uses three-valued logic: a comparison can be TRUE, FALSE or UNKNOWN. Rows are kept only when the condition is TRUE, which causes several surprises.

• salary = NULL is never true — even when salary is NULL. Use IS NULL and IS NOT NULL.
• WHERE city <> 'Delhi' silently excludes rows where city is NULL.
• Arithmetic with NULL returns NULL: price + NULL is NULL.
• Aggregates skip NULLs: AVG(rating) averages only non-NULL ratings, and COUNT(rating) counts only non-NULL values.
• NOT IN against a list that contains a NULL returns no rows at all. Prefer NOT EXISTS.

COALESCE returns the first non-NULL argument. It is used to supply defaults or fall back through several columns:

SELECT COALESCE(discount, 0) AS discount,
       COALESCE(mobile, landline, 'not provided') AS contact
FROM customers;

NULLIF(a, b) returns NULL when the two values are equal, otherwise it returns a. Its classic use is avoiding divide-by-zero errors:

SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM store_summary;

Be deliberate about replacing NULLs. Turning a missing rating into 0 drags the average down and tells a false story; a missing discount usually really does mean zero. The right default depends on what the NULL means in the business.

Note: Always check the NULL rate of key columns before analysis. A metric that looks stable can be hiding a growing share of missing values.

Cohort analysis groups users by a shared starting event — usually their first purchase or sign-up month — and tracks how each group behaves over time. It separates genuine retention trends from the noise created by a changing mix of new and old customers, which a single overall retention number hides.

A retention cohort table has cohorts as rows, months since joining as columns, and the percentage still active in each cell.

WITH firsts 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, f.cohort_month,
         DATE_TRUNC('month', o.order_date) AS active_month
  FROM orders o JOIN firsts f ON f.user_id = o.user_id
)
SELECT cohort_month,
       DATEDIFF('month', cohort_month, active_month) AS month_number,
       COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

Divide each cell by the month-0 count of its cohort to get the retention percentage, then pivot in SQL, Excel or a BI tool. Date functions differ by database; DATEDIFF above is Snowflake and Redshift syntax.

How to read the result:

• Read across a row to see how one cohort decays. A curve that flattens means you have found a loyal core.
• Read down a column to compare cohorts at the same age. If month-3 retention is improving for newer cohorts, recent product changes are working.
• Watch cohort size; percentages from tiny cohorts are noisy.

Note: Define “active” before you start — any order, any login, or a paid order — because each gives a very different curve.

A funnel tracks how many users progress through an ordered sequence of steps towards a goal — for example product view, add to cart, checkout, payment and order confirmed. Funnel analysis shows where users drop off, so you know which step to fix first.

How to build one:

1. Define the steps and the unit. Count users or sessions, not raw events, and decide whether steps must happen in order.
2. Fix a conversion window. For example, a user must reach payment within 7 days of first viewing a product to count as converting.
3. Count distinct users reaching each step, usually with conditional aggregation over an events table.
4. Calculate two rates: step-to-step conversion (checkout to payment) and overall conversion from the top of the funnel.

SELECT
  COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END) AS viewed,
  COUNT(DISTINCT CASE WHEN event = 'add_to_cart' THEN user_id END) AS carted,
  COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END) AS checked_out,
  COUNT(DISTINCT CASE WHEN event = 'payment' THEN user_id END) AS paid
FROM events
WHERE event_date >= '2026-08-01';

This simple version does not enforce order; a stricter version finds each user's first timestamp per step and requires each to be later than the previous one.

Interpreting it:

• Look for the biggest absolute drop, not just the lowest percentage.
• Segment the funnel by device, channel, city or new versus returning users. A payment drop that exists only on Android points to a bug, not a pricing issue.
• Compare against earlier periods to spot sudden changes after a release.

Note: A funnel tells you where users leave, not why. Pair it with session recordings, surveys or an A/B test before recommending a fix.

A well-run A/B test is a controlled experiment: users are randomly split between the current experience (control) and a change (treatment), so any difference in outcomes can be attributed to the change.

1. Write a clear hypothesis. “Showing delivery dates on the product page will increase checkout conversion because uncertainty about delivery causes drop-off.”
2. Choose one primary metric tied to the goal, such as conversion rate. Add guardrail metrics that must not get worse — refund rate, page load time, revenue per user.
3. Pick the randomisation unit. Usually the user, not the session, so the same person always sees the same version.
4. Calculate sample size in advance from the baseline rate, the minimum detectable effect worth acting on, a significance level (commonly 5 percent) and power (commonly 80 percent). This fixes how long the test must run.
5. Run for full weeks to cover weekday and weekend behaviour, and do not stop early because the result looks good.
6. Check the setup. Run a sample ratio mismatch check: if you planned a 50/50 split but got 52/48, the assignment is broken and results cannot be trusted.
7. Analyse. Report the effect size with a confidence interval, not just a p-value, and check guardrails and key segments.
8. Decide. Ship, iterate or drop — weighing the size of the gain against cost and risk.

Common pitfalls: peeking at results daily, testing many metrics and highlighting whichever is significant, novelty effects that fade after a week, and network effects where treatment users influence control users, as in marketplaces or social apps.

Note: Write the analysis plan — metric, duration, decision rule — before launch. It is the best protection against finding the answer you hoped for.

Statistical power is the probability that a test detects a real effect of a given size when it truly exists. A power of 80 percent means that if the change genuinely lifts conversion by the amount you care about, the test will flag it as significant eight times out of ten. Power equals one minus the Type II error rate.

Sample size depends on four inputs:

• Baseline rate or variance — the current conversion rate, or the spread of a continuous metric like order value.
• Minimum detectable effect (MDE) — the smallest change worth acting on. Halving the MDE roughly quadruples the sample needed.
• Significance level (alpha) — usually 0.05, the accepted false-positive rate.
• Power — usually 0.80.

A worked intuition: with a 5 percent baseline conversion and a goal of detecting a lift to 5.5 percent (a 10 percent relative change), you need roughly 31,000 users per variant at 5 percent significance and 80 percent power. Detecting a lift to 6 percent needs only about 8,000 per variant.

From sample size to duration: divide the required users by the eligible daily traffic per variant, then round up to whole weeks so every day of the week is equally represented. If the answer is six months, the test is not feasible as designed; choose a bigger MDE, a more sensitive metric, or a higher-traffic page.

Why underpowered tests are harmful: they usually show “no significant difference” even when the change works, and the rare significant result they produce tends to exaggerate the true effect.

Note: Use a standard calculator or a library such as statsmodels, but be able to explain the four inputs. Interviewers care far more about the reasoning than the formula.

Every hypothesis test can go wrong in two ways.

• Type I error (alpha) — concluding a change works when it does not. You ship a feature that adds nothing, or wastes budget. The significance level, often 5 percent, is the rate you accept for this error.
• Type II error (beta) — missing a real effect. You discard a change that would have helped. Power is 1 minus beta.

Everyday analogies help in interviews: a Type I error is a fire alarm going off with no fire; a Type II error is a fire with no alarm. In medical screening, a Type I error tells a healthy person they are ill, and a Type II error misses a disease.

Balancing them:

• For a fixed sample size, lowering alpha to reduce false positives increases false negatives. The only way to reduce both is a larger sample or a less noisy metric.
• Choose based on the cost of each mistake. For a risky, expensive or hard-to-reverse change — a pricing overhaul — demand stronger evidence. For a cheap, easily reversed UI tweak, a higher false-positive rate may be acceptable.
• Running many tests or checking many metrics inflates Type I errors; corrections such as Bonferroni or controlling the false discovery rate help.

Note: If asked which error is worse, the right answer is “it depends on the cost of each mistake” — then give an example for each side.

A confidence interval gives a range of plausible values for an unknown population figure, based on a sample. For example: “The new checkout lifted conversion by 1.2 percentage points, with a 95 percent confidence interval of 0.4 to 2.0 points.”

The correct interpretation is about the method, not the single interval: if we repeated the experiment many times and built an interval each time, about 95 percent of those intervals would contain the true value. It is not strictly correct to say there is a 95 percent probability that the true value lies inside this particular interval, though in business conversation that shorthand is common.

How it is built for a mean: estimate plus or minus a critical value times the standard error. For 95 percent confidence and a large sample, that is roughly the estimate plus or minus 1.96 standard errors.

What drives its width:

• Sample size — larger samples give narrower intervals; quadrupling the sample halves the width.
• Variability — noisier data gives wider intervals.
• Confidence level — 99 percent intervals are wider than 95 percent ones.

Why analysts should report intervals, not just p-values:

• An interval of 0.4 to 2.0 tells you the effect is real and roughly how big it might be. An interval of −0.1 to 5.0 tells you the test was too small to say anything useful.
• If the interval for a difference excludes zero, the result is significant at the matching level — so the interval contains the p-value information and more.
• Stakeholders can plan with a range: the worst case and the best case are both visible.

Note: When presenting to leaders, translate the interval into business terms — “between ₹4 lakh and ₹20 lakh extra revenue a month” — rather than percentage points.

All three describe spread, but of different things.

• Variance — the average squared distance of each value from the mean. Squaring removes negative signs and gives more weight to large deviations, but the units are squared (rupees squared), which makes it hard to interpret directly.
• Standard deviation (SD) — the square root of the variance, back in the original units. It describes how spread out individual observations are. If delivery times average 3 days with an SD of 0.5 days, most deliveries fall between roughly 2 and 4 days.
• Standard error (SE) — the standard deviation of an estimate, such as a sample mean, across repeated samples. For a mean, SE equals SD divided by the square root of the sample size. It describes how precisely you have estimated the mean, not how varied the data is.

Sample versus population: when estimating from a sample, variance is divided by n − 1 rather than n (Bessel's correction), which removes a small downward bias. In Excel this is STDEV.S versus STDEV.P.

Why it matters: confidence intervals and A/B test calculations use the standard error, while control limits, outlier rules and customer segmentation use the standard deviation. Mixing them up leads to intervals that are far too wide or far too narrow.

Note: Quadrupling the sample halves the standard error, which is why detecting small effects in experiments needs so much traffic.

A normal distribution is the symmetric, bell-shaped curve defined by its mean and standard deviation. Mean, median and mode are equal, and the 68-95-99.7 rule applies: about 68 percent of values lie within one standard deviation of the mean, 95 percent within two and 99.7 percent within three.

Many measurement-type variables — heights, manufacturing tolerances, test scores — are roughly normal. Many business variables are not: income, order value, time on site and session counts are usually right-skewed with a long tail of large values.

The central limit theorem (CLT) says that the distribution of a sample mean approaches a normal distribution as the sample size grows, whatever the shape of the underlying data, provided observations are independent and the variance is finite. In practice, samples of a few dozen are often enough for moderately skewed data; heavily skewed data needs more.

Why it matters so much:

• It is what makes t-tests, z-tests and confidence intervals valid for averages and proportions, even when individual order values are wildly skewed.
• It explains why A/B tests on conversion rate or average order value work with standard formulas at reasonable sample sizes.
• It justifies control charts and quality monitoring built on averages.

Common misunderstanding: the CLT does not make your raw data normal. A skewed revenue distribution stays skewed; only the distribution of its mean becomes approximately normal. For very heavy tails or small samples, use medians, log transforms, bootstrapping or non-parametric tests instead.

Note: Always plot a histogram before assuming normality. A quick look often reveals skew, multiple peaks or outliers that change the whole approach.

A percentile is the value below which a given percentage of observations fall. The 90th percentile of delivery time is the time within which 90 percent of orders arrive. The 25th, 50th and 75th percentiles are the quartiles Q1, the median and Q3.

The interquartile range (IQR) is Q3 minus Q1 — the spread of the middle 50 percent of the data. Because it ignores the extremes, it is robust to outliers, unlike the range or standard deviation.

Practical uses:

• Service levels — companies track p90 or p95 response times because an average hides the slow experiences that annoy customers.
• Outlier flagging — the Tukey rule flags values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR. This is the basis of the box plot's whiskers.
• Segmentation — splitting customers into spend quartiles or deciles.

Skewness measures asymmetry. In right-skewed data such as income or order value, a few large values pull the mean above the median. In left-skewed data such as exam scores on an easy test, the mean falls below the median.

Dealing with skewed data:

• Report the median and percentiles alongside, or instead of, the mean.
• Apply a log transform before modelling or plotting so patterns in the bulk of the data become visible.
• Winsorise — cap extreme values at, say, the 1st and 99th percentiles — when a few records would otherwise dominate.
• Use a log-scale axis or a box plot rather than a plain histogram.
• Use non-parametric tests or bootstrapped intervals for small, skewed samples.

Note: In Excel use PERCENTILE.INC or QUARTILE.INC; in SQL use PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value).

Simpson's paradox occurs when a trend that appears in every subgroup of the data reverses or disappears when the groups are combined. It happens because the groups differ in size and in their mix of an influential third variable.

A business example. Two sales teams report their deal win rates:

Team A wins a higher share in both segments, yet Team B looks better overall. The reason is mix: Team A handles far more hard enterprise deals, which have low win rates in any team. Judging on the overall number would reward the wrong team.

Where it appears in real work:

• Conversion rates that seem to fall after a marketing push, only because the push brought in lower-intent traffic.
• Hospital or branch comparisons where one site takes more difficult cases.
• A/B tests where the traffic mix by device or country shifts between variants.

How to protect against it:

• Segment key metrics by the obvious confounders — channel, region, product, customer type — before drawing conclusions.
• Ask whether the mix has changed over time or between the groups you are comparing.
• Use mix-adjusted or standardised rates, or a regression that controls for the confounder.
• Rely on randomised experiments, which balance mix automatically, when a causal answer matters.

Note: Neither the combined nor the segmented view is automatically “right”. Which one to trust depends on the causal question you are answering.

A metric is any quantified measure — page views, tickets closed, average order value. A KPI (key performance indicator) is one of the few metrics chosen to track progress against a specific strategic goal, with an owner and a target. Every KPI is a metric, but most metrics are not KPIs.

Good KPIs are:

• Tied to an objective, with a clear target and time frame.
• Clearly defined — numerator, denominator, filters, time zone — and written in a glossary.
• Actionable: a team can move it through its decisions.
• Hard to game, or balanced with a counter-metric. Tickets closed per agent should sit beside customer satisfaction, or agents will close tickets without solving them.

Leading versus lagging indicators. Lagging indicators, such as revenue or annual churn, confirm outcomes after the fact. Leading indicators, such as weekly active usage or trial activations, move earlier and predict those outcomes, so teams can react in time. A healthy KPI set has both.

A North Star metric is the single measure that best captures the value customers get from the product and predicts long-term revenue. Examples: nights booked for a travel marketplace, weekly ordering customers for a food delivery app, or messages sent for a chat app.

How to choose one:

1. Start from the core value moment — the point where a customer benefits.
2. Prefer a usage or engagement measure over revenue, since revenue lags and can be bought with discounts.
3. Check historically that it correlates with retention and revenue.
4. Break it into input metrics that individual teams own, such as new users, activation rate and frequency.

Note: Vanity metrics — total downloads, registered users — only ever go up and rarely drive decisions. Interviewers like candidates who can spot them.

These three metrics describe how well a business keeps and monetises customers, and each needs a precise definition before it is calculated.

Churn rate is the share of customers at the start of a period who are lost during it:

Monthly churn = customers lost during month / customers at start of month
Example: 150 lost / 2,000 at start = 7.5%

Exclude new customers acquired during the month from the denominator, otherwise churn is understated. For subscription businesses, churn means a cancellation or non-renewal. For non-contractual businesses such as e-commerce, you must choose an inactivity rule, for example no purchase in 90 days.

Retention rate is the complement for the same group: customers still active at the end of the period from those present at the start, so 92.5 percent in the example above. Cohort retention curves are more informative than a single blended number.

Revenue churn differs from customer churn. Net revenue retention includes upgrades and cross-sell, so it can exceed 100 percent even when some customers leave.

Customer lifetime value (CLV) estimates the total profit a customer generates over the relationship. A simple version:

CLV = average order value × purchase frequency per year
      × gross margin × expected lifetime in years

Subscription shortcut: CLV = monthly margin per customer / monthly churn

So a customer with ₹500 monthly margin and 5 percent monthly churn has a CLV of roughly ₹10,000. More advanced approaches use cohort-based projections, discounting future cash flows, or probabilistic models such as BG/NBD.

Why it matters: comparing CLV with customer acquisition cost (CAC) shows whether growth is profitable. A CLV-to-CAC ratio of around three or more is a common rule of thumb.

Note: State the definition every time you present these numbers. Churn calculated three different ways can differ by a factor of two.

All three retrieve a value from a table based on a lookup key, like a simple join in a spreadsheet.

• VLOOKUP — =VLOOKUP(A2, Customers!A:D, 3, FALSE) searches the first column of a range and returns the value from the column number given.
  • It can only look to the right of the key column.
  • A hard-coded column index breaks silently when someone inserts a column.
  • Omitting FALSE gives an approximate match, a very common source of wrong results.
• INDEX-MATCH — =INDEX(Customers!D:D, MATCH(A2, Customers!A:A, 0)). MATCH finds the row position and INDEX returns the value from any column. It can look left, survives inserted columns and is often faster on large sheets. A two-way version with two MATCH functions finds a value by both row and column.
• XLOOKUP — =XLOOKUP(A2, Customers!A:A, Customers!D:D, "Not found"), available in Excel 365 and 2021. Exact match by default, looks in any direction, has a built-in not-found value, can search from the bottom to get the latest record, and can return several columns at once.

Which to use: XLOOKUP where every user has a modern Excel version; INDEX-MATCH when the file must work in older versions; VLOOKUP only for quick one-off checks.

Pitfalls regardless of function:

• All three return only the first match. Duplicate keys in the lookup table give silently incomplete results — check for duplicates first.
• Values that look identical can fail to match because of trailing spaces or numbers stored as text. TRIM and VALUE fix most cases.
• For large or repeated merges, Power Query or a SQL join is more reliable than thousands of lookup formulas.

Note: Wrap lookups in IFERROR only after you have understood why values are missing; hiding errors hides data problems.

A pivot table summarises a flat table by grouping rows into categories and aggregating values, without writing formulas. It is Excel's equivalent of SQL's GROUP BY, and it is often the fastest way to explore a new dataset.

What it does well:

• Quick summaries — revenue by region and month in seconds, by dragging fields into Rows, Columns and Values.
• Different aggregations — sum, count, average, minimum, maximum and distinct count (when the data is added to the Data Model).
• Show Values As — percentage of row or column total, running total, difference from previous period, rank.
• Grouping — dates into months, quarters and years; numbers into bands.
• Slicers and timelines — interactive filters that make a simple dashboard.
• Drill-down — double-click any cell to see the underlying rows, which is excellent for checking a surprising number.
• Calculated fields — simple ratios such as margin percentage.

Limitations and traps:

• Manual refresh. Pivot tables do not update when source data changes until you refresh, a frequent cause of stale numbers in shared files.
• Source range. If the source is a fixed range, new rows are silently excluded. Convert the source to an Excel Table so the range grows automatically.
• Calculated fields sum before dividing — which is usually right for ratios but surprises people who expect row-level logic.
• Dirty data. Blank cells, text in number columns and inconsistent labels create extra categories or wrong counts.
• Scale. Beyond a million rows, or when combining several sources, use Power Pivot, Power Query or a database.

Note: Use GETPIVOTDATA or a separate summary sheet when a report must reference pivot values; direct cell references break when the pivot layout changes.

pandas is the standard Python library for tabular analysis. Its DataFrame behaves like a SQL table or spreadsheet, and most analyst work maps directly onto a handful of operations.

import pandas as pd

orders = pd.read_csv('orders.csv', parse_dates=['order_date'])
customers = pd.read_csv('customers.csv')

# Inspect
orders.info(); orders.describe(); orders.isna().sum()

# Filter rows (SQL WHERE)
recent = orders[(orders['order_date'] >= '2026-01-01') & (orders['status'] == 'delivered')]

# Group and aggregate (SQL GROUP BY)
summary = (recent.groupby('city')
           .agg(orders=('order_id', 'count'), revenue=('amount', 'sum'))
           .sort_values('revenue', ascending=False))

# Join (SQL JOIN)
merged = recent.merge(customers, on='customer_id', how='left', validate='many_to_one')

# Reshape (Excel pivot table)
pivot = merged.pivot_table(index='city', columns='segment', values='amount', aggfunc='sum')

Other everyday operations:

• drop_duplicates, fillna and astype for cleaning.
• value_counts(normalize=True) for quick category shares.
• dt.to_period('M') and resample for time-based aggregation.
• groupby(...).transform(...) and rolling for window-style calculations such as share of group or moving averages.

Habits that separate strong candidates:

• Use validate= in merge to catch unexpected duplicate keys, and check row counts before and after.
• Combine conditions with the element-wise operators & (and) and the pipe (or), each condition in parentheses; the Python keywords and and or raise an error on a Series.
• Avoid looping over rows; vectorised operations are far faster.
• Use .loc for assignment to avoid the SettingWithCopy warning.

Note: For data that does not fit in memory, push the heavy aggregation into SQL and bring only the summarised result into pandas.

A dashboard succeeds when it answers a specific audience's recurring questions quickly. Most failed dashboards try to show everything to everyone.

1. Start with the audience and decisions.

• Who uses it — an executive glancing weekly, or an operations manager acting daily?
• What decisions will it drive, and what questions must it answer in under a minute?
• Agree the three to five headline KPIs and their exact definitions before building anything.

2. Structure the layout.

• Put headline KPIs at the top left, where the eye lands first, each with a comparison: versus target, last period or last year.
• Below them, trends over time, then breakdowns by region, product or channel.
• Detailed tables go last or on a drill-through page.
• Follow an overview-first, filter, then details-on-demand flow.

3. Design for clarity.

• Pick the simplest chart for each question: line for trends, bar for comparisons. Avoid pie charts with many slices, 3D effects and gauges.
• Use colour sparingly and meaningfully — for example red only for items below target.
• Keep consistent number formats, such as ₹ lakh and crore, and label units.
• Limit filters to the ones users really need.

4. Build trust.

• Show the last refresh time and data source on the page.
• Reconcile totals with finance or the source system before launch.
• Add a definitions tab or tooltips for every metric.
• Set up automated data quality checks and alerts for failed refreshes.

5. Iterate. Launch with a small group, watch how they use it, check usage statistics after a month, and remove visuals nobody looks at.

Note: Performance is part of design. A dashboard that takes 30 seconds to load will be abandoned for an Excel export, however good it looks.

Both are written in DAX, but they are evaluated at different times and serve different purposes.

Use a calculated column when you need a value per row that you will filter, slice or group by — an age band, a Weekend flag, a concatenated key for a relationship. Better still, create such columns upstream in SQL or Power Query, which compresses better.

Use a measure for anything aggregated — total sales, margin percentage, year-to-date revenue, growth versus last year. Measures recalculate correctly whatever the user filters.

Total Sales = SUM(Sales[Amount])
Margin % = DIVIDE([Total Profit], [Total Sales])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

The classic mistake: calculating a ratio such as margin percentage as a calculated column and then summing or averaging it in a visual. The result is an average of row ratios, not the true overall ratio. As a measure, DIVIDE sums profit and sales for the current filter context first and then divides, giving the correct answer.

Related concepts to mention: CALCULATE modifies filter context; DIVIDE handles division by zero safely; time intelligence functions require a proper, marked date table.

Note: A good rule of thumb: if the result should change when a user clicks a slicer, it should be a measure.

Data quality is usually described along six dimensions. Framing your answer this way shows structure, but interviewers want to hear how you would actually check each one.

Turning this into practice:

• Profile first. Before any analysis, check row counts, NULL rates, distinct values, minimums and maximums for key columns.
• Automate checks in the pipeline — dbt tests, Great Expectations, or simple SQL assertions that fail the load when a rule breaks.
• Monitor trends, not just thresholds. A sudden drop in daily row count or a jump in NULL rate often signals a broken upstream feed.
• Reconcile key totals against a system of record such as the finance ledger.
• Assign ownership so every critical dataset has someone accountable for fixing issues at the source.

Quality is relative to purpose: data with a one-day delay is fine for monthly strategy but useless for fraud detection.

Note: Fix problems at the source whenever you can. Cleaning the same issue downstream in every report multiplies effort and inconsistency.

Both are forms of selection bias: the data you analyse is not representative of the population you want to draw conclusions about, so even perfect calculations give the wrong answer.

Sampling bias occurs when the way data is collected makes some groups more likely to be included than others.

• An app survey reaches only engaged users, so satisfaction looks higher than it really is.
• A feedback form answered mainly by customers who had a problem makes service look worse.
• Analysing only metro-city customers and generalising to all of India.
• Opt-in data, such as users who accepted tracking cookies, can differ systematically from those who declined.

Survivorship bias occurs when you study only the entities that made it through some filter and ignore those that dropped out.

• Studying only current customers to explain why people stay, without looking at those who churned.
• Concluding that a sales playbook works by examining only won deals.
• Evaluating fund or stock performance using only the funds still in existence.
• The classic wartime example: armour was initially proposed for the areas where returning aircraft had bullet holes, when the real lesson was to protect the areas with no holes, because planes hit there never came back.

How to guard against them:

• Ask how each row entered the dataset and who is missing.
• Compare the sample's profile — region, age, tenure — with the full population.
• Include failures, churned users and lost deals in the analysis.
• Use random sampling, or weight results to match the population.
• State the limitation plainly when it cannot be fixed.

Note: More data does not cure bias. A million biased records are just as misleading as a thousand, only more confidently.

Linear regression models a numeric outcome as a straight-line combination of one or more input variables, choosing the coefficients that minimise the sum of squared differences between predicted and actual values (ordinary least squares).

monthly_sales = 12,000 + 3.5 × ad_spend + 850 × stores_open − 40 × avg_price

Interpreting the output:

• Intercept — the predicted value when every input is zero. Often it has no practical meaning.
• Coefficient — the expected change in the outcome for a one-unit increase in that input, holding the other inputs constant. Here, each extra rupee of ad spend is associated with ₹3.5 more sales.
• p-value and confidence interval for each coefficient — whether the relationship is distinguishable from zero and how precisely it is estimated.
• R-squared — the share of variation in the outcome explained by the model. 0.65 means 65 percent. Adjusted R-squared penalises adding useless variables.

Key assumptions to check: a roughly linear relationship, independent errors (often violated in time series), constant error variance, and no severe multicollinearity — highly correlated inputs make individual coefficients unstable and hard to interpret. Plotting residuals reveals most problems.

Common misinterpretations:

• A coefficient shows association, not causation, unless the data comes from an experiment or a careful causal design.
• A high R-squared does not mean the model is correct, and a low one is normal for noisy human behaviour.
• Predicting outside the range of the training data is risky.
• Compare coefficient sizes only after standardising, since inputs are on different scales.

For a yes-or-no outcome such as churn, use logistic regression, whose coefficients describe changes in log-odds.

Note: Analysts use regression more for explanation — which drivers matter and by how much — than for pure prediction.

A time series is usually thought of as four components: trend (long-term direction), seasonality (repeating patterns at fixed intervals — day of week, month, festivals), cycles (longer, irregular swings tied to the economy) and noise.

How to identify them:

• Plot it first. A line chart over two or more years usually shows both trend and seasonal peaks at a glance.
• Moving averages. A 7-day average removes weekday seasonality; a 12-month average reveals the trend.
• Seasonal plots. Overlay each year on a January-to-December axis to see whether peaks repeat.
• Decomposition. Methods such as STL, available in Python's statsmodels, split the series into trend, seasonal and residual components.
• Autocorrelation. An ACF plot with spikes at lag 7 or lag 12 confirms weekly or yearly seasonality.

Why it matters for reporting:

• Month-over-month comparisons mislead for seasonal businesses. Retail sales always jump in October and November around Diwali, so a rise that month is not a sign of success by itself.
• Use year-over-year comparisons for the same period, and align festivals that move between years, such as Diwali or Eid.
• Calendar effects — the number of weekends, working days or paydays in a month — can explain surprising swings.
• Seasonally adjusted figures show the underlying trend, which is what leaders really want to know.
• Anomaly detection works only once seasonality is removed; otherwise every Monday or festival looks like an anomaly.

For forecasting, models such as exponential smoothing, SARIMA or Prophet explicitly model both components.

Note: Before declaring that a campaign worked, check that the lift is larger than the seasonal change seen in the same week of previous years.

These four types describe increasing levels of analytical maturity, each answering a harder question and usually delivering more value.

Points worth making in an interview:

• Most analyst work is descriptive and diagnostic, and it is foundational. Predictive models built on poorly understood or unreliable data fail quickly.
• Diagnostic analysis is where analysts add the most judgement — forming hypotheses, segmenting, ruling out data issues and separating correlation from causation.
• The boundaries blur in practice. A forecast (predictive) with a recommended inventory level (prescriptive) often lives in the same dashboard.
• Experiments are a bridge. A/B testing is how you validate that a prescriptive recommendation actually works before rolling it out.

Give a quick example from your own experience moving along the ladder, such as building a sales dashboard, then investigating a drop, then forecasting next quarter.

Note: Value to the business rises along the ladder, but so does the need for clean data, domain knowledge and stakeholder trust.

Work methodically: measure, find the bottleneck, then fix the biggest cost first.

1. Read the execution plan. Use EXPLAIN or EXPLAIN ANALYZE to see how the database runs the query. Look for full table scans on large tables, row estimates far from reality, expensive sorts and joins that explode in row count.

2. Reduce the data read.

• Select only the columns you need instead of SELECT *; columnar warehouses charge and slow down per column scanned.
• Filter as early as possible, and on partitioned tables filter on the partition column, such as order date, so whole partitions are skipped.
• Keep filters sargable: WHERE order_date >= '2026-01-01' can use an index, while WHERE YEAR(order_date) = 2026 usually cannot.

3. Fix joins.

• Join on indexed or well-distributed keys, with matching data types, so no implicit casts are needed.
• Aggregate large tables before joining them where possible.
• Check for accidental many-to-many joins that multiply rows — often the real cause of both slowness and wrong totals.

4. Simplify the logic.

• Replace correlated subqueries with joins or window functions.
• Use UNION ALL instead of UNION when duplicates are impossible, avoiding a sort.
• Use EXISTS rather than IN for large subqueries, and avoid DISTINCT as a band-aid for duplicate rows.

5. Change the structure if needed. Add an index in a transactional database; add clustering or sort keys in a warehouse; pre-aggregate into a summary table or materialised view for a dashboard that runs the same query hundreds of times.

Note: Test each change and measure the time and bytes scanned. Optimising by intuition alone often makes queries more complex without making them faster.

This is one of the most common analyst case questions. Interviewers want a structured approach that rules out simple explanations before hunting for complex ones.

1. Confirm the drop is real.

• Check data freshness and pipeline logs — did yesterday's load complete fully?
• Look for tracking changes, such as a new app release breaking an event or a changed metric definition.
• Compare against another source, such as the payments system or the orders database.

2. Put it in context.

• Is it sudden or gradual? A one-day cliff suggests an outage or bug; a slow slide suggests market or product causes.
• Compare with the same day last week and last year. A festival, a holiday or a long weekend might explain it.

3. Segment to localise the drop. Break the metric down by platform (Android, iOS, web), app version, city, channel, new versus returning users, category and payment method. If the entire drop sits in one segment, you have found where to look.

4. Decompose the metric. Orders equal sessions multiplied by conversion rate. Did traffic fall, or did conversion fall? Then walk the funnel to find the step that changed.

5. Check internal and external events. Releases, pricing changes, stock-outs, marketing campaigns ending, payment gateway incidents, competitor promotions, weather or regional events.

6. Form and test hypotheses. For example: “Android payment success fell from 95 to 70 percent after version 8.2” — then confirm with the engineering team.

7. Communicate. Share an early, clearly labelled update, then the root cause, the estimated impact in orders and revenue, and the recommended fix.

Note: Say out loud that you would check the data pipeline first. A surprising number of real “drops” turn out to be broken tracking.