Data analyst interview concepts: the 20 ideas to know before the interview

Four customers and five orders. Asha has two orders; Chitra and Dev have none; order 104 points at a customer who does not exist.

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

Notice: Four customers became five rows because Asha matched twice. Orders 104 and 105 match no customer, so neither join returns them.

This query fails in most databases, and the logical order explains why.

SELECT amount * 0.18 AS gst
FROM orders
WHERE gst > 50;  -- error

-- fix: repeat the expression
SELECT amount * 0.18 AS gst
FROM orders
WHERE amount * 0.18 > 50;

Notice: WHERE runs before SELECT, so the alias gst does not exist yet. ORDER BY runs after SELECT, so ORDER BY gst works. (Some engines, such as SQLite, accept the alias in WHERE anyway; MySQL does not. Do not rely on it.)

Same customers and orders as above. Which cities have more than ₹600 in matched orders?

SELECT c.city,
       SUM(o.amount) AS total,
       COUNT(*) AS orders
FROM orders o
JOIN customers c
  ON c.customer_id
   = o.customer_id
GROUP BY c.city
HAVING SUM(o.amount) > 600;

Notice: Pune is two orders from one customer. If the question was “cities with more than one buyer”, you would need COUNT(DISTINCT c.customer_id), which is 1 here. The same pattern finds duplicates (GROUP BY the key, HAVING COUNT(*) > 1), and SUM(CASE WHEN n >= 2 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) turns counts into a rate such as repeat rate.

Five scores with a tie at 85. “Find the second-highest score” depends on the function you pick.

SELECT name, score,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rk,
  DENSE_RANK() OVER w AS drk
FROM scores
WINDOW w AS
  (ORDER BY score DESC);

Notice: DENSE_RANK = 2 returns both Ben and Chitra (85). ROW_NUMBER = 2 returns one of them arbitrarily (not guaranteed to be the same next run) unless you add a tie-breaker. RANK = 3 returns nobody.

Five days of revenue (₹ thousand). Change versus the day before, and a 3-day rolling average.

SELECT d, revenue,
  revenue - LAG(revenue)
    OVER (ORDER BY d) AS change,
  AVG(revenue) OVER (
    ORDER BY d
    ROWS BETWEEN 2 PRECEDING
    AND CURRENT ROW) AS avg_3d
FROM daily;

Notice: The first change is NULL because there is no earlier row. The first two averages cover fewer than 3 days; say whether you would show or hide them.

The orders table above has one NULL customer_id. Find customers with no orders.

-- returns 0 rows
SELECT name FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders);

-- returns Chitra, Dev
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id
      = c.customer_id);

Notice: One NULL in the subquery makes NOT IN unknown for every row, so it returns nothing. NOT EXISTS (or a LEFT JOIN … WHERE o.order_id IS NULL) is safe.

Fetch Ben’s marks from a sheet where names are in column A and marks in column C.

=VLOOKUP("Ben", A2:C6, 3, FALSE)
=INDEX(C2:C6,
  MATCH("Ben", A2:A6, 0))
=XLOOKUP("Ben", A2:A6, C2:C6,
         "Not found")

Notice: All three return the same value. Only XLOOKUP needs no column number and no match-type flag, and it can return your own text instead of #N/A.

Five sales rows: region, product, sales (₹).

Notice: South has two Pens rows; the pivot adds them (80 + 100 = 180). Switch the value to Count and the same cell shows 2.

A small retail model.

Notice: Pick Category from dim_product and Sum of amount from fact_sales: Power BI follows the one-to-many relationship from product_key to filter the fact rows.

Four sale lines: Shoe A (2 × ₹1,500), Shoe B (1 × ₹2,000), Shirt (3 × ₹800), Cap (4 × ₹300).

Total Sales = SUMX(Sales,
  Sales[Qty] * Sales[Price])

Shoe Sales = CALCULATE(
  [Total Sales],
  Product[Category] = "Shoes")

Shoe Share = DIVIDE(
  [Shoe Sales], [Total Sales])

Notice: SUMX multiplies row by row, then adds: ₹8,600. SUM(Qty) × SUM(Price) would give 10 × ₹4,600 = ₹46,000, which is meaningless.

A sales dashboard that leaders open every morning, built on 2 years of orders.

Notice: Import, refreshed overnight, is the usual answer: yesterday’s data is fresh enough and visuals stay fast. Choose DirectQuery when the data must be current to the minute or is too large or too restricted to copy.

Three questions a manager might ask about the same sales data.

Notice: “How did sales move this year?” → line. “Which region sold most?” → bars sorted largest first. “Do bigger discounts bring bigger orders?” → scatter. Say the takeaway in the chart title.

Nine fictional salaries in ₹ LPA: 4, 5, 5, 6, 6, 7, 7, 8 and one founder-level 60.

Notice: One value doubled the mean; the median did not move. That is why salary and order-value reports usually lead with the median.

A test of a new checkout shows p = 0.03 and a 95% confidence interval of +0.4 to +3.2 percentage points in conversion.

Notice: p < 0.05 and the interval excludes 0, so at the usual 5% level you reject “no effect”. The true lift is plausibly anywhere from +0.4 to +3.2 points; whether +0.4 is worth shipping is a business call.

Baseline conversion is 10% and you want to detect a 1-point absolute lift (10% → 11%) at 5% significance (two-sided) and 80% power, the settings behind the 16 in Evan Miller’s rule.

n per group ≈ 16 × σ² / δ²
σ² = p(1 − p) = 0.10 × 0.90
   = 0.09
δ  = 0.01
n  ≈ 16 × 0.09 / 0.0001
   = 14,400 users per group

Notice: About 28,800 users in total before you look at the answer. Halve the effect you want to detect and the sample needed goes up four times.

Two app versions, 1,000 users each (fictional). B converts better on mobile and on desktop, yet worse overall.

Notice: B got mostly mobile users, who convert less everywhere. The device mix, not the version, drives the overall gap. In a properly randomised test the mix should be near-identical, so a 40% vs 80% mobile split is itself a red flag: check assignment before reading results.

A fictional subscription app’s funnel last month.

Notice: End to end, 1.5% of visitors pay. Every funnel loses the most people at the top, so compare each step with a benchmark or last month before picking where to dig: the 25% activated-to-paid step may be the real problem.

Fictional: “Revenue fell 20% in December vs November.” Revenue = buyers × orders per buyer × average order value.

Notice: People bought as often, but spent less per order. Next, split order value by category: a November festive sale on high-price items ending would explain it.

How much paint for a 10 × 10 × 10 m room? (The assumptions are labelled; change any of them.)

4 walls: 4 × 10 × 10 = 400 m²
Ceiling: 10 × 10      = 100 m²
Assume door + windows = −10 m²
Area to paint         = 490 m²
Two coats             = 980 m²
Assume 10 m² per litre
(read the tin)
Paint ≈ 98 litres ≈ 100 L

Notice: The interviewer cares that you asked “floor too?” and “how many coats?”, not whether 98 is right.

Three customers; four orders (one with no amount, one for an unknown customer 4).

m = customers.merge(
    orders, on="customer_id",
    how="left")
len(m)           # 4
len(m.dropna())  # 2

Notice: dropna removed Ben (an order with no amount) and Chitra (no order at all). Those are different problems; dropping both hides that Chitra never bought.