Data Science Interview Questions

Interviewers use this question to test whether you connect analysis to outcomes, not just whether you can run a model. Structure your reply with STAR (Situation, Task, Action, Result) and spend most of your time on how you framed the question and what changed because of your work.

• Situation: set the business context in one or two sentences. For example, “Our e-commerce team was about to raise the free-delivery threshold from ₹499 to ₹699 to protect margins.”
• Task: state the decision at stake and who owned it. “The category head asked me to estimate the impact on orders and revenue before the change went live.”
• Action: explain how you turned the request into an answerable question — the metric you chose (contribution margin per order, not just order count), the data you used (basket-size distribution, earlier threshold changes by city) and the method (a before-and-after comparison against control cities, with a sensitivity range rather than a single number).
• Result: quantify the outcome. “Nearly 18% of orders sat between ₹499 and ₹699, and tier-2 cities were far more price-sensitive, so the team rolled out the new threshold only in metros. Margin improved by about 4% without the national order drop we had projected.”

Close with what you learned, such as agreeing the decision metric with stakeholders before touching the data. That shows maturity and signals you would repeat the approach.

Note: Pick a story where the decision genuinely depended on your work. Interviewers often follow up with “What would have happened without your analysis?”, so be ready with numbers and your own specific contribution, not the team's.

This tests whether you can turn an open-ended request into focused, useful work instead of producing a pile of charts nobody acts on. A strong answer shows that you clarify the decision before you touch the data.

How to structure your reply:

1. Ask what decision the insight should inform. “Are you planning next quarter's marketing budget, a pricing change, or a retention push?” Insights without a decision attached rarely get used.
2. Ask what they already believe. Hypotheses such as “our repeat customers are shrinking” give you something to confirm or reject quickly.
3. Agree on success metrics and scope. Revenue, repeat rate, customer acquisition cost — and the time window, segments and deadline.
4. Propose a short plan. Offer two or three candidate questions ranked by business value and effort, and let the stakeholder choose.
5. Deliver a quick first cut. Share an early view within a few days so they can redirect you before you over-invest.

Example points you can use:

• “When our sales director asked for ‘customer insights’, I learned in a 20-minute conversation that the real concern was falling repeat purchases in South India. That narrowed the work to a cohort retention analysis by region, which I delivered in a week.”
• “The analysis showed repeat rates dropped mainly among customers whose first order was delayed, which led to a logistics fix rather than a discount campaign.”

Note: Avoid saying you would “explore the data and see what comes up”. Exploration has its place, but interviewers want to hear that you anchor it to a business question and a decision owner.

Here the interviewer is checking your integrity, your communication skills and your ability to influence without authority. The best answers show that you stayed objective, checked your own work rigorously and presented the evidence in a way the stakeholder could accept.

A structure that works:

• The belief and the stakes: “Our marketing VP was convinced that a festive-season TV campaign had driven a 30% jump in app installs and wanted to double the budget.”
• What your data showed: “When I compared regions with and without the campaign, installs had risen almost equally everywhere. Most of the lift came from a Diwali sale that ran nationally at the same time.”
• How you verified before speaking up: you re-ran the numbers, checked the tracking, asked a peer to review the method and tested alternative explanations. This is important — you want to be sure before you challenge someone senior.
• How you communicated: a private conversation first rather than surprising them in a big meeting; you acknowledged what they had right; you showed the evidence visually; you framed it around the shared goal of spending the budget well.
• The outcome: “We agreed to run a geo-split test for the next campaign. It showed a real but much smaller lift of about 6%, and the budget was increased by 20% instead of 100%.”

Finish with the lesson: disagreement framed as a proposal to test is far easier to accept than a flat “you are wrong”.

Note: Do not tell a story where you simply won the argument. Interviewers look for respect, humility and a willingness to be proven wrong yourself — ideally you suggested a way for the data to settle it.

Every data scientist makes mistakes. This question tests ownership, speed of correction and how you prevent a repeat. Hiding or minimising an error is the one answer that will cost you the offer.

Structure your reply around four points:

1. What the error was and how you found it. Be specific. “Two days after sharing a churn report, I noticed that customers who had switched plans appeared twice because of a join on a non-unique key. Churn was overstated by roughly three percentage points.”
2. How quickly you raised it. “I told my manager the same morning, then emailed everyone who had received the report with the corrected numbers and a one-line explanation of what had changed.”
3. The impact and whether decisions were affected. “The retention team had started planning a campaign sized on the wrong number, so I met their lead to walk through the revised figures before they committed budget.”
4. What you changed afterwards. “I now add row-count checks after every join, compare key totals against a trusted source such as the finance dashboard, and ask a peer to review any analysis that will drive a decision.”

Tips for a strong answer:

• Keep the tone matter-of-fact. Show that you care about the consequences without being defensive or overly apologetic.
• Quantify the size of the error and its effect on the conclusion. Sometimes the direction of the finding survives, and saying so helps calibrate the audience.
• Emphasise the process fix. It shows the mistake made you and your team more reliable.

Note: Duplicate rows from joins, time-zone mismatches and filters applied in the wrong order are common, believable examples. Choose a real one you can explain in detail when the interviewer asks follow-up questions.

Real business data is rarely clean, and deadlines rarely move. The interviewer wants to see pragmatism, clear prioritisation and honest communication about uncertainty rather than perfectionism.

How to structure your story:

• Context and deadline: “The leadership team needed a view of regional sales performance for a board meeting in three days. The data came from five regional spreadsheets with different column names, missing months and duplicate invoices.”
• How you triaged: explain what you fixed properly and what you deliberately left. “I focused on the fields needed for the three headline questions — revenue, units and region — and ignored the rest. I standardised column names with a small pandas script, removed duplicate invoice numbers and flagged two regions where a month was missing.”
• How you handled gaps: “Instead of silently filling the missing months, I showed them as estimates based on the same month last year and labelled them clearly in the deck.”
• How you communicated risk: “I added a one-slide data-quality note listing known issues and the likely direction of any error, so the audience knew which numbers were firm and which were approximate.”
• Outcome and follow-up: “The board used the analysis to reallocate targets across regions. Afterwards I worked with the regional teams to adopt a common template so the next cycle took hours instead of days.”

This structure shows that you can separate “good enough for this decision” from “good enough forever”, which is a core data science skill.

Note: Mention the specific cleaning steps you took, such as deduplication, type fixes and reconciliation against a known total. Concrete detail convinces interviewers that you actually did the work rather than describing it in general terms.

Data scientists are almost always oversubscribed. This question checks whether you can allocate your time to the highest-value work and manage expectations transparently, rather than serving whoever shouts loudest.

A framework you can describe:

