Data Analyst interview prep

First define repeat rate out loud: the share of customers with two or more orders in the period. Confirm the period and whether cancelled orders count.

Then: in a CTE, count orders per customer with GROUP BY customer_id. In the outer query, divide the number of customers with count >= 2 by all customers: SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) * 1.0 / COUNT(*). Multiply by 1.0 so integer division does not return zero.

Joins: an inner join keeps only rows that match in both tables; a left outer join keeps every row from the left table and fills NULLs where there is no match. For repeat rate by city you would left join customers to orders so customers with no orders still appear; say out loud that this changes the denominator from customers who ordered to all customers.

WITH c AS (
  SELECT customer_id,
         COUNT(*) AS n
  FROM orders
  GROUP BY customer_id
)
SELECT
  SUM(CASE WHEN n >= 2
      THEN 1 ELSE 0 END)
  * 1.0 / COUNT(*)
  AS repeat_rate
FROM c;

Join transactions to accounts (to get the customer) and customers (to get the city). Sum spend per customer and city with GROUP BY.

Then rank inside each city: DENSE_RANK() OVER (PARTITION BY city ORDER BY total_spend DESC). Wrap it in a CTE and keep rank <= 3.

Say how you handle ties: DENSE_RANK keeps everyone tied at third; ROW_NUMBER would cut to exactly three. Ask which the business wants. Also mention filtering out refunds or failed transactions before summing.

WITH s AS (
  SELECT cu.city,
         cu.customer_id,
         SUM(t.amount) AS spend
  FROM transactions t
  JOIN accounts a
    ON a.account_id = t.account_id
  JOIN customers cu
    ON cu.customer_id = a.customer_id
  GROUP BY cu.city, cu.customer_id
), r AS (
  SELECT *,
    DENSE_RANK() OVER (
      PARTITION BY city
      ORDER BY spend DESC) AS rk
  FROM s
)
SELECT * FROM r WHERE rk <= 3;

Start by agreeing the definition, because “whale” is a business term. Offer one: customers in the top 1% by spend in the last 90 days, or customers above a fixed number of transactions per month.

For a percentile definition: aggregate spend per customer, then use NTILE(100) OVER (ORDER BY total_spend DESC) or PERCENT_RANK and keep the top bucket. For a frequency definition: COUNT(*) per customer per month with HAVING above the threshold.

Close with a check: how much of total spend the whales account for, and whether the list is stable month to month.

Logical order: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT. That is why you cannot use a SELECT alias in WHERE, but you can in ORDER BY.

Third highest salary: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = 3. DISTINCT stops the same salary appearing once per person who earns it. DENSE_RANK handles duplicates, so two people on the top salary do not push the answer down.

Mention the alternative (ORDER BY salary DESC with DISTINCT and OFFSET 2) and what to return if fewer than three distinct salaries exist.

A classic self-join: find employees who earn more than their manager. Join employees e to employees m ON e.manager_id = m.id and keep e.salary > m.salary. Explain that you join the table to itself with two aliases.

A classic LAG question: month-on-month revenue change. Aggregate revenue by month, then LAG(revenue) OVER (ORDER BY month) gives last month’s value; subtract and divide for growth. LEAD looks forward instead, for example days until a customer’s next order.

Mention the first row returns NULL for LAG and how you would display it.

WITH m AS (
  SELECT DATE_TRUNC('month', order_date)
           AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
  revenue * 1.0
  / LAG(revenue) OVER (ORDER BY month)
  - 1 AS mom_growth
FROM m;

Second highest: MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employees) is short and handles duplicates. The DENSE_RANK version generalises to any N.

Duplicates: decide what “duplicate” means (same email? same every column?). Then GROUP BY those columns HAVING COUNT(*) > 1 to list them. To keep one copy, use ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY created_at) and delete or filter rows where it is above 1.

Amazon’s reviewer said one question was medium-hard, so expect a follow-up such as “now return the whole latest row per customer”.

