Data Analyst Interview Questions and Answers: 60 Questions from Easy to Hard

SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW) AS running_total,
       ROUND(AVG(revenue) OVER (ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING
                    AND CURRENT ROW), 1) AS avg_7d
FROM daily_revenue
ORDER BY order_date;

WITH steps AS (
    SELECT user_id,
           MIN(CASE WHEN ev = 'view' THEN ts END) AS t_view,
           MIN(CASE WHEN ev = 'cart' THEN ts END) AS t_cart,
           MIN(CASE WHEN ev = 'pay'  THEN ts END) AS t_pay
    FROM events
    GROUP BY user_id
)
SELECT COUNT(t_view) AS viewed,
       COUNT(CASE WHEN t_cart > t_view THEN 1 END) AS carted,
       COUNT(CASE WHEN t_pay > t_cart
                   AND t_cart > t_view THEN 1 END) AS paid
FROM steps;

WITH ranked AS (
    SELECT city, minutes,
           ROW_NUMBER() OVER (PARTITION BY city
                              ORDER BY minutes) AS rn,
           COUNT(*) OVER (PARTITION BY city) AS n
    FROM deliveries
    WHERE minutes IS NOT NULL
)
SELECT city, AVG(minutes) AS median_minutes
FROM ranked
WHERE 2 * rn BETWEEN n AND n + 2
GROUP BY city
ORDER BY city;

-- Slow: WHERE DATE(created_at) = '2026-11-01'
-- Fast: a range on the raw column can use an index
SELECT city, COUNT(*) AS orders
FROM orders
WHERE created_at >= '2026-11-01'
  AND created_at <  '2026-11-02'
GROUP BY city
ORDER BY city;

     E          F
1    From       Rate
2           0   0%
3      50,000   2%
4    1,00,000   3.5%
5    2,00,000   5%

B2 = 1,45,000
=VLOOKUP(B2, $E$2:$F$5, 2, TRUE)              -> 3.5%
=INDEX($F$2:$F$5, MATCH(B2, $E$2:$E$5, 1))    -> 3.5%

Flow   Device    Users   Paid   Rate
New    Mobile      800    160   20.0%
New    Desktop     200     90   45.0%
Old    Mobile      200     36   18.0%
Old    Desktop     800    340   42.5%
New overall: 250 / 1,000 = 25.0%
Old overall: 376 / 1,000 = 37.6%

SELECT COUNT(*) AS total_rows,
       COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_ids,
       SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_amt,
       SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS negative_amt,
       MAX(created_at) AS latest_row
FROM orders;

city    visitors   orders
Pune         100       50
Delhi     10,000      900
Kochi         60       30

SELECT ROUND(AVG(100.0 * orders / visitors), 1) AS avg_of_rates,
       ROUND(100.0 * SUM(orders) / SUM(visitors), 1) AS overall
FROM city_daily;
-- avg_of_rates = 36.3, overall = 9.6

Month  New      From last     From two       MAU
                 month's new   months ago
Jan    10,000    -             -              10,000
Feb    15,000    4,000 (40%)   -              19,000
Mar    20,000    5,250 (35%)   3,000 (30%)    28,250

from math import ceil, sqrt
from statistics import NormalDist

def n_per_group(p1, p2, alpha=0.05, power=0.80):
    z_a = NormalDist().inv_cdf(1 - alpha / 2)
    z_b = NormalDist().inv_cdf(power)
    p = (p1 + p2) / 2
    top = (z_a * sqrt(2 * p * (1 - p))
           + z_b * sqrt(p1 * (1 - p1) + p2 * (1 - p2)))
    return ceil(top ** 2 / (p2 - p1) ** 2)

print(n_per_group(0.10, 0.11))   # 14751

City     Before   After   Change
Jaipur    5,000   4,600     -400   (fee launched)
Indore    4,000   3,800     -200   (no change)
Effect = -400 - (-200) = -200 orders a day

Month    Visitors   Conversion   AOV      Revenue
Sep      5,00,000   3.0%         Rs 800   Rs 1.20 crore
Oct      4,80,000   2.5%         Rs 850   Rs 1.02 crore
Change   -4.0%      -16.7%       +6.25%   -15.0%

import csv
from collections import defaultdict

def city_totals(path):
    totals, bad = defaultdict(int), 0
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            try:
                totals[row["city"]] += int(row["amount"])
            except (KeyError, TypeError, ValueError):
                bad += 1
    return dict(totals), bad