Data Analyst Interview Preparation: SQL, Excel, Power BI, Statistics and Case Questions

The interview loop

SQL patterns you must know

import duckdb, pandas as pd
sales = pd.read_csv("superstore_sales.csv", parse_dates=["order_date"])
print(duckdb.sql("""
-- Top 2 products by sales in each region (window function)
WITH r AS (
  SELECT region, product_name, SUM(sales) AS sales,
         RANK() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) AS rnk
  FROM sales GROUP BY region, product_name)
SELECT region, product_name, ROUND(sales) AS sales FROM r WHERE rnk <= 2 ORDER BY region, rnk;
""").df())

  region        product_name      sales
0   East     Apex Copier 347  4191180.0
1   East   Zenith Copier 719  3323372.0
2  North    Orbit Copier 746  3790940.0
3  North  Zenith Machine 401  3566453.0
4  South    Orbit Copier 746  5405517.0
5  South   Vertex Copier 262  4258600.0
6   West   Zenith Copier 719  4087068.0
7   West  Zenith Machine 401  4023468.0

import duckdb, pandas as pd
sales = pd.read_csv("superstore_sales.csv", parse_dates=["order_date"])
print(duckdb.sql("""
-- Month-over-month growth with LAG
WITH m AS (SELECT DATE_TRUNC('month', order_date) AS month, SUM(sales) AS sales FROM sales GROUP BY 1)
SELECT month, ROUND(sales) AS sales, ROUND(100.0 * (sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales) OVER (ORDER BY month), 1) AS mom_pct
FROM m ORDER BY month DESC LIMIT 4;
""").df())

       month       sales  mom_pct
0 2025-12-01   7652267.0    -25.6
1 2025-11-01  10284516.0     67.5
2 2025-10-01   6140290.0    -27.5
3 2025-09-01   8468060.0     45.9

Approach out loud: clarify the table grain → write the query step by step (CTEs) → check edge cases (NULLs, ties, duplicates) → validate with a small count.

Excel and Power BI questions

Statistics and experiments

Be ready to explain: mean vs median, standard deviation, distributions, correlation vs causation, confidence intervals, p-values, Type I/II errors, sample size and A/B test pitfalls (peeking, SRM, novelty). See ML lessons 1–6 for model answers.

Case questions: a framework

1. Clarify the metric and time frame. 2. Check data quality first. 3. Segment (where is the drop?). 4. Form hypotheses (internal vs external). 5. Validate with data. 6. Recommend action and how you would measure it.

Product-sense metrics: define a north-star metric, input metrics and guardrails (e.g. for a food-delivery app: weekly ordering customers; conversion, delivery time, order value; cancellations, complaints).

Behavioural: STAR

Situation (context) → Task (your goal) → Action (what you did, with tools) → Result (numbers, learning). Prepare 6 stories: a project you are proud of, a mistake you fixed, a conflict, a tight deadline, influencing without authority, learning a new tool fast.

Practice resources

W3Schools SQL quiz and exercises, LeetCode SQL 50, HackerRank SQL, StrataScratch, DataLemur, Kaggle Learn; mock interviews with peers; time yourself (15 minutes per medium SQL question).