This is “latest row per group”. Use ROW_NUMBER() OVER (PARTITION BY restaurant_id, dish_id ORDER BY last_updated DESC) in a CTE and keep row number 1.

Mention the alternative: join to a subquery of MAX(last_updated) per restaurant and dish. Point out it can return two rows if two updates share the same timestamp, which ROW_NUMBER avoids (add a tie-breaker column).

Then say why the business cares: showing an unavailable dish as available causes cancelled orders.

SELECT restaurant_id, dish_id,
       status, last_updated
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY restaurant_id,
                   dish_id
      ORDER BY last_updated DESC
    ) AS rn
  FROM dish_status
) t
WHERE rn = 1;

Joins: inner, left, right, full outer and cross. NULLs never equal anything, including another NULL, so rows with a NULL join key do not match in an inner join; in a left join they stay, with NULLs on the right side.

UNION: the columns must match in number and have compatible types. Most databases try to convert implicitly (an integer and a decimal combine), but text and dates will fail or convert unpredictably, so cast explicitly. UNION removes duplicates; UNION ALL keeps them and is faster.

A good add: count rows before and after a join to catch silent losses or duplication.

Both rank rows by an ORDER BY. With ties, RANK skips numbers (1, 1, 3) and DENSE_RANK does not (1, 1, 2). ROW_NUMBER gives every row a unique number even when tied.

Use DENSE_RANK for “Nth highest value” and ROW_NUMBER when you need exactly one row per group.

Show it on a tiny example out loud: three restaurants with ratings 4.5, 4.5 and 4.2. That makes the difference obvious in ten seconds.

Clarify the grain first: one row per advertiser per year, and which dimension (ad type, region) the team wants.

Then GROUP BY advertiser_id, EXTRACT(YEAR FROM spend_date) and, if needed, the dimension; SUM the spend. Add useful columns with window functions: share of the advertiser’s total, and year-on-year change with LAG.

Say how you would check it: totals by year should match the finance number, and advertisers with no spend in a year should show 0, not disappear (a LEFT JOIN to a calendar or advertiser list).

This is an anti-join: rows in one set with no match in another. The safest pattern is NOT EXISTS, because NOT IN returns nothing if the subquery contains a NULL.

SELECT DISTINCT o.customer_id
FROM orders o
WHERE o.order_date >= '2026-01-01'
  AND o.order_date <  '2026-02-01'
  AND NOT EXISTS (
    SELECT 1 FROM orders f
    WHERE f.customer_id = o.customer_id
      AND f.order_date >= '2026-02-01'
      AND f.order_date <  '2026-03-01'
  );

Use half-open date ranges (>= start, < next start) so you never miss orders late on the last day. The business reading: this is a churn list for a win-back campaign.

Aggregate to one row per day first, then average over the current row and the 6 before it.

WITH d AS (
  SELECT order_date AS day,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT day, revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING
         AND CURRENT ROW
  ) AS avg_7d
FROM d;

Point out the trap: ROWS counts rows, so a day with no orders is skipped and the window covers more than 7 calendar days. Join to a calendar table first, or use a RANGE window on dates where your database supports it. The first 6 days average fewer than 7 values.

VLOOKUP searches the first column of a range and returns a value from a column to the right. HLOOKUP does the same across rows. With a hard-coded index number, VLOOKUP breaks when columns are inserted (HLOOKUP when rows are), and neither can look left or up.

INDEX-MATCH combines MATCH (find the position) with INDEX (return the value at that position), so it can look in any direction and survives inserted columns.

XLOOKUP, in newer Excel, does both in one function, defaults to an exact match, can search from the bottom, and has a built-in “if not found” value. Use XLOOKUP when the client’s Excel supports it, INDEX-MATCH otherwise.

Pivot table: convert the data to a Table first so new rows are included, then put the category in Rows, the measure in Values (Sum or Count), and the date grouped by month in Columns. Use Show Values As for % of total.

