Data Analyst Interview Resources

✅ Excel Scenario-Based Questions for Interview & Practice 🧠📊

📌 Scenario 51
Question: You have sales data by employee and need to calculate the total sales for each employee. How would you do it?
Answer: Use a Pivot Table.
Select the dataset → Insert → PivotTable → Drag Employee Name to Rows → Drag Sales to Values.

📊 Scenario 52
Question: You need to extract the first 5 characters from an Order ID. How would you do it?
Answer: Use the LEFT() function.
Example:
=LEFT(A2,5)

📅 Scenario 53
Question: You need to extract the last 4 digits of a Customer ID. Which function would you use?
Answer: Use the RIGHT() function.
Example:
=RIGHT(A2,4)

📈 Scenario 54
Question: You have a column containing full names and need to extract only the first name. How would you do it?
Answer: Use TEXTBEFORE() in newer Excel versions.
Example:
=TEXTBEFORE(A2," ")
This extracts everything before the first space.

🔍 Scenario 55
Question: You need to extract the domain name from an email address such as "employee@company.com". How would you do it?
Answer: Use TEXTAFTER().
Example:
=TEXTAFTER(A2,"@")
This returns company.com.

💬 Double Tap ♥️ For More!

🚀 𝗜𝗕𝗠 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🎓

Upgrade your tech skills with 100% FREE IBM certification courses and build a strong foundation in AI, Data Science, Cloud Computing, SQL, Python, and Machine Learning.

🎯 Perfect For
🎓 Students & Freshers
👨‍💻 Software Developers
📊 Data Analysts
🤖 AI & Data Science Aspirants
💼 Working Professionals

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/45KgqDR

🔥 Start learning today and prepare yourself for high-paying opportunities in the tech industry!

🚀 𝗙𝗥𝗘𝗘 𝗙𝗿𝗲𝘀𝗵𝗲𝗿 𝗛𝗶𝗿𝗶𝗻𝗴 𝗗𝗿𝗶𝘃𝗲 | 𝗧𝗲𝗰𝗵 𝗥𝗼𝗹𝗲𝘀 𝗨𝗽 𝘁𝗼 ₹𝟭𝟮 𝗟𝗣𝗔!🔥

Internship + Pre-Placement Offer

💼 Company: GoComet
💰 Stipend: ₹30,000–35,000/Month
🚀 PPO: Up to ₹12 LPA

📍 Assessment Centres: Pune | Hyderabad | Noida | Chennai | Bangalore

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:

Full Stack Intern:- https://pdlink.in/4z3vF8o

AI First SDET Interns :- https://pdlink.in/4hS1Am2

⏳ Limited Hiring Slots Available

Junior-level Data Analyst interview questions:

Introduction and Background

1. Can you tell me about your background and how you became interested in data analysis?
2. What do you know about our company/organization?
3. Why do you want to work as a data analyst?

Data Analysis and Interpretation

1. What is your experience with data analysis tools like Excel, SQL, or Tableau?
2. How would you approach analyzing a large dataset to identify trends and patterns?
3. Can you explain the concept of correlation versus causation?
4. How do you handle missing or incomplete data?
5. Can you walk me through a time when you had to interpret complex data results?

Technical Skills

1. Write a SQL query to extract data from a database.
2. How do you create a pivot table in Excel?
3. Can you explain the difference between a histogram and a box plot?
4. How do you perform data visualization using Tableau or Power BI?
5. Can you write a simple Python or R script to manipulate data?

Statistics and Math

1. What is the difference between mean, median, and mode?
2. Can you explain the concept of standard deviation and variance?
3. How do you calculate probability and confidence intervals?
4. Can you describe a time when you applied statistical concepts to a real-world problem?
5. How do you approach hypothesis testing?

Communication and Storytelling

1. Can you explain a complex data concept to a non-technical person?
2. How do you present data insights to stakeholders?
3. Can you walk me through a time when you had to communicate data results to a team?
4. How do you create effective data visualizations?
5. Can you tell a story using data?

Case Studies and Scenarios

1. You are given a dataset with customer purchase history. How would you analyze it to identify trends?
2. A company wants to increase sales. How would you use data to inform marketing strategies?
3. You notice a discrepancy in sales data. How would you investigate and resolve the issue?
4. Can you describe a time when you had to work with a stakeholder to understand their data needs?
5. How would you prioritize data projects with limited resources?

Behavioral Questions