1. Clarify each request. What decision does it support, when is that decision being made, and what happens if the analysis is late? A request tied to a pricing decision next week outranks a “nice to know” dashboard.
2. Estimate value and effort. Place requests on a simple impact-versus-effort grid. Quick, high-impact work goes first; large, low-impact work gets challenged or deferred.
3. Look for reuse. Two teams asking about customer retention can often be served by one well-built dataset.
4. Align with your manager or the business owners. When priorities genuinely conflict, escalate with a clear recommendation rather than choosing silently.
5. Communicate timelines. Tell each requester when they will get something and what form it will take, even if the answer is “not this sprint”.

Example points for your answer:

• “In one quarter I had requests from marketing, supply chain and finance. I listed them in a shared tracker with the business decision and deadline for each, reviewed it with my manager weekly and shared it openly so teams could see why their item was placed where it was.”
• “For lower-priority requests I often gave a quick directional answer from existing dashboards, which solved the need without a full analysis.”
• “Recurring requests were a signal to build self-serve reports, which freed up time for deeper work.”

Note: Avoid answers that suggest you simply work longer hours to do everything. Interviewers want to see judgement, trade-offs and transparency, not heroics.

Most experiments do not produce a big win, so interviewers want to know whether you treat null and negative results as valuable learning and can communicate them without spin.

How to structure your reply:

• The test and the expectation: “We tested a redesigned product page with larger images, expecting a 5% lift in add-to-cart. The product team had already planned a full rollout.”
• The result, stated plainly: “After two full weeks at the planned sample size, add-to-cart moved by +0.4% with a 95% confidence interval of -1.1% to +1.9%. Page load time on low-end Android phones rose by 700 milliseconds.”
• How you checked it was trustworthy: no sample ratio mismatch, correct event logging, no novelty spike in the first days. This shows the null result was real, not a broken test.
• How you communicated: lead with the decision-relevant message, not the p-value — “The redesign did not measurably improve conversion and made the page slower for about a third of our users.” Then explain what the interval rules out: any lift larger than about 2% is unlikely.
• What happened next: “We shipped only the image-compression improvement, dropped the heavy layout and ran a follow-up test on delivery-date messaging, which produced a 3% lift.”

Point out that you resisted slicing the data into many segments until something looked significant, because that invites false positives.

Note: Frame a flat result as money saved: the company avoided shipping and maintaining a change that did not help. Showing that you value learning over winning is exactly what experimentation-heavy teams look for.

Good data scientists question the numbers everyone else takes for granted. This question tests your metric literacy, courage and ability to propose a better alternative, not just criticise.

A structure that works:

1. The metric and why it mattered: “Our customer support team was measured on average ticket resolution time, and it had improved 25% over two quarters.”
2. What made you suspicious: “Customer satisfaction scores were falling over the same period, and repeat contacts were rising.”
3. How you investigated: “I found that agents were closing tickets quickly and customers were reopening new ones for the same issue. Resolution time looked better because one problem was being counted as several fast tickets.”
4. What you proposed: “I suggested measuring first-contact resolution — the share of issues solved without a repeat contact within seven days — alongside resolution time, and defining a ‘case’ at the customer-issue level rather than the ticket level.”
5. How you got buy-in: “I shared the analysis with the support head first, piloted the new metric for one team for a month, and showed that it tracked satisfaction scores far more closely.”
6. Outcome: “The new KPI was adopted company-wide and repeat contacts dropped by about 15% the next quarter.”

Points to emphasise:

• Goodhart's law: when a measure becomes a target, people optimise the measure rather than the goal.
• You paired the main metric with a guardrail so it could not be gamed.
• You respected the people who owned the old metric and made the change collaborative.

Note: Ratios, averages and vanity counts such as downloads or page views are common culprits. Choosing an example where the misleading metric affected real decisions makes your story much stronger.

Cleaning the same bad data every week is a symptom; fixing the pipeline is the cure. This question tests cross-functional collaboration, root-cause thinking and your ability to make a business case for engineering time.

How to structure your reply:

• The problem and its cost: “Around 8% of our order records had missing or wrong city values, which broke our regional demand forecasts. Analysts were spending a few hours every week patching them manually.”
• How you traced the root cause: “By comparing broken rows with app versions and entry points, I found the city field came from a free-text address form on the older Android app, while the newer app used a pincode lookup.”
• How you made the case: “I quantified the impact — forecast error in affected regions was nearly double, and the manual workaround cost about 150 analyst-hours a year — and presented it to the engineering manager with a concrete proposal.”
• How you collaborated: “Engineering added pincode validation to the old form and backfilled historical rows from the pincode master. I wrote the validation rules and SQL checks, and we agreed on a data contract specifying allowed values and null rates.”
• How you prevented recurrence: “We added automated data-quality tests to the pipeline that alert the owning team if null or invalid rates cross a threshold.”
• Outcome: “Invalid city values fell below 0.5% and the manual patching stopped entirely.”

Highlight that you spoke the engineers' language — logs, schemas and tests — and made their work easier rather than just filing a complaint.

Note: Terms like data contracts, schema validation and pipeline monitoring show that you understand modern data practice. Just make sure you can explain exactly what you did versus what the engineers did.

Many dashboards are built, admired once and then abandoned. This question checks whether you design for the user and the decision, and whether you measure adoption rather than assume it.

Structure your answer around the dashboard's life cycle:

1. Start with the user and their decisions. “Before building the weekly sales dashboard, I interviewed five regional managers. They needed to know which stores were missing targets and why, every Monday morning before their calls.”
2. Keep it focused. “I limited the first page to four metrics — revenue against target, units, average bill value and footfall conversion — with a drill-down to store level. Everything else went to a secondary tab.”
3. Build trust in the numbers. “I reconciled totals with the finance report, documented metric definitions on the dashboard itself and showed the data refresh time clearly.”
4. Fit it into existing habits. “Instead of expecting people to open a new tool, I scheduled a Monday email with a snapshot and a link, and the dashboard became the agenda for the weekly review call.”
5. Measure and iterate. “I tracked views per user. When one region stopped using it, I found their stores had a different target structure, so I added that view.”

Results worth quoting: weekly active users, meetings that now use the dashboard, hours of manual reporting eliminated, or decisions it enabled. For example, “Manual Excel reporting of about ten hours a week was eliminated, and underperforming stores were flagged four days earlier.”

Note: Also mention retiring reports that nobody used. Showing that you pruned low-value work demonstrates that you think about impact, not output.

Statistics, algebra, specialized software, artificial intelligence, machine learning, and other fields are all combined in data science. The use of particular ideas and analytical methods to extract information from data used in strategic planning, decision-making, etc. is known as data science. Data science is the practice of analyzing data to get meaningful insights.

Supervised learning

• Using known and labeled data as input
• There is a feedback mechanism in supervised learning.
• Among supervised learning algorithms, decision trees, logistic regression, and support vector machines are the most often used ones.

Unsupervised learning 

• uses the input of unlabeled data.
• Without supervision, there is no feedback system.
• K-means clustering, hierarchical clustering, and algorithm are the three most popular unsupervised learning algorithms.

