Data Interview Preparation

What to Expect in Data Analyst Interviews

Data analyst interviews typically involve several stages. Understanding the structure helps you prepare efficiently rather than studying everything equally.

Common interview stages:

1. Recruiter/HR screen (15-30 minutes) -- background, motivation, salary expectation
2. Technical take-home task or live SQL/Python test (1-4 hours)
3. Technical interview (45-60 minutes) -- SQL, statistics, problem-solving
4. Case study or business scenario discussion (45-60 minutes)
5. Stakeholder/hiring manager final interview -- communication, experience, fit

Not every company uses all stages. Startups often skip to a brief technical test followed by a conversation. Large enterprises may run 5-6 rounds.

SQL Interview Questions: What to Expect

SQL is tested in almost every data analyst interview. You will face two types:

Conceptual questions (can you explain what X is?)

• "What is the difference between INNER JOIN and LEFT JOIN?"
• "When would you use a CTE vs a subquery?"
• "What is a window function and when would you use one?"

Practical questions (write a query that does X)

• "Given a table of orders, find customers who placed more than 3 orders in the last 30 days"
• "Write a query to calculate a 7-day rolling average of daily revenue"
• "Find the top 3 products by revenue in each category"

-- Common SQL interview question: Top N per group
-- Find the top 2 customers by total spend in each region

WITH customer_totals AS (
    SELECT
        region,
        customer_id,
        SUM(order_value) AS total_spend,
        RANK() OVER (PARTITION BY region ORDER BY SUM(order_value) DESC) AS rank
    FROM orders
    GROUP BY region, customer_id
)
SELECT region, customer_id, total_spend
FROM customer_totals
WHERE rank <= 2;

-- Rolling 7-day average
SELECT
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_revenue_table;

-- Customer retention cohort
SELECT
    DATE_TRUNC('month', first_order_date) AS cohort_month,
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(DISTINCT customer_id) AS customers
FROM (
    SELECT
        customer_id,
        order_date,
        MIN(order_date) OVER (PARTITION BY customer_id) AS first_order_date
    FROM orders
) sub
GROUP BY 1, 2
ORDER BY 1, 2;

Statistics and Analytical Thinking Questions

Interviewers want to assess whether you think statistically, not just technically.

Common questions:

• "A metric increased by 20% this week. How would you investigate whether this is real or an anomaly?"
• "We ran an A/B test for 2 weeks and saw a 5% improvement. Can we ship the change?"
• "How would you define and measure 'customer health' for our SaaS product?"

Framework for metric change investigation:

When a metric changes unexpectedly:

1. Data integrity check
   - Is the tracking code working correctly?
   - Are there logging errors or duplicate events?
   - Has the data pipeline changed?

2. Segment decomposition
   - Does the change hold across all user segments, or is it driven by one?
   - Is it platform-specific (mobile vs desktop)?
   - Is it geography-specific?

3. External factors
   - Did we make a product change around that time?
   - Was there a marketing campaign?
   - Is there a seasonal pattern?
   - Did a competitor do something?

4. Statistical validation
   - Is the change within normal variation (control chart / z-score)?
   - How many users are affected?
   - What is the confidence interval around the change?

A/B Testing Interview Questions

A/B testing questions test whether you understand statistical validity, not just arithmetic.

Classic question: "Our A/B test shows a 3% improvement in conversion. After 4 days and 1,000 users, the p-value is 0.03. Should we ship?"

Strong answer structure:

1. Check sample size: was 1,000 users enough to detect a 3% effect with 80% power?
2. Check test duration: 4 days may not cover a full weekly cycle (Monday behaviour differs from Friday)
3. Check for novelty effect: did users engage just because it was new?
4. Check segment consistency: does the improvement hold across all user segments?
5. Check business significance: is 3% meaningful at this scale? What is the revenue impact?

from statsmodels.stats.power import NormalIndPower

# Calculate required sample size for an A/B test
effect_size = 0.03 / 0.10  # 3% improvement on a 10% baseline
analysis = NormalIndPower()
n = analysis.solve_power(effect_size=effect_size, alpha=0.05, power=0.80)
print(f"Required sample size per group: {int(n):,}")
# If actual sample < this, the test is underpowered

Behavioural and Case Questions

Behavioural questions test how you have handled real situations. Use the STAR format:

Situation: What was the context? Task: What were you responsible for? Action: What did you do specifically? Result: What was the measurable outcome?

Common behavioural questions:

• "Tell me about a time you found a significant error in a dataset. How did you handle it?"
• "Describe a situation where you had to explain complex data to a non-technical stakeholder."
• "Give me an example of an insight you found that changed a business decision."
• "Tell me about a project that did not go as planned. What did you learn?"

Business case questions test your ability to structure an ambiguous problem:

• "How would you measure the success of a new feature launch?"
• "Our revenue is down 15% this quarter. Walk me through how you would investigate this."
• "Design a dashboard for the head of marketing."

Use a structured framework: clarify the question, state your assumptions, define metrics, identify data sources, and suggest how you would present findings.

Technical Take-Home Tasks

Many companies send a take-home analysis to complete in 1-4 hours. Treat this as a portfolio project:

1. Read the brief carefully. Answer the question asked, not the question you wish was asked.
2. Document your assumptions. If data is ambiguous, state how you interpreted it.
3. Show your thinking. Include comments in code. Show intermediate steps.
4. Lead with findings. Start with a summary of key insights, then show supporting evidence.
5. Include limitations. What could not be measured? What would you do with more time?
6. Submit clean work. Well-formatted code, clear charts with titles, no spelling errors.

What to Ask at the End

Asking thoughtful questions signals genuine interest and analytical thinking.

Good questions for a data analyst interview:

• "How does the analytics team typically work with the product and engineering teams?"
• "What does the data infrastructure look like today, and where are you investing in it?"
• "What is the most important analytical question the team is trying to answer right now?"
• "How do you measure the impact of the analytics team's work?"
• "What does success look like for someone in this role in the first 90 days?"

Avoid questions whose answers are clearly on the company website. It signals lack of research.

Key Takeaways

• Data analyst interviews typically involve SQL tests, statistics questions, business case discussions, and behavioural questions. Prepare for all four types.
• SQL window functions, CTEs, and cohort queries appear frequently in technical tests. Practice writing these until they are fluent.
• When asked about a metric change, follow a structured investigation: data integrity, segment decomposition, external factors, statistical validation.
• Use the STAR format for behavioural questions. Have 4-5 concrete examples from projects (including portfolio projects) ready to discuss.
• Take-home tasks are portfolio opportunities. Lead with findings, document assumptions, show your thinking, and include limitations.

Practice Exercise

-- Practice these SQL patterns before your next interview:

-- 1. Find customers with more than 2 orders in the last 30 days
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) > 2;

-- 2. Calculate month-over-month revenue growth
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(order_value) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
          LAG(revenue) OVER (ORDER BY month) * 100, 1) AS pct_change
FROM monthly;

-- 3. Find the percentage of revenue from the top 20% of customers (Pareto)
WITH customer_spend AS (
    SELECT
        customer_id,
        SUM(order_value) AS total_spend,
        NTILE(5) OVER (ORDER BY SUM(order_value) DESC) AS quintile
    FROM orders
    GROUP BY customer_id
)
SELECT
    quintile,
    COUNT(*) AS customers,
    SUM(total_spend) AS revenue,
    ROUND(100.0 * SUM(total_spend) / SUM(SUM(total_spend)) OVER (), 1) AS pct_of_revenue
FROM customer_spend
GROUP BY quintile
ORDER BY quintile;