1. Can you describe a time when you overcame a difficult data analysis challenge?
2. How do you handle tight deadlines and multiple projects?
3. Can you tell me about a project you worked on and your role in it?
4. How do you stay up-to-date with new data tools and technologies?
5. Can you describe a time when you received feedback on your data analysis work?

Final Questions

1. Do you have any questions about the company or role?
2. What do you think sets you apart from other candidates?
3. Can you summarize your experience and qualifications?
4. What are your long-term career goals?

Hope this helps you 😊

Here are some essential data science concepts from A to Z:

A - Algorithm: A set of rules or instructions used to solve a problem or perform a task in data science.

B - Big Data: Large and complex datasets that cannot be easily processed using traditional data processing applications.

C - Clustering: A technique used to group similar data points together based on certain characteristics.

D - Data Cleaning: The process of identifying and correcting errors or inconsistencies in a dataset.

E - Exploratory Data Analysis (EDA): The process of analyzing and visualizing data to understand its underlying patterns and relationships.

F - Feature Engineering: The process of creating new features or variables from existing data to improve model performance.

G - Gradient Descent: An optimization algorithm used to minimize the error of a model by adjusting its parameters.

H - Hypothesis Testing: A statistical technique used to test the validity of a hypothesis or claim based on sample data.

I - Imputation: The process of filling in missing values in a dataset using statistical methods.

J - Joint Probability: The probability of two or more events occurring together.

K - K-Means Clustering: A popular clustering algorithm that partitions data into K clusters based on similarity.

L - Linear Regression: A statistical method used to model the relationship between a dependent variable and one or more independent variables.

M - Machine Learning: A subset of artificial intelligence that uses algorithms to learn patterns and make predictions from data.

N - Normal Distribution: A symmetrical bell-shaped distribution that is commonly used in statistical analysis.

O - Outlier Detection: The process of identifying and removing data points that are significantly different from the rest of the dataset.

P - Precision and Recall: Evaluation metrics used to assess the performance of classification models.

Q - Quantitative Analysis: The process of analyzing numerical data to draw conclusions and make decisions.

R - Random Forest: An ensemble learning algorithm that builds multiple decision trees to improve prediction accuracy.

S - Support Vector Machine (SVM): A supervised learning algorithm used for classification and regression tasks.

T - Time Series Analysis: A statistical technique used to analyze and forecast time-dependent data.

U - Unsupervised Learning: A type of machine learning where the model learns patterns and relationships in data without labeled outputs.

V - Validation Set: A subset of data used to evaluate the performance of a model during training.

W - Web Scraping: The process of extracting data from websites for analysis and visualization.

X - XGBoost: An optimized gradient boosting algorithm that is widely used in machine learning competitions.

Y - Yield Curve Analysis: The study of the relationship between interest rates and the maturity of fixed-income securities.

Z - Z-Score: A standardized score that represents the number of standard deviations a data point is from the mean.

Credits: https://t.me/free4unow_backup

Like if you need similar content 😄👍

✅ Excel Scenario-Based Questions for Interview & Practice 🧠📊

📌 Scenario 46
Question: Your sales dataset contains blank cells. Instead of displaying "0", you want the result to appear blank. How would you do it?
Answer: Use the IF() function.
Example:
=IF(A2="","",A2*10)
This returns a blank if A2 is empty; otherwise, it performs the calculation.

📊 Scenario 47
Question: Your manager wants to know how many unique customers made purchases this month. How do you calculate it?
Answer: Excel 365: Use the UNIQUE() function with COUNTA().
Example:
=COUNTA(UNIQUE(A2:A1000))
This returns the count of distinct customers.

📅 Scenario 48
Question: You have sales data in separate worksheets for each month. How do you calculate the total annual sales?
Answer: Use a 3D Reference.
Example:
=SUM(Jan:Dec!B2)
This adds the value in cell B2 across all worksheets from Jan to Dec.

📈 Scenario 49
Question: Your manager wants to identify all transactions above the average sales value. How would you do it?
Answer: Use the AVERAGE() and IF() functions.
Example:
=IF(B2>AVERAGE(B2:B100),"Above Average","Below Average")

🔍 Scenario 50
Question: You need to replace all occurrences of "N/A" with "Not Available" throughout the worksheet. What's the fastest way?
Answer: Use Find & Replace.
Press Ctrl + H → Find what: "N/A" → Replace with: "Not Available" → Click Replace All.

💬 Double Tap ♥️ For More!

✅ Excel Scenario-Based Questions for Interview & Practice 🧠📊

