Data Analyst Interview Resources

𝗛𝗼𝘄 𝗥𝗮𝘄 𝗗𝗮𝘁𝗮 𝗕𝗲𝗰𝗼𝗺𝗲𝘀 𝗥𝗲𝗮𝗹 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗩𝗮𝗹𝘂𝗲

Data creates impact only when it turns into decisions. The analytics process can be seen as a simple journey:

🔹 Data – Raw, messy information collected from systems, users, or transactions.

🔹 Sorted – Cleaning and organizing the data by removing duplicates and fixing inconsistencies.

🔹 Arranged – Analyzing the data through aggregation, grouping, and exploration to find patterns.

🔹 Presented Visually – Using charts and dashboards to make insights easy to understand.

🔹 Explained with a Story – Connecting insights to real business problems and context.

🔹 Actionable – Turning insights into better decisions and improvements.

📊 Great analysts don’t just analyze data — they turn it into decisions that create value.

🧠 SQL Interview Question (Moderate–Tricky & Identifying Users with Increasing Transactions)
📌

transactions(transaction_id, user_id, transaction_date, amount)

❓ Ques :

👉 Find users whose transaction amount strictly increases with every new transaction.

🧩 How Interviewers Expect You to Think

• Sort transactions by date for each user
• Compare each amount with the previous one
• Identify users whose amounts always increase

💡 SQL Solution

WITH t AS (
SELECT
user_id,
amount,
LAG(amount) OVER (
PARTITION BY user_id
ORDER BY transaction_date
) AS prev_amount
FROM transactions
)

SELECT user_id
FROM t
GROUP BY user_id
HAVING SUM(
CASE
WHEN prev_amount IS NOT NULL AND amount <= prev_amount
THEN 1 ELSE 0
END
) = 0;

🔥 Why This Question Is Powerful

• Tests understanding of LAG() with conditional logic
• Evaluates ability to validate patterns across sequential data
• Reflects real-world analytics like tracking user spending growth trends

❤️ React if you want more tricky real interview-level SQL questions 🚀

Top 100 Data Analyst Interview Questions

✅ Data Analytics Basics
1. What is data analytics?
2. Difference between data analytics and data science?
3. What problems does a data analyst solve?
4. What are the types of data analytics?
5. What tools do data analysts use daily?
6. What is a KPI?
7. What is a metric vs KPI?
8. What is descriptive analytics?
9. What is diagnostic analytics?
10. What does a typical day of a data analyst look like?

Data and Databases
11. What is structured data?
12. What is semi-structured data?
13. What is unstructured data?
14. What is a database?
15. Difference between OLTP and OLAP?
16. What is a primary key?
17. What is a foreign key?
18. What is a fact table?
19. What is a dimension table?
20. What is a data warehouse?

SQL for Data Analysts
21. What is SELECT used for?
22. Difference between WHERE and HAVING?
23. What is GROUP BY?
24. What are aggregate functions?
25. Difference between INNER and LEFT JOIN?
26. What are subqueries?
27. What is a CTE?
28. How do you handle duplicates in SQL?
29. How do you handle NULL values?
30. What are window functions?

Excel for Data Analysis
31. What are pivot tables?
32. Difference between VLOOKUP and XLOOKUP?
33. What is conditional formatting?
34. What are COUNTIFS and SUMIFS?
35. What is data validation?
36. How do you remove duplicates in Excel?
37. What is IF formula used for?
38. Difference between relative and absolute reference?
39. How do you clean data in Excel?
40. What are common Excel mistakes analysts make?

Data Cleaning and Preparation
41. What is data cleaning?
42. How do you handle missing data?
43. How do you treat outliers?
44. What is data normalization?
45. What is data standardization?
46. How do you check data quality?
47. What is duplicate data?
48. How do you validate source data?
49. What is data transformation?
50. Why is data preparation important?

Statistics for Data Analysts
51. Difference between mean and median?
52. What is standard deviation?
53. What is variance?
54. What is correlation?
55. Difference between correlation and causation?
56. What is an outlier?
57. What is sampling?
58. What is distribution?
59. What is skewness?
60. When do you use median over mean?

Data Visualization
61. Why is data visualization important?
62. Difference between bar and line chart?
63. When do you use a pie chart?
64. What is a dashboard?
65. What makes a good dashboard?
66. What is a KPI card?
67. Common visualization mistakes?
68. How do you choose the right chart?
69. What is drill down?
70. What is data storytelling?

Power BI or Tableau
71. What is Power BI or Tableau used for?
72. What is a data model?
73. What is a relationship?
74. What is DAX?
75. Difference between measure and calculated column?
76. What is Power Query?
77. What are filters and slicers?
78. What is row level security?
79. What is refresh schedule?
80. How do you optimize reports?

Business and Case Questions
81. How do you analyze a sales drop?
82. How do you define success metrics?
83. What business metrics have you worked on?
84. How do you prioritize insights?
85. How do you validate insights?
86. What questions do you ask stakeholders?
87. How do you handle vague requirements?
88. How do you measure business impact?
89. How do you explain numbers to managers?
90. How do you recommend actions?