By estimating probability using its underlying logistic function, logistic regression analyses the relationship between the dependent variable (our label for what we want to predict) and one or more independent variables (our features) (sigmoid)

• Use the complete collection of data as your input.
• Determine the entropy of the target variable and the characteristics of the predictors
• Do the math to determine your information gain for all qualities (we gain information on sorting different objects from each other)
• As the root node, pick the property with the greatest information gain.
• until each branch's decision node is reached, carry out the same steps on each branch.

Many different decision trees are used to create a random forest. The random forest puts all the trees together if the data is divided into many packages and a decision tree is created for each package of data.

How to construct a random forest model:

• Choose 'k' features at random from a total of 'm' features where k <<m
• Calculate node D using the best split point among the "k" characteristics.
• Utilize the optimum split to divide the node into daughter nodes.
• In order to complete the leaf nodes, repeat steps two and three.
• Create a forest by repeating steps one through four n times to produce n trees.

How do you prevent your model from becoming overfitting?

A model that is overfitted ignores the wider picture and is only tuned for a relatively tiny quantity of data. To prevent overfitting, there are three basic strategies:

• Keep the model straightforward by considering fewer variables, which will help to reduce some of the noise in the training data.
• Utilize cross-validation methods, such as the k-folds method.
• If you want to avoid overfitting, employ regularisation techniques like LASSO that penalize specific model parameters.

How to We can easily delete the rows with missing data values if the data set is huge. We use the remaining data to forecast the values, making it the quickest method.manage missing data values includes the following:

Using the panda's data frame in Python, we may replace missing values with the mean or average of the remaining data for smaller data sets. There are other methods for doing this, including df.mean() and df.fillna (mean).



To communicate the same information succinctly, a data collection with several dimensions is reduced in size using a technique known as "dimensionality reduction."

This decrease aids in data compression and storage space reduction. Additionally, because there are fewer dimensions, there is a reduction in computation time. It eliminates features that are unnecessary; for instance, holding a value in two separate units serves no purpose (meters and inches).

Based on user preferences, a recommender system predicts how a user will evaluate a certain product. It can be divided into two sections:

Teamwork in Filtering

For instance, Last.FM suggests songs based on what other users who share your interests frequently listen to. Customers may get the following message along with product recommendations after completing a purchase on Amazon: "Users who bought this also bought..."

Filtering based on content

As an illustration, Pandora uses a song's characteristics to suggest songs with related characteristics. Instead than focusing on who else is listening to music, we are here focusing on the substance.

In order to choose k for k-means clustering, we employ the elbow approach. The elbow technique works by applying the k-means clustering algorithm on the data set, where k is the number of clusters.

It is referred to as the sum of the squared distances between each cluster member and its centroid.

Select the appropriate response:

• Rational Regression
• Regular Regression
• Apriori K-means clustering algorithm
• Logistic regression is the most suited

The summary of the results of a certain problem's predictions is the confusion matrix. It is a table that is used to summarise the model's performance. The Confusion Matrix, a n*n matrix, assesses how well the categorization model performs.

The true-positive rate represents the percentage of correct predictions made for the positive class. This statistic also calculates the proportion of genuine positives that are accurately validated.

The false-positive rate indicates the proportion of incorrect predictions made for the positive

The fundamental distinction between standard application programming and data science is that traditional programming requires the creation of rules to convert input to output. The rules are generated automatically from the data in data science. class. A false positive determines that something is true when it is initially false.

Popular data science libraries include

• Flow Tensor
• Pandas
• NumPy 
• sSciPy 
• sScrapy
• sLibrosa
• sMatPlotLib

The mean uses every value, so a few extreme observations pull it towards the tail. The median is the middle value once the data is sorted, so it depends only on rank and barely moves when outliers appear. That difference is exactly why skewness matters.

• Right (positive) skew — a long tail of large values, common in income, order value, session length and insurance claims. Here the mean is greater than the median.
• Left (negative) skew — a long tail of small values, such as marks on an easy exam. Here the mean is less than the median.
• Symmetric data — mean and median are close, and either works.

Example: if nine employees earn ₹6 lakh a year and the founder earns ₹2 crore, the mean salary is ₹25.4 lakh while the median is ₹6 lakh. Reporting the mean as the “typical salary” would be badly misleading.

What to report:

• To describe a typical customer or user in skewed data, report the median with percentiles such as P25, P75 and P90, or the interquartile range.
• When the business cares about totals — revenue, cost, capacity — the mean is what matters, because total = mean × count. Average order value stays useful for revenue planning even when it is skewed.
• For modelling, consider a log transform on heavily right-skewed variables, or a trimmed mean if you want a compromise between the two.

Note: A quick EDA habit is to compare the mean and median of every numeric column. A large gap flags skew or outliers worth investigating before you summarise or model the data.

Pearson correlation measures the strength of a linear relationship between two continuous variables. It equals their covariance divided by the product of their standard deviations and ranges from -1 to +1. Spearman correlation is Pearson correlation computed on the ranks of the values, so it measures any monotonic relationship — one that consistently rises or falls, even if not in a straight line.

When to use which:

• Use Pearson when both variables are continuous, the scatter plot looks roughly linear and there are no extreme outliers — for example, ad spend versus clicks within a normal budget range.
• Use Spearman for ordinal data such as 1-to-5 survey ratings, for skewed data, or when a relationship is curved but consistently increasing, such as years of experience versus salary.
• Kendall's tau is another rank-based option that behaves better with small samples and many tied values.

Example: for positive x, y = x cubed gives a Spearman correlation of exactly 1 but a Pearson correlation below 1, because the relationship is perfectly monotonic but not linear. In pandas you can compare both with df.corr(method='pearson') and df.corr(method='spearman').

Note: Always plot the data first. A correlation near zero does not mean “no relationship” — a U-shaped pattern can have near-zero Pearson and Spearman values while the variables are strongly related. And neither coefficient says anything about causation.

A confidence interval is a range computed from sample data that is designed to capture an unknown population parameter, such as a mean or a conversion rate, at a stated long-run rate. For a mean with a reasonably large sample, a 95% interval is approximately: sample mean ± 1.96 × (standard deviation ÷ square root of n).

The correct interpretation: if you repeated the same sampling process many times and built an interval each time, about 95% of those intervals would contain the true value. The 95% describes the reliability of the method, not of one particular interval.

Common misinterpretations:

• “There is a 95% probability that the true value lies in this interval.” In the frequentist framework the true value is fixed, so a given interval either contains it or does not. That probability statement belongs to a Bayesian credible interval.
• “95% of the data falls in this interval.” No — that describes a prediction or tolerance interval. A confidence interval for a mean is much narrower than the spread of individual values.
• “If two intervals overlap, the difference is not significant.” Two 95% intervals can overlap slightly while the difference between the groups is still significant. Test or build an interval for the difference directly.