📌 Scenario 41 
Question: Your manager wants to retrieve the sales amount for a specific Order ID entered in a search box. How would you do it? 
Answer: Use "XLOOKUP()" (or "INDEX" + "MATCH" in older versions). 
Example: 
=XLOOKUP(E2,A:A,B:B,"Order Not Found") 
Where "E2" contains the Order ID, "A:A" is the Order ID column, and "B:B" is the Sales column.

📊 Scenario 42 
Question: You have a large dataset and need to allow users to select a department from a dropdown list. How do you do it? 
Answer: Use Data Validation. 
Go to Data → Data Validation → List → Select the range containing department names. This creates a dropdown list.

📅 Scenario 43 
Question: You need to calculate the number of days remaining until a project's deadline. How would you do it? 
Answer: Use the formula: 
=Deadline_Date-TODAY() 
Example: =B2-TODAY() 
This returns the number of days left until the deadline.

📈 Scenario 44 
Question: Your manager wants to rank employees based on their sales performance. How do you do it? 
Answer: Use the "RANK.EQ()" function. 
Example: 
=RANK.EQ(B2,B2:B100,0) 
This ranks employees from highest to lowest sales.

🔍 Scenario 45 
Question: You need to display "Invalid" if a sales value is negative; otherwise, display the sales amount. How would you do it? 
Answer: Use the "IF()" function. 
Example: 
=IF(B2<0,"Invalid",B2) 
This flags negative values while keeping valid sales amounts unchanged.

💬 Double Tap ♥️ For More!

✅ Excel Scenario-Based Questions for Interview & Practice 🧠📊

📌 Scenario 36
Question: Your manager asks you to return the second highest sales value in the dataset. How would you do it?
Answer: Use the "LARGE()" function.
Example:
=LARGE(B:B,2)
This returns the second highest sales value.

📊 Scenario 37
Question: You need to highlight all duplicate Employee IDs automatically whenever new data is added. How do you do it?
Answer: Use Conditional Formatting → Highlight Cells Rules → Duplicate Values.
Any duplicate Employee ID will be highlighted automatically.

📅 Scenario 38
Question: You want to calculate the age of an employee from their Date of Birth. How would you do it?
Answer: Use the "DATEDIF()" function.
Example:
=DATEDIF(A2,TODAY(),"Y")
This returns the employee's age in years.

📈 Scenario 39
Question: Your manager wants to know whether each employee has achieved the sales target of ₹75,000. How do you display the result?
Answer: Use the "IF()" function.
Example:
=IF(B2>=75000,"Achieved","Not Achieved")

🔍 Scenario 40
Question: You need to display today's date automatically whenever the workbook is opened. How do you do it?
Answer: Use the "TODAY()" function.
Example:
=TODAY()
It automatically updates to the current date whenever the workbook is recalculated.

💬 Double Tap ♥️ For More!

Preparing for a SQL interview?

Focus on mastering these essential topics:

1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!

2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.

3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.

4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.

5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.

6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.

7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.

8. Indexing: Understand how proper indexing can significantly boost query performance.

9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.

10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.

11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.

12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this SQL query.

Find the second highest salary in each department from the employees table, excluding any department with fewer than 2 employees.

𝗠𝗲: Challenge accepted!

SELECT 
    department, 
    MAX(salary) AS second_highest_salary
FROM (
    SELECT 
        department, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
) ranked
WHERE rn = 2
GROUP BY department;

I used a subquery with ROW_NUMBER() window function partitioned by department to rank salaries in descending order within each department. The outer query then filters for rank 2 (second highest) and groups to get distinct departments. This demonstrates mastery of window functions, which are essential for advanced analytics and ranking problems.

𝗧𝗶𝗽 𝗳𝗼𝗿 𝗦𝗤𝗟 𝗝𝗼𝗯 𝗦𝗲𝗲𝗸𝗲𝗿𝘀:
Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() unlock complex ranking and analytics—practice them daily to ace behavioral and technical rounds!

React with ❤️ for more

✅ Data Analytics Roadmap for Freshers 🚀📊

1️⃣ Understand What a Data Analyst Does
🔍 Analyze data, find insights, create dashboards, support business decisions.

2️⃣ Start with Excel
📈 Learn:
– Basic formulas
– Charts & Pivot Tables
– Data cleaning
💡 Excel is still the #1 tool in many companies.

3️⃣ Learn SQL
🧩 SQL helps you pull and analyze data from databases.
Start with:
– SELECT, WHERE, JOIN, GROUP BY
🛠️ Practice on platforms like W3Schools or Mode Analytics.