Duplicates: Data → Remove Duplicates, choosing the columns that define a duplicate. Before deleting, flag them with COUNTIFS or the UNIQUE function so you can check what you are removing.

Mention that you keep the raw data untouched on its own sheet.

This is a live task, so talk while you work. For the lookup: =VLOOKUP(name, A:B, 2, FALSE); explain that FALSE forces an exact match, which is the most common VLOOKUP mistake.

For the pivot: Insert → PivotTable, put a grouping (class or grade band) in Rows and the student name in Values set to Count.

If you have time, show a check: COUNTA of the names should equal the pivot’s grand total.

Have one file ready before the interview. Open with the business question in one sentence and the answer in one sentence.

Then walk through the structure: a raw data sheet you never edit, a cleaning sheet, a calculations sheet, and an output sheet with the pivot or chart. Show one formula you are proud of and why you chose it.

End with what you would do differently with more time or a database.

Import copies the data into the Power BI model. It is fast and supports all DAX, but the data is only as fresh as the last scheduled refresh and the model has size limits.

DirectQuery leaves the data in the source and sends a query every time someone uses a visual. Data is current, but reports are slower, some DAX and transformations are limited, and the source database takes the load.

Pick Import for most reports; DirectQuery for very large or near-real-time data. Mention composite models, which mix both.

SUM adds up one column. SUMX is an iterator: it goes row by row through a table, evaluates an expression (such as quantity × price), then adds the results. Use SUMX when the value you need does not exist as a column.

A calculated column is computed once per row at refresh and stored in the model, so it uses memory and can be used to slice. A measure is computed at query time for whatever filters are on the visual, so it responds to slicers and stores nothing.

Rule of thumb: prefer measures for anything you aggregate.

Row-level security restricts which rows a user can see. You define roles with DAX filters in Power BI Desktop (for example Region = “West”), or dynamic roles using USERPRINCIPALNAME(), then assign people to roles in the Power BI service.

Performance: use a star schema, remove unused columns, reduce cardinality (split date-time into date and time), prefer measures over calculated columns, limit visuals per page, and use Performance Analyzer to find the slow visual and its DAX.

Say you test RLS with “View as role” before publishing.

Start from the question the chart must answer. Change over time: a line chart. Comparing categories: a sorted bar chart. Part of a whole: a stacked bar, or a pie only for two or three parts. Relationship between two numbers: a scatter plot. Distribution: a histogram or box plot.

Then cut clutter: one message per chart, a title that states the finding, labels on the data instead of a legend where possible, and colour only to highlight.

Give an example of a chart you changed because the first version confused people.

Aim for a star schema: one fact table of events or transactions (orders, with amounts and keys) surrounded by dimension tables that describe them (customer, product, date, store).

Steps: clean in Power Query, create a proper date table and mark it, set one-to-many relationships from dimensions to the fact with single-direction filters, and hide the key columns from report users.

Explain why: a star schema keeps DAX simple and reports fast, while one wide flat table or many-to-many relationships cause wrong totals and slow visuals.

Filter context is the set of filters active when a measure is evaluated: the row and column of the visual, slicers, and page or report filters. The same measure, such as [Total Sales], shows a different number in every cell because each cell has a different filter context.

CALCULATE evaluates an expression in a modified filter context. CALCULATE([Total Sales], Region = "West") adds or replaces a filter; CALCULATE([Total Sales], ALL(Product)) removes the product filter, which is how you build “% of total”.

Give one practical measure out loud, such as share of category: DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product[Name]))).

You need a proper date table, marked as the date table and related to the fact table.

Then: Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])). Growth = DIVIDE([Total Sales] - [Sales LY], [Sales LY]).

Mention the traps: without a continuous date table time intelligence returns wrong or blank values, and a partial current month compares against a full month last year, so you may need to limit last year to the same number of days.