What drives the width: larger samples narrow it (width shrinks with the square root of n, so quadrupling the sample halves it), more variable data widens it, and a higher confidence level such as 99% widens it.

Why it matters: reporting “conversion rose 1.2 points, 95% CI 0.3 to 2.1” gives stakeholders both the effect size and its uncertainty, which is far more useful for decisions than a p-value alone.

Note: Explicitly separating a confidence interval from a credible interval shows the interviewer that you understand the frequentist logic, not just the formula.

A hypothesis test is a structured way to decide whether the data provides enough evidence against a default assumption. Take an example: does a new checkout page increase average order value?

1. State the hypotheses. The null hypothesis (H0) is the “no effect” position: average order value is the same for both pages. The alternative (H1) is what you want evidence for: it differs (two-sided) or is higher (one-sided). Choose one-sided only if a change in the other direction genuinely does not matter, and decide before seeing data.
2. Choose the significance level and power. Commonly alpha = 0.05, the accepted Type I error rate, and power = 80%. Use them to fix the sample size in advance.
3. Pick the test statistic. It depends on data type and assumptions: a Welch t-test for comparing two means, a z-test or chi-square test for proportions, a Mann-Whitney U test for heavily skewed data.
4. Collect data and check assumptions. Independence of observations, adequate sample size, correct randomisation and no data-quality issues.
5. Compute the statistic and p-value. The p-value is the probability of seeing a result at least this extreme if H0 were true.
6. Decide. If p ≤ alpha, reject H0. Otherwise you fail to reject H0 — which is not the same as proving there is no effect.
7. Report effect size and a confidence interval. “Average order value rose by ₹38, 95% CI ₹12 to ₹64, p = 0.004.” Then judge practical significance: is ₹38 worth the engineering and operational cost?

Common mistakes to mention: changing the hypothesis after seeing results, stopping early when p dips below 0.05, running many tests without correction and treating statistical significance as business importance.

Note: Interviewers often ask what a p-value is NOT. It is not the probability that H0 is true, and it is not the probability that the result is due to chance alone.

The right test depends on the type of outcome variable (continuous or categorical), how many groups you compare and whether distributional assumptions hold.

Key details for each:

• t-test: use Welch's version by default because it does not assume equal variances. Use a paired t-test when the same users are measured before and after. With large samples the central limit theorem makes it robust to non-normal data.
• Chi-square: works on counts in a contingency table. Expected counts should be at least about 5 per cell; otherwise use Fisher's exact test. For a simple two-group conversion comparison, a two-proportion z-test is equivalent.
• ANOVA: the F-test only tells you that some group differs. Follow it with post-hoc tests such as Tukey's HSD to find which pairs differ, which also controls for multiple comparisons.
• Mann-Whitney U: a non-parametric test based on ranks. It compares whether values in one group tend to be larger than in the other, not strictly the medians. Its multi-group counterpart is the Kruskal-Wallis test.

Note: For heavy-tailed business metrics such as revenue per user, many teams prefer a t-test on large samples, a t-test after capping extreme values, or a bootstrap. Mention that you would check the distribution before choosing.

Bootstrapping is a resampling method that estimates the sampling distribution of a statistic without relying on a closed-form formula. It treats your sample as a stand-in for the population and repeatedly resamples from it.

How it works:

1. From a sample of n observations, draw n observations with replacement — some rows appear more than once, others not at all.
2. Compute the statistic of interest (median, ratio, percentile, model metric) on this resample.
3. Repeat 1,000 to 10,000 times to build a distribution of the statistic.
4. Take the 2.5th and 97.5th percentiles of that distribution as a 95% percentile confidence interval. The BCa method corrects for bias and skew when accuracy matters.

import numpy as np
rng = np.random.default_rng(42)
boot = [np.median(rng.choice(x, size=len(x), replace=True)) for _ in range(5000)]
low, high = np.percentile(boot, [2.5, 97.5])

When it is useful:

• Statistics with no simple standard-error formula: the median, P90 delivery time, or ratio metrics such as revenue per session.
• Skewed or small samples where normal approximations are doubtful.
• Putting a confidence interval around a model metric such as AUC or precision on a test set, which shows whether two models genuinely differ.

Limitations:

• It assumes the sample is representative. Bootstrapping cannot fix a biased sample.
• Observations must be independent. For time series or clustered data, use a block or cluster bootstrap that resamples whole blocks, users or stores.
• It performs poorly for extreme statistics such as the maximum, and with very small samples.

Note: In A/B tests where the metric is a ratio such as clicks per page view, randomisation happens by user but the metric is per page view. Resampling users rather than page views keeps the uncertainty estimate honest.

Both biases occur when the data you analyse is not representative of the population you want to draw conclusions about. The statistics can be computed perfectly and still point in the wrong direction.

Selection bias arises from how records enter the dataset.

• Self-selection: users who opt in to a new feature are already more engaged, so comparing them with non-users overstates the feature's effect on retention.
• Non-response: satisfaction surveys are answered mostly by very happy or very angry customers.
• Coverage: an online survey about digital payments excludes people who are offline, exactly the group you may care about.

Survivorship bias is a specific form in which you only see the entities that passed some filter.

• Studying only current customers to find what drives loyalty ignores everyone who already churned — the people who hold the answer.
• Evaluating a stock-picking strategy only on companies that are still listed ignores those that went bankrupt, inflating returns.
• The classic example: in the Second World War, analysts initially wanted to reinforce areas where returning planes had bullet holes; the insight was that planes hit elsewhere never returned.

How to guard against them:

1. Define the target population first and trace exactly how each row got into your data.
2. Include the failures — churned users, rejected applications, closed stores, delisted products.
3. Compare your sample with the population on known attributes such as age, city or tenure, and reweight if they differ.
4. Randomise where possible; otherwise use causal methods that adjust for how units were selected, and state the remaining assumptions.
5. Analyse by intent-to-treat — everyone who was offered the feature — rather than only those who used it.

Note: When an analysis produces a surprisingly strong effect, one of the first questions to ask is “Who is missing from this dataset?”

Exploratory data analysis (EDA) is about understanding what the data represents, whether it can be trusted and what patterns are worth pursuing, before any modelling. A structured approach keeps it from turning into random plotting.

1. Understand the context and grain. What does one row represent — an order, a customer, a customer-day? How was the data generated, and what time period does it cover? Misreading the grain causes most double-counting errors.
2. Check structure. Shape, column types and a first look at values.
   

   df.shape; df.info(); df.head()
   df.describe(include='all')
   df.isna().mean().sort_values(ascending=False)
   df.duplicated(subset=['order_id']).sum()

