Complete Data Analyst Interview Guide (0–2 Years of Experience)

🔹 Round 1: SQL + Scenario-Based Questions

Q1. Get top 3 products by revenue within each category

SELECT *
FROM (
SELECT p.product_id, p.category, SUM(o.revenue) AS total_revenue,
RANK() OVER(PARTITION BY p.category ORDER BY SUM(o.revenue) DESC) AS rnk
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_id, p.category
) ranked
WHERE rnk <= 3;

Q2. Find users who purchased in January but not in February

SELECT DISTINCT user_id
FROM orders
WHERE MONTH(order_date) = 1
AND user_id NOT IN (
SELECT user_id FROM orders WHERE MONTH(order_date) = 2
);

Q3. Avg. ride time by city + peak hours

SELECT city, AVG(DATEDIFF(MINUTE, start_time, end_time)) AS avg_ride_mins
FROM trips
GROUP BY city;

-- For peak hour detection (example logic)
SELECT DATEPART(HOUR, start_time) AS ride_hour, COUNT(*) AS ride_count
FROM trips
GROUP BY DATEPART(HOUR, start_time)
ORDER BY ride_count DESC;


⸻

🔹 Round 2: Python + Data Cleaning

Q1. Clean messy CSV with pandas

import pandas as pd

df = pd.read_csv('data.csv')
df.columns = df.columns.str.strip().str.lower()
df.drop_duplicates(inplace=True)
df['date'] = pd.to_datetime(df['date'], errors='coerce')
df.fillna(method='ffill', inplace=True)

Q2. Extract domain names from email IDs

emails = ['abc@gmail.com', 'xyz@outlook.com']
domains = [email.split('@')[1] for email in emails]

Q3. Difference: .loc[] vs .iloc[]
• .loc[] → label-based selection
• .iloc[] → index-based selection

Q4. Handle outliers using IQR

Q1 = df['column'].quantile(0.25)
Q3 = df['column'].quantile(0.75)
IQR = Q3 - Q1
filtered_df = df[(df['column'] >= Q1 - 1.5*IQR) & (df['column'] <= Q3 + 1.5*IQR)]


⸻

🔹 Round 3: Power BI / Dashboarding

Tasks you should know:
• Create a dashboard with weekly trends, margins, churn %
• Use bookmarks/slicers for KPI toggles
• Apply filters to show top 5 items dynamically
• Exclude visuals from slicer using “Edit Interactions” → turn off filter icon on card visual

🔗 Try replicating dashboards from Power BI Gallery

⸻

🔹 Round 4: Business Case + Logic-Based Thinking

Q1. Sales dropped last quarter — what to check?
• Compare YoY/QoQ data
• Identify categories/geos with the biggest drop
• Analyze order volume vs. avg. order value
• Check marketing spend, discounts, stockouts

Q2. App downloads ⬆️, activity ⬇️ — what’s wrong?
• Check Day 1/7/30 retention
• Is onboarding working?
• UI bugs or crashes?
• Compare install → sign-up → usage funnel

Q3. Returns increasing — how to investigate?
• Analyze return % by brand, category, SKU
• Check return reasons (defects, sizing, etc.)
• Compare returners’ order history
• Seasonal impact?

⸻

🔰 Free Practice Tools:
• 🔹 SQL on LeetCode
• 🔹 Python on Hackerrank
• 🔹 Power BI Gallery

React ❤️ For More