LOD expressions compute an aggregate at a level different from the view. FIXED computes at the dimensions you name, whatever is on the view; INCLUDE adds a dimension to the view’s level; EXCLUDE removes one.

Classic use of FIXED: each customer’s first order date, { FIXED [Customer ID] : MIN([Order Date]) }, which lets you build cohorts by first-purchase month on any chart.

Mention that FIXED is computed before most filters unless they are context filters, so a regular filter will not change it.

Before the test: write the hypothesis, pick one primary metric (for example orders per user) and one or two guardrails (cancellations, delivery time), and estimate the sample size you need for the smallest change worth detecting.

During: randomise at the right unit (user, not session), check that the groups look alike, and do not stop early because the numbers look good.

After: compare the primary metric with a significance test and a confidence interval, check the guardrails, and check big segments. Ship only if the primary metric improves and no guardrail gets worse.

Start with what “better” means for the business: more engagement, more revenue, fewer complaints. Choose the metric that represents it and agree the time frame.

Then design the comparison: ideally a randomised test; if not possible, a careful before-and-after with a comparison group.

Stratified (blocked) randomisation: if users differ a lot by segment (country, new vs returning, platform), randomise within each segment so both groups get the same mix. That reduces noise and stops one group being, by chance, full of heavy users. Analyse per segment and overall.

Pick the business outcome first. If the goal is fewer failed payments: the null hypothesis is that the payment success rate is the same with UPI as with cards; the alternative is that it is different (or higher, for a one-sided test).

Primary metric: payment success rate. Guardrails: checkout conversion, average order value (card users may buy more on EMI), refund and dispute rate, and payment cost per order.

Say you would test it on a random share of users and check segments such as order value bands, because high-value buyers may need cards for EMI.

If there were truly no difference, a result at least this extreme would happen about 3% of the time. Below the usual 0.05 threshold, so we call it statistically significant. It is not the probability that the change works.

The interval says the true lift is plausibly between 0.4 and 3.2 points. It excludes zero, which matches p < 0.05, but the low end is small: decide whether +0.4 would still be worth shipping.

Type I error: a false positive, shipping a change that does nothing (its rate is the 5% threshold). Type II error: a false negative, missing a real effect, usually because the sample was too small.

Two numbers moving together does not mean one causes the other. A hidden factor can drive both, or the direction can run the other way.

Business example: customers who open our emails buy more. That may be because loyal customers both open emails and buy more, not because the emails work. To know, send the email to a random half and hold out the other half, then compare purchases.

Keep it to three sentences and end with the test you would run.

First check the data: did the complaint form, category list or tracking change? Is the rise in absolute complaints or in complaints per order (orders may simply be up)?

Then ask whether the SLA measures what customers feel. An average can stay flat while the slow tail grows; check the 90th percentile and late-order share. Customers may also complain about things the SLA does not cover: wrong items, rude partners, refunds, app bugs.

Split complaints by type, city, partner and app version to find where the rise sits. Recommend by cause, and suggest adding the missing measure to the SLA.

A north-star metric captures the value customers get and predicts long-term revenue. For delivery: completed on-time deliveries per week is a strong candidate, because it needs demand, supply and reliability to all work.

Under it, track drivers: orders placed, acceptance rate, pickup and delivery times, on-time %, cancellations. Add guardrails: cost per delivery, partner earnings per hour and complaints, so you do not grow the metric by burning money or partners.

Track it weekly by city in one dashboard with targets.

Clarify: gross merchandise value or net revenue? All categories? Compared with last December too?

Then think seasonality first. In India, October–November often carries the festive sale season, so December can be lower every year. Compare December with last December, not just with November.

If it is a real drop: split revenue into visitors × conversion × orders per buyer × order value. Check internal causes (fewer sales events, pricing, app issues, delivery delays) and external ones (competitor sales, payment problems). Segment by category, city and new vs returning users, then recommend by cause.