3. Assess data quality. Missing values and their pattern, duplicates, impossible values such as negative quantities or future dates, inconsistent categories such as “Bengaluru” and “Bangalore”, and whether totals reconcile with a trusted source.
4. Univariate analysis. Histograms and box plots for numeric columns (look for skew, outliers and spikes at suspicious values like 0 or 999), and value counts for categories (look for rare levels and dominance).
5. Bivariate and target analysis. How does each feature relate to the outcome? Use scatter plots, grouped means, correlation matrices and cross-tabs. Watch for features that look too predictive — often a sign of leakage.
6. Time and segment views. Plot key metrics over time to spot trends, seasonality and breaks caused by logging changes, then split by region, platform or customer type.
7. Document findings and hypotheses. Record the data issues, cleaning decisions and questions to validate with domain experts.

Libraries such as ydata-profiling or sweetviz can generate a quick automated report, but they supplement rather than replace thinking about the business context.

Note: The most valuable EDA output is often a short list of data issues and surprising patterns shared with stakeholders, because it prevents wrong conclusions later.

An outlier is an observation that differs markedly from the rest of the data. Detecting one is a statistical task; deciding what to do with it is a business and domain judgement.

Detection methods:

• IQR rule: flag values below Q1 - 1.5 × IQR or above Q3 + 1.5 × IQR. It is robust and works for skewed data.
• Z-score: flag values more than 3 standard deviations from the mean. It assumes roughly normal data and is itself distorted by the outliers it is trying to find.
• Modified z-score using the median absolute deviation (MAD): a robust alternative to the z-score.
• Visual checks: box plots, histograms and scatter plots, which also reveal clusters of odd values.
• Multivariate methods: Isolation Forest, Local Outlier Factor or Mahalanobis distance catch rows that look normal on each column but are unusual in combination, such as a 22-year-old with 30 years of work experience.

Deciding what to do — first ask why the value is extreme:

Treatment options: winsorising (capping at, say, the 1st and 99th percentiles), log transforms, robust statistics such as the median, robust losses such as Huber loss, or tree-based models that are less sensitive to extreme feature values.

Note: Never remove outliers just to make a result significant or a model look better, and always document what you removed and why. In A/B tests, decide on any capping rule before looking at the results.

High-cardinality categoricals such as pincodes, product SKUs or merchant IDs break naive one-hot encoding: it creates thousands of sparse columns, increases memory and training time, and gives rare categories too few examples to learn from. Common alternatives:

• Frequency or count encoding: replace each category with how often it appears. Simple and leakage-free, but different categories with similar counts become indistinguishable.
• Grouping rare levels: keep the top categories (for example those with at least 100 rows) and merge the rest into “Other”. Often combined with other methods.
• Using a natural hierarchy: map pincode to district, state or city tier, or SKU to sub-category and brand. This adds business meaning and reduces cardinality.
• Target (mean) encoding: replace each category with the average target value for that category. It is powerful but leaks the target, so compute it out of fold and apply smoothing: encoded value = (n × category mean + m × global mean) ÷ (n + m), where n is the category count and m controls how strongly rare categories shrink towards the global mean.
• Hashing trick: hash categories into a fixed number of buckets. Memory-efficient and handles unseen values, at the cost of occasional collisions and lost interpretability.
• Learned embeddings: in neural networks, each category gets a dense vector learned during training, capturing similarity between categories.
• Native handling: CatBoost uses ordered target statistics that avoid leakage, and LightGBM can split on categorical features directly.

counts = train['pincode'].value_counts()
train['pincode_freq'] = train['pincode'].map(counts)
test['pincode_freq'] = test['pincode'].map(counts).fillna(0)

Unseen categories: always decide how new values at prediction time are handled — map them to “Other”, the global mean or zero count.

Note: For linear models, a combination of rare-level grouping and target encoding usually works well; for gradient boosting, frequency encoding, target encoding or native categorical support are the usual choices.

Before creating any feature, fix the timeline: choose an observation (snapshot) date, use only data before it for features, and define churn in a later prediction window, for example “no purchase in the next 60 days”. Features that peek past the snapshot date cause leakage and wildly optimistic results.

Useful feature families:

• RFM: recency (days since last order), frequency (orders in the last 30, 90 and 365 days) and monetary value (total and average spend).
• Trend features: spend in the last 30 days divided by spend in the previous 30 days, or change in order frequency. A declining trend often predicts churn better than absolute levels.
• Purchase rhythm: average and standard deviation of days between orders, and whether the current gap is longer than the customer's usual gap.
• Breadth and engagement: distinct categories bought, app sessions, wishlist activity and share of orders bought on discount.
• Experience signals: late deliveries, returns, cancellations, support tickets and failed payments in recent periods.
• Customer attributes: tenure, acquisition channel, city tier, plan type and payment method.

snap = pd.Timestamp('2026-06-30')
hist = tx[tx['order_date'] <= snap]
feat = hist.groupby('customer_id').agg(
    last_order=('order_date', 'max'),
    orders=('order_id', 'nunique'),
    spend=('amount', 'sum'))
feat['recency_days'] = (snap - feat['last_order']).dt.days

Good practices:

• Build training data from several snapshot dates so the model learns patterns that hold across seasons.
• Compute every feature the same way at training and scoring time — ideally with one shared SQL or pandas function.
• Check feature importance for suspicious winners; a feature like “account closed flag” is usually the label in disguise.

Note: Domain conversations are the best source of features. Asking the customer support team why people leave often reveals signals, such as unresolved complaints, that no amount of automated feature generation would find.

Business goals are usually vague; data science problems must be precise. Translating one into the other is often the most valuable step in a project. Using “reduce churn” as an example:

1. Identify the decision and the action. What will the business do with the output? If the retention team can call 2,000 customers a week or send a discount coupon, the model must rank customers for that action.
2. Define the target precisely. For a subscription business, churn may be cancellation; for e-commerce it might be “no purchase in the next 60 days”. The definition should match how the business already measures churn.
3. Fix the unit, horizon and timing. Predict per customer, every Monday, for the next 60 days, using data available up to Sunday night.
4. Choose success metrics at two levels. A model metric that matches the action — precision at the top 2,000 customers, or recall within capacity — and a business metric such as retained revenue net of offer cost.
5. Establish a baseline. What happens today? A simple rule such as “no order in 45 days” may already capture much of the value, and the model has to beat it.
6. List constraints. Data availability, latency, fairness, interpretability needs and privacy rules.
7. Plan how to prove impact. Agree on a holdout or A/B test in which some flagged customers receive no intervention.

An advanced point worth making: predicting who will churn is not the same as predicting who can be saved. Some high-risk customers will leave regardless, and some would stay without a coupon. Uplift modelling, which estimates the effect of the intervention on each customer, targets the persuadable group and often delivers far more value per rupee spent.

Note: Write the problem statement on one page — decision, target, unit, horizon, metric, baseline and experiment plan — and get the business owner to sign off before building anything.

A well-designed A/B test is decided largely before it starts. The steps below keep it trustworthy and useful.