Projects and Real World
91. Explain your best project.
92. What data sources did you use?
93. How did you clean the data?
94. What insight had the most impact?
95. What challenge did you face?
96. How did you solve it?
97. How did stakeholders use your dashboard?
98. What would you improve in your project?
99. How do you handle tight deadlines?
100. Why should we hire you as a data analyst?

Double Tap ♥️ For Detailed Answers

Essential Excel Functions for Data Analysts 🚀

1️⃣ Basic Functions

SUM() – Adds a range of numbers. =SUM(A1:A10)

AVERAGE() – Calculates the average. =AVERAGE(A1:A10)

MIN() / MAX() – Finds the smallest/largest value. =MIN(A1:A10)


2️⃣ Logical Functions

IF() – Conditional logic. =IF(A1>50, "Pass", "Fail")

IFS() – Multiple conditions. =IFS(A1>90, "A", A1>80, "B", TRUE, "C")

AND() / OR() – Checks multiple conditions. =AND(A1>50, B1<100)


3️⃣ Text Functions

LEFT() / RIGHT() / MID() – Extract text from a string.

=LEFT(A1, 3) (First 3 characters)

=MID(A1, 3, 2) (2 characters from the 3rd position)


LEN() – Counts characters. =LEN(A1)

TRIM() – Removes extra spaces. =TRIM(A1)

UPPER() / LOWER() / PROPER() – Changes text case.


4️⃣ Lookup Functions

VLOOKUP() – Searches for a value in a column.

=VLOOKUP(1001, A2:B10, 2, FALSE)


HLOOKUP() – Searches in a row.

XLOOKUP() – Advanced lookup replacing VLOOKUP.

=XLOOKUP(1001, A2:A10, B2:B10, "Not Found")



5️⃣ Date & Time Functions

TODAY() – Returns the current date.

NOW() – Returns the current date and time.

YEAR(), MONTH(), DAY() – Extracts parts of a date.

DATEDIF() – Calculates the difference between two dates.


6️⃣ Data Cleaning Functions

REMOVE DUPLICATES – Found in the "Data" tab.

CLEAN() – Removes non-printable characters.

SUBSTITUTE() – Replaces text within a string.

=SUBSTITUTE(A1, "old", "new")



7️⃣ Advanced Functions

INDEX() & MATCH() – More flexible alternative to VLOOKUP.

TEXTJOIN() – Joins text with a delimiter.

UNIQUE() – Returns unique values from a range.

FILTER() – Filters data dynamically.

=FILTER(A2:B10, B2:B10>50)



8️⃣ Pivot Tables & Power Query

PIVOT TABLES – Summarizes data dynamically.

GETPIVOTDATA() – Extracts data from a Pivot Table.

POWER QUERY – Automates data cleaning & transformation.


You can find Free Excel Resources here: https://t.me/excel_data

Hope it helps :)

#dataanalytics

📊 Essential SQL Concepts Every Data Analyst Must Know

🚀 SQL is the most important skill for Data Analysts. Almost every analytics job requires working with databases to extract, filter, analyze, and summarize data.

Understanding the following SQL concepts will help you write efficient queries and solve real business problems with data.

1️⃣ SELECT Statement (Data Retrieval)

What it is: Retrieves data from a table.

SELECT name, salary
FROM employees;

Use cases: Retrieving specific columns, viewing datasets, extracting required information.

2️⃣ WHERE Clause (Filtering Data)

What it is: Filters rows based on specific conditions.

SELECT *
FROM orders
WHERE order_amount > 500;

Common conditions: =, >, <, >=, <=, BETWEEN, IN, LIKE

3️⃣ ORDER BY (Sorting Data)

What it is: Sorts query results in ascending or descending order.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Sorting options: ASC (default), DESC

4️⃣ GROUP BY (Aggregation)

What it is: Groups rows with same values into summary rows.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Use cases: Sales per region, customers per country, orders per product category.

5️⃣ Aggregate Functions

What they do: Perform calculations on multiple rows.

SELECT AVG(salary)
FROM employees;

Common functions: COUNT(), SUM(), AVG(), MIN(), MAX()

6️⃣ HAVING Clause

What it is: Filters grouped data after aggregation.

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Key difference: WHERE filters rows before grouping, HAVING filters groups after aggregation.

7️⃣ SQL JOINS (Combining Tables)

What they do: Combine tables.

-- INNER JOIN

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

-- LEFT JOIN

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Common types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

8️⃣ Subqueries

What it is: Query inside another query.

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Use cases: Comparing values, filtering based on aggregated results.

9️⃣ Common Table Expressions (CTE)

What it is: Temporary result set used inside a query.

WITH high_salary AS (
  SELECT name, salary
  FROM employees
  WHERE salary > 70000
)
SELECT *
FROM high_salary;

Benefits: Cleaner queries, easier debugging, better readability.

🔟 Window Functions

What they do: Perform calculations across rows related to current row.

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()