CTR = clicks ÷ impressions, so it can fall because clicks fell or because impressions rose. Check which.

If impressions jumped (a new surface or autoplay change shows more thumbnails), CTR can fall while total clicks rise: that may be fine. If clicks fell, check the data pipeline, then internal changes (a new ranking model, thumbnail layout, app release) and external ones (a holiday, a competitor event).

Segment by surface (home, search, suggested), device, country and creator type. Recommend a rollback or test for the change that lines up with the drop.

First question the forecast: what was it based on, and is it still valid?

Then walk the funnel: store impressions → store page views → installs → first open. Find the step where the gap sits. Low impressions point to discoverability (search ranking, ads); low page-to-install conversion points to the store page (screenshots, ratings, app size); low opens point to onboarding or a bug.

Segment by channel, country and device. Suggest one test per weak step, such as new screenshots or a smaller app size, and the metric you would watch.

Start from the email’s goal (bring people back, sell, inform). Then the funnel: delivered → opened → clicked → did the target action (a session, a purchase) within N days. Opens are unreliable because of privacy features, so lean on clicks and downstream actions.

Add guardrails: unsubscribes and spam complaints.

In the data: an email_events table (user, email_id, event_type, timestamp) joined to product events. Code it with conditional counts per email, and measure the real effect against a holdout group that did not get the email.

Pick a surface and its goal, for example Reels: help people find short videos they enjoy.

Metrics: daily users of Reels, time spent per user, and a quality signal such as shares or follows from Reels. Choose one as primary.

Target: base it on the trend, seasonality and the expected effect of planned launches, not a round number. Guardrails: overall app time (did Reels just steal from Feed?), creator posting, reports and well-being signals such as late-night use. Say you would review the target each quarter.

Agree the definitions: cost per order = total rider payouts (base pay + distance pay + incentives) ÷ delivered orders; rider earnings per hour = payouts ÷ logged-in hours.

In SQL or a pivot: group payouts, orders and hours by city and week, then divide. Show both side by side, because pushing cost per order down usually pushes earnings down too.

Point out what drives the differences: distance per order, orders per hour (batching), incentive spend at peak, and rain or festival days. Recommend where cost can fall without cutting earnings, such as better batching.

Choose a structure: people × share who eat pizza in a day. Assume Pune’s population is about 7 million (state it as an assumption).

Segment: say 40% are 15–40 and urban-affluent enough to buy pizza often. If they eat pizza about once a fortnight, that is 7M × 0.4 ÷ 14 ≈ 200,000 people a day; at about 1.5 people per pizza that is roughly 130,000 pizzas. Add a smaller older and younger segment at once a month.

Sanity-check from supply: outlets × pizzas per outlet per day. If both land in the same range, you are done.

Clarify: paint the four walls only, or the ceiling too? Any doors and windows? One coat or two?

Four walls: 4 × 10 × 10 = 400 m². Add the ceiling: 500 m². Subtract roughly 10% for a door and windows, so about 450 m².

Coverage: assume one litre covers about 10 m² per coat. Two coats: 450 × 2 ÷ 10 = 90 litres. Add 10% for waste: about 100 litres.

Structure by TV households: India has roughly 200 million TV households (an assumption to state). Most were at home during lockdown, and the re-runs were on the national broadcaster.

Assume 40–50% of TV households tuned in at least once in the month, and about 3–4 viewers per household. That gives roughly 200M × 0.45 × 3.5 ≈ 300 million people.

Say what would change it (cable vs free-to-air reach, repeat viewers) and how you would check: published broadcast ratings data for that period.

In pandas: group dishes by order_id into lists, then generate every pair within each order with itertools.combinations on the sorted list (sorting makes (A, B) and (B, A) the same). Count pairs with a Counter or value_counts and take the top one.

In SQL: self-join the order_items table on order_id where a.dish_id < b.dish_id, GROUP BY the pair and ORDER BY COUNT(*) DESC.