1. Write a clear hypothesis. “Showing the estimated delivery date on the product page will increase checkout conversion, because it reduces uncertainty about when the order arrives.” The reasoning helps you interpret the result either way.
2. Choose metrics. One primary metric tied to the hypothesis (checkout conversion), secondary metrics that explain the mechanism (add-to-cart rate) and guardrail metrics that must not degrade (page load time, cancellations, revenue per user).
3. Choose the randomisation unit. Usually the user, so the same person sees a consistent experience. Use sessions only for changes users will not notice; use clusters such as cities when users interact with each other.
4. Calculate sample size and duration. From the baseline rate, minimum detectable effect, significance level and power. Run for whole weeks to cover weekday and weekend behaviour, and avoid unusual periods such as a big sale.
5. Pre-register the analysis plan. Metric definitions, test to be used, planned segments and decision rules. This prevents p-hacking.
6. Validate the setup. Run an A/A test or check assignment logic, confirm events are logged identically in both variants and ramp traffic gradually from 1–5% to catch bugs.
7. Monitor health, not significance. Watch for sample ratio mismatch, crashes and guardrail breaches during the test, but do not stop early because the primary metric looks good.
8. Analyse. Report the effect with a confidence interval, check guardrails and look at pre-planned segments only.
9. Decide. Ship, iterate or abandon, weighing statistical evidence against practical significance, engineering cost and long-term effects.

Note: Keep a shared log of every experiment, including failures. Over time it becomes the organisation's memory of what works, and it prevents teams from repeating tests that already failed.

Sample size is fixed before the test so that it can reliably detect the smallest effect worth acting on. You need four inputs:

• Baseline rate or variance of the metric, from historical data. For a conversion rate this is p; for a continuous metric you need its standard deviation.
• Minimum detectable effect (MDE) — the smallest change that would justify a decision, for example an absolute lift of 1 percentage point.
• Significance level (alpha) — usually 0.05, two-sided, giving z = 1.96.
• Power (1 - beta) — usually 80%, giving z = 0.84.

Formula for two proportions (per group):

n = (z for alpha/2 + z for beta) squared × [p1(1 - p1) + p2(1 - p2)] ÷ (p2 - p1) squared

Worked example: baseline conversion 10%, MDE of 1 point (to 11%).

• (1.96 + 0.84) squared = 7.84
• p1(1 - p1) + p2(1 - p2) = 0.09 + 0.0979 = 0.1879
• n = 7.84 × 0.1879 ÷ 0.0001 ≈ 14,700 users per group, or about 29,500 in total.

If 5,000 eligible users arrive per day, the test needs about six days; round up to a full week to cover weekly cycles.