Why SQL is Critical for Data Analysts
• Extract data from databases
• Analyze large datasets efficiently
• Generate reports and dashboards
• Support business decision-making

SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ♥️ For More

📊 Interviewer: How do you remove duplicate records in SQL?

👋 Me: We can remove duplicates using DISTINCT, GROUP BY, or delete duplicate rows using ROW_NUMBER().


✅ 1️⃣ Using DISTINCT (to fetch unique values)

SELECT DISTINCT column_name
FROM employees;

👉 Returns unique records but does not delete duplicates.


✅ 2️⃣ Using GROUP BY (to identify duplicates)

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

👉 Helps find duplicate records.


✅ 3️⃣ Delete Duplicates Using ROW_NUMBER() (Most Important ⭐)
(Keeps one record and deletes others)

DELETE FROM employees
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY name, salary
             ORDER BY id
           ) AS rn
    FROM employees
  ) t
  WHERE rn > 1
);

🧠 Logic Breakdown:

- DISTINCT → shows unique records
- GROUP BY → identifies duplicates
- ROW_NUMBER() → removes duplicates safely


✅ Use Case: Data cleaning, ETL processes, data quality checks.

💡 Tip: Always take a backup before deleting duplicate records.

💬 Tap ❤️ for more!

🧠 SQL Interview Question (Moderate & Revenue Analysis)
📌

orders(order_id, customer_id, order_amount)

❓ Ques :

👉 Find customers who contribute more than 30% of the total company revenue.

🧩 How Interviewers Expect You to Think

• Calculate overall total revenue
• Aggregate revenue at customer level
• Compare individual contribution against total
• Avoid recalculating total multiple times inefficiently

💡 SQL Solution

WITH total_revenue AS (
SELECT SUM(order_amount) AS total_rev
FROM orders
),
customer_revenue AS (
SELECT
customer_id,
SUM(order_amount) AS cust_rev
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id
FROM customer_revenue c
CROSS JOIN total_revenue t
WHERE c.cust_rev > 0.30 * t.total_rev;

🔥 Why This Question Is Powerful

• Tests percentage-based business logic
• Evaluates ability to combine multiple aggregations
• Reflects real-world Pareto (80/20) analysis scenarios
• Common in product, growth & revenue analytics interviews

❤️ React if you want more real interview-level SQL questions

1. What is the difference between the RANK() and DENSE_RANK() functions?

The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.

2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?

One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.

3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?

Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.

4. How can you split a column into 2 or more columns?

You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.

5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?

https://t.me/sqlspecialist/851

Here are free resources that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.

🧠 SQL Interview Question (Moderate & Analytical)
📌

events(user_id, event_name, event_date)

-- event_name values: 'Visited', 'Added_to_Cart', 'Purchased'

❓ Ques :

👉 Find users who added a product to cart but never completed the purchase.

🧩 How Interviewers Expect You to Think

• Understand funnel stage logic
• Apply conditional aggregation correctly
• Ensure absence of a specific event
• Avoid double counting users

💡 SQL Solution

SELECT
user_id
FROM events
GROUP BY user_id
HAVING
SUM(CASE WHEN event_name = 'Added_to_Cart' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN event_name = 'Purchased' THEN 1 ELSE 0 END) = 0;

🔥 Why This Question Is Powerful

• Tests real business thinking (conversion funnel analysis)
• Checks ability to detect missing conditions
• Common in product & e-commerce analytics interviews
• Evaluates aggregation + logical filtering skills together

❤️ React if you want more real interview-level SQL questions

🧠 SQL Interview Question (Tricky & Logic-Based)
📌

logins(user_id, login_date)

❓ Ques :

👉 Find users who logged in for 3 or more consecutive days.

🧩 How Interviewers Expect You to Think

• Understand consecutive date logic
• Use date arithmetic smartly
• Create groups using row-number difference trick
• Avoid complex self-joins
• Aggregate after forming streak groups

💡 SQL Solution

WITH numbered_logins AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM logins
),
grouped_logins AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM numbered_logins
)

SELECT
user_id
FROM grouped_logins
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

🔥 Why this question is powerful:

• Tests advanced window function usage
• Checks understanding of gaps & islands concept
• Evaluates real-world product analytics thinking
• Very common in growth / engagement analytics interviews

❤️ React if you want more scenario-based SQL questions

🧠 SQL Interview Question (Commonly Asked)
📌

products(product_id, product_name, category_id, price)

❓ Ques :

👉 Find the second highest priced product in each category.

🧩 How Interviewers Expect You to Think

• Partition data by category
• Rank products based on price (descending)
• Understand difference between RANK, DENSE_RANK, and ROW_NUMBER
• Handle ties properly
• Filter after ranking logic

💡 SQL Solution

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
DENSE_RANK() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS price_rank
FROM products
)

SELECT
product_id,
product_name,
category_id,
price
FROM ranked_products
WHERE price_rank = 2;

🔥 Why this question is powerful:

• Tests window functions deeply
• Checks ranking logic understanding
• Very common in Data Analyst interviews

❤️ React if you want more scenario-based SQL questions