4️⃣ Pick a Programming Language
🐍 Start with Python (easier) or R
– Learn pandas, matplotlib, numpy
– Do small projects (e.g. analyze sales data)

5️⃣ Data Visualization Tools
📊 Learn:
– Power BI or Tableau
– Build simple dashboards
💡 Start with free versions or YouTube tutorials.

6️⃣ Practice with Real Data
🔍 Use sites like Kaggle or Data.gov
– Clean, analyze, visualize
– Try small case studies (sales report, customer trends)

7️⃣ Create a Portfolio
💻 Share projects on:
– GitHub
– Notion or a simple website
📌 Add visuals + brief explanations of your insights.

8️⃣ Improve Soft Skills
🗣️ Focus on:
– Presenting data in simple words
– Asking good questions
– Thinking critically about patterns

9️⃣ Certifications to Stand Out
🎓 Try:
– Google Data Analytics (Coursera)
– IBM Data Analyst
– LinkedIn Learning basics

🔟 Apply for Internships & Entry Jobs
🎯 Titles to look for:
– Data Analyst (Intern)
– Junior Analyst
– Business Analyst

💬 React ❤️ for more!

📊 Complete Roadmap to Become a Power BI Expert

📂 1. Understand Basics of Data & BI
– What is Business Intelligence?
– Importance of data visualization

📂 2. Learn Power BI Interface
– Power BI Desktop overview
– Power Query Editor basics

📂 3. Connect to Data Sources
– Excel, SQL Server, SharePoint, APIs, CSV, etc.

📂 4. Data Transformation & Cleaning
– Use Power Query to shape, clean, and prepare data

📂 5. Learn Data Modeling
– Create relationships between tables
– Understand star schema & normalization basics

📂 6. Master DAX (Data Analysis Expressions)
– Calculated columns, measures, time intelligence functions

📂 7. Create Interactive Visualizations
– Charts, slicers, maps, tables, and custom visuals

📂 8. Build Dashboards & Reports
– Combine visuals for insightful dashboards
– Use bookmarks, drill-throughs, tooltips

📂 9. Publish & Share Reports
– Power BI Service basics
– Sharing, workspaces, and app creation

📂 10. Learn Power BI Administration
– Row-level security (RLS)
– Gateway setup & scheduled refresh

📂 11. Practice Real-World Projects
– Sales dashboards, financial reports, customer insights

👍 Like for more!

🎯 SQL Interview Pattern: Running Total (Cumulative Sum)

Master this one pattern, and you'll be able to solve questions like:

📈 Running Total of Sales
💰 Cumulative Revenue
🛒 Customer Lifetime Spend
📦 Running Inventory Balance
👥 Cumulative User Sign-ups
💳 Account Balance After Each Transaction
📊 Cumulative Monthly Profit

🧠 How to Identify This Pattern

If the question contains words like:

• Running Total
• Cumulative
• Till Date
• So Far
• Progressive Sum
• Rolling Balance

👉 Think: Window Function (SUM() OVER())

✅ Approach

1️⃣ Identify the value to accumulate.
2️⃣ Find the correct ordering column (usually Date or ID).
3️⃣ Use:

SUM(column_name) OVER (
ORDER BY column_name
)

💡 Bonus Tip:

Need a separate running total for each customer, product, or department?

➡️ Add PARTITION BY before ORDER BY.

❤️ Like this post? React with a ❤️ if you'd like more SQL interview patterns!

✅ 🔤 A–Z of SQL Commands 🗄️💻⚡

A – ALTER
Modify an existing table structure (add/modify/drop columns).

B – BEGIN
Start a transaction block.

C – CREATE
Create database objects like tables, views, indexes.

D – DELETE
Remove records from a table.

E – EXISTS
Check if a subquery returns any rows.

F – FETCH
Retrieve rows from a cursor.

G – GRANT
Give privileges to users.

H – HAVING
Filter aggregated results (used with GROUP BY).

I – INSERT
Add new records into a table.

J – JOIN
Combine rows from two or more tables.

K – KEY (PRIMARY KEY / FOREIGN KEY)
Define constraints for uniqueness and relationships.

L – LIMIT
Restrict number of rows returned (MySQL/PostgreSQL).

M – MERGE
Insert/update data conditionally (mainly in SQL Server/Oracle).

N – NULL
Represents missing or unknown data.

O – ORDER BY
Sort query results.

P – PROCEDURE
Stored program in the database.