For a continuous metric, a handy rule of thumb (Lehr's rule) for 80% power and alpha 0.05 is n ≈ 16 × variance ÷ (MDE squared) per group.

Key trade-offs:

• Halving the MDE roughly quadruples the sample size, since n depends on 1 ÷ MDE squared.
• Lower baseline rates and noisier metrics need larger samples.
• More variants or multiple primary metrics require corrections and more data.

from statsmodels.stats.power import NormalIndPower
from statsmodels.stats.proportion import proportion_effectsize
es = proportion_effectsize(0.11, 0.10)
n = NormalIndPower().solve_power(effect_size=es, alpha=0.05, power=0.8)

Note: If the required sample is larger than your traffic allows, consider a bigger MDE, a more sensitive metric or variance reduction such as CUPED, rather than running an underpowered test that cannot give a clear answer.

Each hypothesis test at alpha = 0.05 has a 5% chance of a false positive when there is no real effect. Run many tests and false positives become almost certain: with 20 independent tests and no true effects, the probability of at least one “significant” result is 1 - 0.95 to the power 20, or about 64%.

Where it shows up in practice: tracking dozens of metrics in an A/B test, slicing results by many segments (city, device, age), testing several variants against control, or screening hundreds of features for correlation with a target.

Bonferroni correction: test each hypothesis at alpha ÷ m, where m is the number of tests. It controls the family-wise error rate (FWER) — the chance of even one false positive. It is simple but conservative, so it loses power as m grows. The Holm step-down method controls the same error rate with more power and is almost always a better choice.

Benjamini-Hochberg (BH): controls the false discovery rate (FDR) — the expected share of false positives among the results you declare significant. Procedure:

1. Sort the m p-values from smallest to largest.
2. For rank k, compare p(k) with (k ÷ m) × q, where q is the target FDR, such as 0.05.
3. Find the largest k where p(k) is at or below its threshold and reject hypotheses 1 to k.

Example with five p-values — 0.001, 0.008, 0.020, 0.041, 0.300 — and q = 0.05. BH thresholds are 0.01, 0.02, 0.03, 0.04 and 0.05, so the first three are rejected. Bonferroni uses a single threshold of 0.01 and rejects only the first two.

When to use which: FWER methods when a single false positive is costly, such as a launch decision or a medical claim; FDR methods when screening many candidates for follow-up, such as segments or features.

Note: The best defence is design: pre-register one primary metric for the decision and treat everything else as exploratory evidence that needs confirmation in a follow-up test.

A sample ratio mismatch (SRM) occurs when the observed split of users between variants differs from the designed split by more than chance allows. It is one of the most reliable signs that an experiment is broken.

How to detect it: run a chi-square goodness-of-fit test on the user counts. Suppose a 50/50 test ends with 50,000 users in control and 48,500 in treatment. The expected count is 49,250 each, the chi-square statistic is about 22.8 and the p-value is around 0.000002. Many teams flag SRM when p is below 0.001. A 3% gap sounds small, but at this scale it is far beyond random variation.

Common causes: bugs in the assignment code, the treatment crashing or loading slowly so some users never log an exposure event, redirects that drop users, bot filtering that affects one variant more, or users being reassigned when they switch devices.

Why it matters: the users who went missing are rarely random — often they are the slowest devices or least engaged users — so the groups are no longer comparable and the measured effect can be badly biased. Fix the cause before analysing; do not simply reweight.

Other checks before trusting a result:

• A/A tests and pre-period balance: key metrics should be similar across variants before exposure.
• Instrumentation parity: both variants log events identically.
• Novelty and primacy effects: plot the daily treatment effect. A lift that fades after the first few days may not last.
• Unit mismatch: if you randomise by user but analyse per session or page view, use the delta method or a bootstrap by user for correct standard errors.
• Outliers and bots: a few extreme accounts can dominate revenue metrics.
• Interaction with other experiments: overlapping tests on the same page can interfere.
• Guardrail metrics: latency, errors and cancellations must stay within limits.

Note: Automating the SRM check in your experimentation platform is one of the cheapest ways to avoid shipping decisions based on broken tests.

A standard A/B test assumes the Stable Unit Treatment Value Assumption (SUTVA): one user's outcome depends only on their own assignment, not on anyone else's. When users interact or share resources, that assumption fails and user-level randomisation gives biased results.

Where interference appears:

• Two-sided marketplaces: in a ride-hailing or food-delivery app, if treated riders get a discount and book more, they take drivers away from control riders. Treatment looks better partly because control got worse, so the effect is overstated.
• Social and communication products: a new sharing feature affects the friends of treated users, who may be in control, understating the true effect.
• Shared budgets and inventory: ad auctions with a fixed campaign budget, or limited stock in a flash sale.

Design options:

• Cluster randomisation: randomise groups that interact mostly within themselves — cities, delivery zones, colleges, or clusters in a social graph. Interference then happens inside a variant, not across variants.
• Switchback tests: alternate treatment and control over time windows in the same region, for example every hour, for pricing or dispatch algorithms. Randomise the schedule and allow a buffer for carryover between windows.
• Budget-split or inventory-split designs: give each variant its own share of budget or supply so they cannot compete.
• Two-sided randomisation: randomise both buyers and sellers to measure spillovers directly.

Trade-offs: randomising clusters or time periods gives far fewer independent units than randomising users, so power drops. Use cluster-robust standard errors, stratify clusters by size or past metrics, and run tests longer. Checking whether user-level and cluster-level estimates differ can itself reveal the size of the interference.

Note: Mentioning SUTVA by name and giving a marketplace example signals strong experimentation experience — it is a favourite interview topic at delivery, mobility and social media companies.

CUPED (Controlled-experiment Using Pre-Experiment Data) is a variance-reduction technique introduced by Microsoft in 2013. It uses data collected before the experiment to remove predictable noise from the outcome metric, so you can detect the same effect with fewer users or in less time.

How it works: for each user, take a pre-experiment covariate X — usually the same metric measured over the weeks before the test — and compute an adjusted outcome:

Y adjusted = Y - theta × (X - mean of X), where theta = covariance(X, Y) ÷ variance(X)

Because X was measured before randomisation, it is unaffected by the treatment, so the adjustment keeps the estimate unbiased. It removes the part of each user's outcome that was predictable from their past behaviour — heavy buyers buy a lot regardless of variant.

How much it helps: the variance falls by a factor of (1 - rho squared), where rho is the correlation between X and Y. With rho = 0.7, variance drops by 49%, so the test needs roughly half the sample for the same power. For stable metrics such as revenue per returning user, 30–50% reductions are common.

Practical points:

• New users have no history. Set their covariate to zero with an indicator variable, or use other pre-treatment attributes.
• CUPED is equivalent to regression adjustment (ANCOVA) with the pre-period metric as a covariate. Several covariates can be combined in one regression.
• CUPAC extends the idea by using a machine-learning prediction of the outcome as the covariate.
• Only ever use covariates measured before exposure; post-treatment covariates introduce bias.

Other variance-reduction tools: stratified randomisation, capping heavy-tailed metrics, choosing more sensitive metrics such as conversion instead of revenue, and filtering to users who could actually see the change (triggered analysis).

Note: In an interview, framing CUPED as “the same statistical power with fewer users” connects the technique to business value: faster decisions and more experiments per quarter.

Experiments are sometimes impossible — a policy applies to everyone, a change has already launched, or randomising would be unethical or illegal. Quasi-experimental methods estimate causal effects from observational data by finding a credible comparison group and stating assumptions clearly.

• Difference-in-differences (DiD): compare the before-and-after change in a treated group with the change in an untreated group over the same period. For example, a new delivery fee launched in Pune but not in comparable cities. The key assumption is parallel trends — without the change, both groups would have moved together. Check it by plotting pre-period trends.
• Synthetic control: when only one unit is treated, such as a single state or city, build a weighted combination of untreated units that closely matches its pre-period history, and use it as the counterfactual.
• Regression discontinuity: when treatment is assigned by a cut-off — customers who spend above ₹10,000 get gold status — compare units just above and just below the threshold, which should be otherwise similar.
• Instrumental variables: use a variable that shifts treatment but affects the outcome only through that treatment, such as distance to a store affecting whether someone joins a loyalty programme. Valid instruments are hard to find.
• Propensity score matching or weighting: model the probability of receiving treatment from observed characteristics and compare similar treated and untreated units. It only adjusts for observed confounders.
• Interrupted time series: model the pre-change trend and seasonality and compare it with what happened afterwards. Bayesian structural time-series tools such as CausalImpact are common here.

Making the result credible:

• State the assumptions explicitly and test what you can.
• Run placebo tests — apply the method to a fake launch date or an untreated group and check that no effect appears.
• Do sensitivity analysis to see how strong an unobserved confounder would need to be to overturn the result.
• Triangulate with more than one method when the decision is important.

Note: Being honest about limitations is part of the answer. Saying “this estimate relies on parallel trends, which looks plausible from the pre-period” is far stronger than presenting the number as definitive.

A North Star metric is the single metric that best captures the core value a product delivers to its customers and that predicts long-term business success. It aligns teams around a shared outcome rather than a collection of local targets.

Examples by product type:

• Food delivery: orders delivered on time per week.
• Music streaming: time spent listening.
• Online learning: learners completing a course module each week.
• A job portal: applications that lead to an interview.

What makes a good North Star:

• It reflects value to the customer, not just revenue extraction.
• It is a leading indicator of revenue and retention, not a lagging financial number.
• Teams can influence it through their work, often via a tree of input metrics — for delivery, that tree includes active customers, order frequency and on-time rate.
• It is hard to game and simple to understand.

Guardrail metrics protect against optimising the North Star at the expense of something else that matters. They should not get worse, even if they are not the goal.

• User experience: app crash rate, page load time, uninstalls, notification opt-outs.
• Business health: revenue per user, cost per order, refund rate, support tickets.
• Trust and fairness: complaint rate, cancellations, seller or partner satisfaction in marketplaces.

Example: sending more push notifications may raise weekly engagement in the short term while uninstall and opt-out rates rise, destroying long-term reach. Tracking those as guardrails in every experiment catches the problem before it scales.

How to choose guardrails: ask what could go wrong if a team pushed the North Star as hard as possible, look at past incidents and set explicit tolerances, such as “page load must not worsen by more than 100 milliseconds”.

Note: Product-sense interviews often ask you to define metrics for a specific product. Naming the North Star, two or three input metrics and two guardrails gives a complete, well-structured answer.

This classic product-analytics case tests structured thinking. Avoid jumping to one explanation; work from data validity to internal causes to external causes, narrowing with segmentation at each step.

1. Confirm the drop is real. Was there a tracking or logging change, a delayed pipeline, a new bot filter, a change in the DAU definition or a time-zone issue? Compare against independent sources such as server logs, orders or payment volumes. A surprising number of “drops” are data bugs.
2. Characterise it. Sudden (one day) or gradual? Exactly when did it start? Is it continuing? A sharp step usually points to a release, outage or tracking change; a slow decline points to product or market issues.
3. Segment to localise the drop. Platform (Android, iOS, web), app version, country and city, acquisition channel, new versus returning users, and user tier. If the fall is concentrated in Android version 8.2, you are close to the answer.
4. Decompose the metric. DAU = new users + retained users + resurrected users. Did acquisition fall (marketing or app-store ranking) or did existing users stop returning (product problem)?
5. Check internal changes. Recent releases, running experiments, pricing changes, paused marketing campaigns, broken push notifications or emails, and SEO changes.
6. Check external factors. Festivals and holidays such as Diwali, exam season, competitor launches, app-store policy changes, outages at payment or login providers, and regulation. Compare with the same period last year.
7. Form hypotheses, test them and quantify. Estimate how much of the 20% each cause explains, then recommend actions: roll back the release, fix the notification service or adjust campaigns.

Communicate as you go. Share a quick status with stakeholders early — “the drop is real, concentrated in Android, investigating the latest release” — rather than waiting for a perfect answer.

Note: Interviewers reward candidates who ask clarifying questions first, such as how DAU is defined and whether the drop is week-on-week or day-on-day, before diving into hypotheses.

A cohort is a group of users who share a starting event in the same period, usually the month they signed up or made a first purchase. A retention analysis tracks what share of each cohort is still active in each period after that start.

How to build it:

1. Assign each user a cohort, such as their first-order month.
2. For each activity, calculate the period number: months since the cohort month.
3. Count distinct active users per cohort and period, then divide by the cohort size.
4. Display it as a triangle-shaped matrix: rows are cohorts, columns are months since start, cells are retention percentages.

df['cohort'] = df.groupby('user_id')['order_date'].transform('min').dt.to_period('M')
df['period'] = (df['order_date'].dt.to_period('M') - df['cohort']).apply(lambda x: x.n)
counts = df.groupby(['cohort', 'period'])['user_id'].nunique().unstack()
retention = counts.div(counts[0], axis=0)

What it reveals that aggregate metrics hide:

• Growth masking churn: total active users can rise because acquisition is strong while each new cohort retains worse than the last. Aggregate numbers look healthy right up until acquisition slows.
• The shape of the curve: a curve that flattens — say at 25% after month four — suggests a core of users who find lasting value, a sign of product-market fit. A curve that keeps falling towards zero signals a deeper problem.
• Impact of changes: cohorts that joined after an onboarding redesign can be compared with earlier cohorts at the same age.
• Channel quality: splitting cohorts by acquisition channel often shows that cheap installs from one channel retain far worse.

Pitfalls: define “active” carefully and consistently; remember that recent cohorts have fewer observed periods (right-censoring); watch small cohorts where percentages are noisy; and account for seasonality, since a festive-season cohort may behave differently.

Note: Revenue retention cohorts, which track the spend of each cohort over time, are equally useful and can exceed 100% when surviving customers spend more.

Window functions perform a calculation across a set of rows related to the current row without collapsing them the way GROUP BY does. Each row keeps its detail and gains a computed value. The syntax is function() OVER (PARTITION BY ... ORDER BY ... frame).

• PARTITION BY splits rows into groups, similar to GROUP BY.
• ORDER BY sets the order within each partition.
• The frame, such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, limits which rows are included, for moving averages.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_spend,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS amount_rank,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order
FROM orders;

Common functions:

Top N per group — for example the three highest-paid employees in each department:

WITH ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees)
SELECT * FROM ranked WHERE rnk <= 3;

