Data Analyst Interview Resourc… on Telegram: “✅ Data Analyst Interview Questions 📊 🟨 SQL 1️⃣…”

✅ Data Analyst Interview Questions 📊

🟨 SQL
1️⃣ Write a query to find the second highest salary in the employee table.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

(Handles ties; alternative: use DENSE_RANK() for modern SQL.)

2️⃣ Get the top 3 products by revenue from sales table.

SELECT product_id, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_id
ORDER BY total_revenue DESC
LIMIT 3;

3️⃣ Use JOIN to combine customer and order data.

SELECT c.customer_name, o.order_date, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

(Use INNER for matches; LEFT for all customers.)

4️⃣ Difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters after GROUP BY (e.g., on aggregates like SUM). WHERE is for individual rows, HAVING for grouped results.

5️⃣ Explain INDEX and how it improves performance.
An INDEX speeds up data retrieval by creating a data structure (like a B-tree) for quick lookups on columns. It reduces full table scans but adds overhead on inserts/updates.

🟦 Excel / Power BI
1️⃣ How would you clean messy data in Excel?
Use Text to Columns for splitting, Find & Replace for errors, Remove Duplicates tool, and Power Query for advanced ETL (e.g., trim spaces, handle dates).

2️⃣ What is the difference between Pivot Table and Power Pivot?
Pivot Tables summarize data visually; Power Pivot adds data modeling (relationships, DAX) for larger datasets and complex calculations beyond standard Pivots.

3️⃣ Explain DAX measures vs calculated columns.
Measures are dynamic formulas (e.g., SUM for totals) computed on-the-fly for reports; calculated columns are static, row-by-row computations stored in the model.

4️⃣ How to handle missing values in Power BI?
Use Power Query to replace nulls (e.g., with averages via "Replace Values"), or DAX like IF(ISBLANK()) in visuals. For viz, filter them out or use "Show items with no data."

5️⃣ Create a KPI visual comparing actual vs target sales.
In Power BI, drag KPI visual, add actual sales to Value, target to Target, and trend metric. Set variance to show % difference—green/red indicators highlight performance.

🟩 Python
1️⃣ Write a function to remove outliers from a list using IQR.

import numpy as np
def remove_outliers(data):
    Q1 = np.percentile(data, 25)
    Q3 = np.percentile(data, 75)
    IQR = Q3 - Q1
    lower = Q1 - 1.5 * IQR
    upper = Q3 + 1.5 * IQR
    return [x for x in data if lower <= x <= upper]

2️⃣ Convert a nested list to a flat list.

nested = [[1, 2], [3, 4]]
flat = [item for sublist in nested for item in sublist]
# Or: import itertools; list(itertools.chain.from_iterable(nested))

3️⃣ Read a CSV file and count rows with nulls.

import pandas as pd
df = pd.read_csv('file.csv')
null_counts = df.isnull().sum(axis=1)
print(null_counts[null_counts > 0].count())  # Rows with at least one null

4️⃣ How do you handle missing data in pandas?
Use df.fillna(value) for imputation (e.g., mean), df.dropna() to drop rows/cols, or df.interpolate() for time series. Check with df.isnull().sum() first.

5️⃣ Explain the difference between loc[] and iloc[].
loc[] uses labels (e.g., df.loc['row_label']) for selection; iloc[] uses integer positions (e.g., df.iloc[0:2])—great for slicing by index.

💡 Pro Tip: Practice with mock datasets from Kaggle + build dashboards on Power BI to showcase in interviews.

💬 Tap ❤️ for detailed answers!