Q – QUERY
Request for data (general SQL statement).

R – ROLLBACK
Undo changes in a transaction.

S – SELECT
Retrieve data from tables.

T – TRUNCATE
Remove all records from a table quickly.

U – UPDATE
Modify existing records.

V – VIEW
Virtual table based on a query.

W – WHERE
Filter records based on conditions.

X – XML PATH
Generate XML output (mainly SQL Server).

Y – YEAR()
Extract year from a date.

Z – ZONE (AT TIME ZONE)
Convert datetime to specific time zone.

❤️ Double Tap for More

Complete Excel Topics for Data Analysts 😄👇

MS Excel Free Resources
-> https://t.me/excel_data

1. Introduction to Excel:
- Basic spreadsheet navigation
- Understanding cells, rows, and columns

2. Data Entry and Formatting:
- Entering and formatting data
- Cell styles and formatting options

3. Formulas and Functions:
- Basic arithmetic functions
- SUM, AVERAGE, COUNT functions

4. Data Cleaning and Validation:
- Removing duplicates
- Data validation techniques

5. Sorting and Filtering:
- Sorting data
- Using filters for data analysis

6. Charts and Graphs:
- Creating basic charts (bar, line, pie)
- Customizing and formatting charts

7. PivotTables and PivotCharts:
- Creating PivotTables
- Analyzing data with PivotCharts

8. Advanced Formulas:
- VLOOKUP, HLOOKUP, INDEX-MATCH
- IF statements for conditional logic

9. Data Analysis with What-If Analysis:
- Goal Seek
- Scenario Manager and Data Tables

10. Advanced Charting Techniques:
- Combination charts
- Dynamic charts with named ranges

11. Power Query:
- Importing and transforming data with Power Query

12. Data Visualization with Power BI:
- Connecting Excel to Power BI
- Creating interactive dashboards

13. Macros and Automation:
- Recording and running macros
- Automation with VBA (Visual Basic for Applications)

14. Advanced Data Analysis:
- Regression analysis
- Data forecasting with Excel

15. Collaboration and Sharing:
- Excel sharing options
- Collaborative editing and comments

16. Excel Shortcuts and Productivity Tips:
- Time-saving keyboard shortcuts
- Productivity tips for efficient work

17. Data Import and Export:
- Importing and exporting data to/from Excel

18. Data Security and Protection:
- Password protection
- Worksheet and workbook security

19. Excel Add-Ins:
- Using and installing Excel add-ins for extended functionality

20. Mastering Excel for Data Analysis:
- Comprehensive project or case study integrating various Excel skills

Since Excel is another essential skill for data analysts, I have decided to teach each topic daily in this channel for free. Like this post if you want me to continue this Excel series 👍♥️

Share with credits: https://t.me/sqlspecialist

Hope it helps :)

🚀 𝗖𝗶𝘀𝗰𝗼 𝗙𝗥𝗘𝗘 𝗧𝗲𝗰𝗵 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝟱 𝗠𝘂𝘀𝘁-𝗗𝗼 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🎓

Cisco offers learning opportunities covering some of the most valuable foundations for careers in Cybersecurity, Networking, Linux and IoT.

✅ Beginner-Friendly Tech Skills
✅ Learn In-Demand IT Concepts
✅ Build Practical Knowledge
✅ Strengthen Your Resume
✅ Great for Students & Freshers

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4fhCSKo

🔥 Learn from Cisco • Build Skills • Upgrade Your Resume • Get Career-Ready!

🔍 Best Data Analytics Roles Based on Your Graduation Background!

Thinking about a career in Data Analytics but unsure which role fits your background? Check out these top job roles based on your degree:

🚀 For Mathematics/Statistics Graduates:
🔹 Data Analyst
🔹 Statistical Analyst
🔹 Quantitative Analyst
🔹 Risk Analyst

🚀 For Computer Science/IT Graduates:
🔹 Data Scientist
🔹 Business Intelligence Developer
🔹 Data Engineer
🔹 Data Architect

🚀 For Economics/Finance Graduates:
🔹 Financial Analyst
🔹 Market Research Analyst
🔹 Economic Consultant
🔹 Data Journalist

🚀 For Business/Management Graduates:
🔹 Business Analyst
🔹 Operations Research Analyst
🔹 Marketing Analytics Manager
🔹 Supply Chain Analyst

🚀 For Engineering Graduates:
🔹 Data Scientist
🔹 Industrial Engineer
🔹 Operations Research Analyst
🔹 Quality Engineer