You need the CTE because window functions are evaluated after WHERE, so they cannot be filtered directly in the same query. Snowflake, BigQuery and Databricks offer QUALIFY as a shortcut.

Note: Interviewers often ask for the second-highest salary per department. Explain whether ties should count, then choose DENSE_RANK or ROW_NUMBER accordingly — that clarification is often what they are really testing.

All three work on a grouped DataFrame, but they differ in what shape of result they return, which determines when to use them.

# agg: one row per city
df.groupby('city').agg(revenue=('amount', 'sum'), orders=('order_id', 'nunique'))

# transform: broadcast the group statistic to every row
df['city_avg'] = df.groupby('city')['amount'].transform('mean')
df['share_of_city'] = df['amount'] / df.groupby('city')['amount'].transform('sum')
df['amount'] = df['amount'].fillna(df.groupby('city')['amount'].transform('median'))

# apply: arbitrary per-group logic, e.g. top two orders in each city
top2 = df.groupby('city', group_keys=False).apply(lambda g: g.nlargest(2, 'amount'))

When to use each:

• agg for summarising — totals, counts, means, multiple statistics at once with named aggregation.
• transform for feature engineering and cleaning — group averages, ranks, z-scores within a group, filling missing values with the group median, or computing each row's share of its group. No merge is needed because the result already aligns with the original index.
• apply only when the logic genuinely needs the whole group at once, such as fitting a small model per group. It is the slowest because it calls Python code for each group.
• filter is a related method that keeps or drops entire groups, for example cities with at least 100 orders.

Performance tips: built-in string aggregations such as 'sum' and 'mean' run in optimised code and are much faster than lambdas. For very large data, consider Polars or DuckDB, which handle grouped operations efficiently.

Note: A common interview follow-up is “how would you add each customer's total spend as a column?” The idiomatic answer is transform('sum'), not a groupby followed by a merge.

A time series is usually thought of as a combination of components:

• Trend: the long-term direction, such as steady growth in online orders.
• Seasonality: patterns that repeat over a fixed period — daily, weekly, yearly, or around festivals such as Diwali and the wedding season.
• Cycles: longer, irregular swings, such as economic cycles.
• Noise: random variation that cannot be predicted.

Stationarity means the statistical properties — mean, variance and autocorrelation — do not change over time. Classical models such as ARIMA assume it. Test with the Augmented Dickey-Fuller test (null hypothesis: the series is non-stationary) or the KPSS test (null hypothesis: stationary). Make a series stationary by differencing (subtracting the previous value, or the value a season ago) and by log transforms to stabilise variance.

A practical forecasting approach:

1. Plot and decompose the series, for example with STL decomposition, to see trend, seasonality, outliers and structural breaks such as the COVID period.
2. Set baselines: a naive forecast (last value) and a seasonal naive forecast (same day last week or last year). Many sophisticated models fail to beat them.
3. Try suitable models: exponential smoothing (ETS), ARIMA or SARIMA, Prophet for business series with holidays, or gradient boosting with lag, rolling-window and calendar features when there are many related series or external drivers such as price and promotions.
4. Validate with time-respecting backtests: rolling-origin (walk-forward) evaluation, never a random split, because the future cannot be used to predict the past.
5. Choose metrics that fit the business: MAE, WAPE or MAPE, with care when actual values are near zero.
6. Report prediction intervals, not just point forecasts, so planners can size inventory or staffing with the uncertainty in mind.

Note: For demand forecasting across thousands of products, a single global gradient-boosting model with lag features often beats one ARIMA model per product, and it is much easier to maintain.