Mention scale: orders with many items create many pairs, so filter rare dishes first. The business use: combo offers and “frequently ordered together”.

A DataFrame is a two-dimensional table in pandas: labelled columns, each with its own data type, and an index for the rows. Think of it as a spreadsheet or SQL table in Python.

Then show you use it: read_csv to load, head and info to inspect, boolean filters to select rows, groupby with agg to summarise, merge to join, and isna with fillna or dropna for missing values.

A senior answer adds a caution: check dtypes after loading, because numbers read as text break sums silently.

Build two small dicts and wrap them in pd.DataFrame: customers (id, name, city) and orders (order_id, customer_id, amount). Include one customer with no orders and one order with a missing amount on purpose.

Merge: pd.merge(customers, orders, left_on="id", right_on="customer_id", how="left"). Explain why how="left" keeps customers without orders.

Then think before dropping. After the left merge, the customer with no orders also has a missing amount, so df.dropna(subset=["amount"]) removes them too. Say which you want: drop only orders with a missing amount (filter the orders table first, or use an inner merge), or keep the customer and fill amount with 0. Print shapes before and after.

First find out why they are missing or extreme, because the reason decides the fix. Profile each column: share missing, and whether missingness depends on something (one app version, one city).

Missing values: drop rows only when few are missing at random; otherwise fill with a sensible value (median, 0 where 0 is true, last known value for time series) and add a flag column so the fill is visible.

Outliers: check whether they are errors (a ₹10 crore order from a test account) or real (a bulk buyer). Fix or remove errors; keep real ones, but report the median alongside the mean or cap them for the analysis. Always say what you changed and how many rows it touched.

Analysis: agree the question, profile the data (size, gaps, duplicates), aggregate to the level that matters (week, region, segment), and look for changes over time and differences between segments. Check that a trend survives seasonality and a few outliers.

Presenting: lead with the answer in one sentence, then the two numbers that prove it, one chart with a title that states the finding, and the decision you recommend.

Use an example from your own work and say what the audience did next.

Validation: reconcile totals to a trusted source (finance, a known dashboard), re-run the key number a second way, check the data for gaps or duplicates in the period, and ask whether the effect holds across segments and time. Have a colleague sanity-check anything surprising.

Root cause: confirm the change is real, split the metric into its drivers, find which driver moved, then check internal changes (releases, pricing, operations) and external ones (season, competitors), and segment until the cause is narrow enough to act on.

Give one example where validation caught an error.

For an analyst, trust comes from numbers that hold up and from saying what you are unsure of.

Give habits: document how each metric is defined, show the query or source behind a number, flag caveats up front, deliver when you said you would, and correct mistakes quickly and openly.

Tell one short story: a time you found an error in your own work, told people before they found it, and fixed it. That is usually the moment trust was built.

Show that you separate the work from yourself. Say you ask what specifically is wrong and why, check it against the data, and change the work if they are right.

Give a real example: a manager said your dashboard was confusing, you asked which decisions they used it for, rebuilt it around those three questions, and usage went up.

If you disagreed, say how you showed the evidence calmly and accepted the final call.

Pick a work disagreement about substance, not personalities: for example two teams using different definitions of “active customer” and getting different numbers.

Explain what each side wanted, how you brought both definitions and the data to one meeting, and how you agreed on one definition (or showed both, clearly labelled).

End with the result: one number everyone used, and a written definition so it did not happen again.

Keep it to about 60 seconds in three parts: present, past, next.

Present: “I am a final-year B.Com student specialising in analytics.” Past: one or two pieces of proof, such as “I analysed 1.2 million public food-delivery orders in SQL and found repeat orders drop sharply after 45-minute waits; the dashboard is on my Tableau Public”, plus an internship or a role where you owned a report. Next: why this role and company, in one sentence.

Do not list every course. Interviewers often pick one thing you mention and ask about it, so lead with the project you know best.