🚀 For Social Science Graduates:
🔹 Data Analyst
🔹 Research Assistant
🔹 Social Media Analyst
🔹 Public Health Analyst

🚀 For Biology/Healthcare Graduates:
🔹 Clinical Data Analyst
🔹 Biostatistician
🔹 Research Coordinator
🔹 Healthcare Consultant

✅ Pro Tip:

Some of these roles may require additional certifications or upskilling in SQL, Python, Power BI, Tableau, or Machine Learning to stand out in the job market.

Like if it helps ❤️

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find employees who earn the same salary as at least one other employee in the same department.

𝗠𝗲: Challenge accepted! 💪
SELECT
employee_id,
employee_name,
department,
salary
FROM employees
WHERE (department, salary) IN (
SELECT
department,
salary
FROM employees
GROUP BY department, salary
HAVING COUNT(*) > 1
)
ORDER BY department, salary DESC;

💡 Explanation:
The query identifies duplicate salary values within each department.

• The subquery groups records by department and salary.
• **HAVING COUNT(*) > 1** finds salary values that appear more than once in the same department.
• The outer query returns all employees whose (department, salary) matches those duplicate combinations.

This question tests your understanding of:
✅ GROUP BY
✅ HAVING
✅ Multi-column filtering
✅ Identifying duplicate records

🎯 Expected Output Example
Employee Department Salary
John IT 80,000
Alice IT 80,000
David HR 65,000
Sarah HR 65,000

🚀 Alternative Using Window Functions
SELECT
employee_id,
employee_name,
department,
salary
FROM (
SELECT
*,
COUNT(*) OVER (
PARTITION BY department, salary
) AS salary_count
FROM employees
) t
WHERE salary_count > 1;

This approach avoids a subquery with GROUP BY and is a great way to showcase your knowledge of window functions.

🚀 When interview questions ask you to find duplicates, think of these three approaches:

1. GROUP BY + HAVING
2. Window functions COUNT() OVER
3. Self Join for specific comparison scenarios

Knowing multiple solutions demonstrates strong SQL problem-solving skills.

❤️ React with ❤️ for more SQL interview challenges!

🔥 SQL Interview Case Studies & Real-World Business Problems

🧠 Case Study 1: Top 3 Customers by Revenue
📊 Orders Table
order_id customer_id amount
1 101 500
2 102 1000
3 101 700

❓ Business Question
Find the top 3 customers by total revenue.

✅ Solution
SELECT customer_id,
SUM(amount) AS total_revenue
FROM orders
GROUP BY customer_id
ORDER BY total_revenue DESC
LIMIT 3;

🧠 Case Study 2: Department with Highest Average Salary

❓ Business Question
Which department has the highest average salary?

✅ Solution
SELECT department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

🧠 Case Study 3: Customers Who Never Ordered
📊 Tables
Customers customer_id name
Orders order_id customer_id

❓ Business Question
Find customers who never placed an order.

✅ Solution
SELECT c.customer_id,
c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

🧠 Case Study 4: Second Highest Salary

❓ Business Question
Find employees with the second highest salary.

✅ Solution
SELECT *
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
);

🧠 Case Study 5: Monthly Sales Trend

❓ Business Question
Calculate monthly sales.

✅ Solution
SELECT YEAR(order_date) AS year,
MONTH(order_date) AS month,
SUM(amount) AS sales
FROM orders
GROUP BY YEAR(order_date),
MONTH(order_date)
ORDER BY year, month;

🎯 Practice Tasks
1️⃣ Find top-selling product
2️⃣ Find employee with highest salary in each department
3️⃣ Find customers with more than 5 orders
4️⃣ Find month with highest sales
5️⃣ Find departments having more than 10 employees

⚡ Mini Challenge 🔥
E-commerce Scenario

Tables:
Customers customer_id name
Orders order_id customer_id amount order_date

Business Question
Find the top 5 customers by total spending in the last 12 months.

🔥 Interview Tip
Most SQL interviews are NOT about syntax.

They're about:
✅ Understanding business problem
✅ Choosing the right approach
✅ Writing efficient SQL

Double Tap ❤️ For More

𝗔𝗜 & 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 (𝗡𝗼 𝗖𝗼𝗱𝗶𝗻𝗴 𝗡𝗲𝗲𝗱𝗲𝗱)

Apply Now👉:- https://pdlink.in/4aYWald

By E&ICT Academy, IIT Roorkee

Batch Closing Soon - 18th July 2026