Q5. Find customers whose total sales exceed โน50,000.
๐ Double Tap โค๏ธ For More
SELECT c.Customer_Name, SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Customer_Name
HAVING SUM(o.Sales) > 50000;
๐ Double Tap โค๏ธ For More
โค9
๐ Data Analyst Roadmap โ Part 15
๐๏ธ SQL โ Level 5: Subqueries, CTEs & Derived Tables
You've now learned how to retrieve, filter, aggregate, categorize, and join data.
The next step is learning how to break complex SQL problems into smaller, manageable steps.
The three important concepts in this part are:
โข Subqueries
โข CTEs (Common Table Expressions)
โข Derived Tables
These are heavily used in real-world SQL analysis and interviews.
1๏ธโฃ What Is a Subquery?
A subquery is a SQL query inside another SQL query.
Think of it as:
For example, suppose you want to find employees earning more than the average salary.
First, calculate the average:
Then use that result to filter employees:
The query inside the parentheses is the subquery.
2๏ธโฃ Why Use Subqueries?
Without a subquery, you might have to calculate the average separately and manually enter it.
With a subquery, SQL calculates it dynamically.
This is useful for questions such as: Employees earning above average, Products selling above average, Customers spending more than average, Orders larger than the overall average, Finding records based on another query's result
3๏ธโฃ How a Subquery Works
Consider:
Conceptually: Subquery โ Calculate average salary โ Average Salary โ Main Query โ Find employees above average
The inner query provides a value that the outer query uses.
4๏ธโฃ Scalar Subquery
A scalar subquery returns a single value.
For example:
returns one value.
You can use it like:
Now every employee can be compared against the overall average.
5๏ธโฃ Subquery with IN
A subquery doesn't always return one value. It can return a list.
Suppose you want customers who have placed at least one order.
The inner query returns a list of customer IDs. The outer query retrieves matching customers.
6๏ธโฃ NOT IN
You can also find records that aren't present in another query.
For example:
However, be careful when using NOT IN if the subquery can contain NULL, because NULL semantics can produce unexpected results.
For anti-matching logic, NOT EXISTS or a properly structured LEFT JOIN ... IS NULL is often safer.
7๏ธโฃ EXISTS
EXISTS checks whether a matching row exists.
For example:
This means:
You don't need the subquery to return the actual order details. You're simply checking whether a match exists.
8๏ธโฃ NOT EXISTS
NOT EXISTS does the opposite.
๐๏ธ SQL โ Level 5: Subqueries, CTEs & Derived Tables
You've now learned how to retrieve, filter, aggregate, categorize, and join data.
The next step is learning how to break complex SQL problems into smaller, manageable steps.
The three important concepts in this part are:
โข Subqueries
โข CTEs (Common Table Expressions)
โข Derived Tables
These are heavily used in real-world SQL analysis and interviews.
1๏ธโฃ What Is a Subquery?
A subquery is a SQL query inside another SQL query.
Think of it as:
First solve one problem โ then use that result to solve another problem.
For example, suppose you want to find employees earning more than the average salary.
First, calculate the average:
SELECT AVG(Salary)
FROM Employees;
Then use that result to filter employees:
SELECT
Name,
Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
The query inside the parentheses is the subquery.
2๏ธโฃ Why Use Subqueries?
Without a subquery, you might have to calculate the average separately and manually enter it.
With a subquery, SQL calculates it dynamically.
This is useful for questions such as: Employees earning above average, Products selling above average, Customers spending more than average, Orders larger than the overall average, Finding records based on another query's result
3๏ธโฃ How a Subquery Works
Consider:
SELECT
Name,
Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
Conceptually: Subquery โ Calculate average salary โ Average Salary โ Main Query โ Find employees above average
The inner query provides a value that the outer query uses.
4๏ธโฃ Scalar Subquery
A scalar subquery returns a single value.
For example:
SELECT AVG(Salary)
FROM Employees;
returns one value.
You can use it like:
SELECT
Name,
Salary,
Salary - (
SELECT AVG(Salary)
FROM Employees
) AS Difference_From_Average
FROM Employees;
Now every employee can be compared against the overall average.
5๏ธโฃ Subquery with IN
A subquery doesn't always return one value. It can return a list.
Suppose you want customers who have placed at least one order.
SELECT
Customer_ID,
Customer_Name
FROM Customers
WHERE Customer_ID IN (
SELECT Customer_ID
FROM Orders
);
The inner query returns a list of customer IDs. The outer query retrieves matching customers.
6๏ธโฃ NOT IN
You can also find records that aren't present in another query.
For example:
Find customers who have never placed an order.
SELECT
Customer_ID,
Customer_Name
FROM Customers
WHERE Customer_ID NOT IN (
SELECT Customer_ID
FROM Orders
);
However, be careful when using NOT IN if the subquery can contain NULL, because NULL semantics can produce unexpected results.
For anti-matching logic, NOT EXISTS or a properly structured LEFT JOIN ... IS NULL is often safer.
7๏ธโฃ EXISTS
EXISTS checks whether a matching row exists.
For example:
SELECT
c.Customer_ID,
c.Customer_Name
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.Customer_ID = c.Customer_ID
);
This means:
Return customers for whom at least one matching order exists.
You don't need the subquery to return the actual order details. You're simply checking whether a match exists.
8๏ธโฃ NOT EXISTS
NOT EXISTS does the opposite.
โค1๐1
The CTE is often easier to read when the query becomes complex.
A good rule:
1๏ธโฃ7๏ธโฃ CTE vs Derived Table
Conceptually, both can create an intermediate result.
Derived Table: Usually appears inside:
CTE: Defined before the main query:
CTEs generally make multi-step analytical queries easier to organize.
1๏ธโฃ8๏ธโฃ CTE for Data Filtering
Suppose you only want 2026 orders.
This makes the query's logic easy to follow:
First โ select 2026, Then โ analyze by region
1๏ธโฃ9๏ธโฃ CTE for Business Logic
Suppose you want to classify orders:
Now you've separated: Classification from: Aggregation. This is much easier to maintain.
2๏ธโฃ0๏ธโฃ CTE for Multi-Step Analysis
Imagine the business asks:
A CTE can break this into understandable stages.
For example:
This is much easier to reason about than attempting everything at once.
2๏ธโฃ1๏ธโฃ CTEs Are Not Permanent Tables
This is important.
A normal table: Customers, Orders, Products is stored in the database.
A CTE:
2๏ธโฃ2๏ธโฃ CTEs and Performance
A common misconception is:
That's not necessarily true.
A CTE is primarily a query organization/readability tool.
Actual performance depends on: Database engine, Query structure, Indexes, Data volume, Optimizer behavior, Joins, Aggregations
So don't use a CTE simply because you think it automatically makes a query faster. Use it when it makes the logic clearer or otherwise fits your query design.
2๏ธโฃ3๏ธโฃ Correlated Subquery
A correlated subquery references a column from the outer query.
Example:
A good rule:
Simple calculation โ subquery can be fine.
Multiple logical steps โ CTE is often clearer.
1๏ธโฃ7๏ธโฃ CTE vs Derived Table
Conceptually, both can create an intermediate result.
Derived Table: Usually appears inside:
FROM (...)CTE: Defined before the main query:
WITH Name AS (...)CTEs generally make multi-step analytical queries easier to organize.
1๏ธโฃ8๏ธโฃ CTE for Data Filtering
Suppose you only want 2026 orders.
WITH Orders_2026 AS (
SELECT *
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01'
)
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders_2026
GROUP BY Region;
This makes the query's logic easy to follow:
First โ select 2026, Then โ analyze by region
1๏ธโฃ9๏ธโฃ CTE for Business Logic
Suppose you want to classify orders:
WITH Classified_Orders AS (
SELECT
Order_ID,
Sales,
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category
FROM Orders
)
SELECT
Sales_Category,
COUNT(*) AS Order_Count
FROM Classified_Orders
GROUP BY Sales_Category;
Now you've separated: Classification from: Aggregation. This is much easier to maintain.
2๏ธโฃ0๏ธโฃ CTE for Multi-Step Analysis
Imagine the business asks:
Which region has the highest average customer sales?
A CTE can break this into understandable stages.
For example:
WITH Customer_Sales AS (
SELECT
Customer_ID,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
),
Regional_Customer_Sales AS (
SELECT
c.Region,
cs.Customer_ID,
cs.Total_Sales
FROM Customer_Sales cs
JOIN Customers c
ON cs.Customer_ID = c.Customer_ID
)
SELECT
Region,
AVG(Total_Sales) AS Avg_Customer_Sales
FROM Regional_Customer_Sales
GROUP BY Region
ORDER BY Avg_Customer_Sales DESC;
This is much easier to reason about than attempting everything at once.
2๏ธโฃ1๏ธโฃ CTEs Are Not Permanent Tables
This is important.
A normal table: Customers, Orders, Products is stored in the database.
A CTE:
WITH Customer_Sales AS (...) exists only for the duration of that query.2๏ธโฃ2๏ธโฃ CTEs and Performance
A common misconception is:
"CTEs are always faster than subqueries."
That's not necessarily true.
A CTE is primarily a query organization/readability tool.
Actual performance depends on: Database engine, Query structure, Indexes, Data volume, Optimizer behavior, Joins, Aggregations
So don't use a CTE simply because you think it automatically makes a query faster. Use it when it makes the logic clearer or otherwise fits your query design.
2๏ธโฃ3๏ธโฃ Correlated Subquery
A correlated subquery references a column from the outer query.
Example:
SELECT
e.Name,
e.Salary
FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.Department = e.Department
);
โค1
This asks:
The inner query depends on the current employee's department. This is more advanced than a basic subquery.
2๏ธโฃ4๏ธโฃ Why Correlated Subqueries Matter
Suppose: IT Average salary = โน80,000, HR Average salary = โน60,000
An employee earning โน75,000: Could be below IT average, Could be above HR average
So comparing everyone to the overall company average isn't enough. A correlated subquery allows you to compare each employee to the relevant group.
2๏ธโฃ5๏ธโฃ Subquery vs JOIN
Sometimes the same problem can be solved using either a subquery or a JOIN.
For example, finding customers with orders can be done with:
Neither is universally better.
The right choice depends on: What result you need, Whether duplicates matter, Query readability, Database optimizer, Data structure
Focus on understanding the logic rather than memorizing one preferred method.
2๏ธโฃ6๏ธโฃ A Very Common Interview Problem
Question:
A correlated subquery solution:
This is an excellent interview question because it tests: Subqueries, Aggregation, Correlation, Business logic
2๏ธโฃ7๏ธโฃ Another Interview Problem
Question:
Using a derived table:
Or using a CTE:
Both approaches produce the same analytical idea.
๐งช Practical Interview Challenge
Suppose you have Employees table
Q1. Find employees earning above the overall average.
Q2. Find employees earning above their department average.
Q3. Create a CTE containing average salary by department.
Q4. Find departments whose average salary exceeds โน70,000.
Q5. Find customers who have placed at least one order.
๐ Double Tap โค๏ธ For More
Which employees earn more than the average salary of their own department?
The inner query depends on the current employee's department. This is more advanced than a basic subquery.
2๏ธโฃ4๏ธโฃ Why Correlated Subqueries Matter
Suppose: IT Average salary = โน80,000, HR Average salary = โน60,000
An employee earning โน75,000: Could be below IT average, Could be above HR average
So comparing everyone to the overall company average isn't enough. A correlated subquery allows you to compare each employee to the relevant group.
2๏ธโฃ5๏ธโฃ Subquery vs JOIN
Sometimes the same problem can be solved using either a subquery or a JOIN.
For example, finding customers with orders can be done with:
WHERE EXISTS (...) or: JOIN Orders ...Neither is universally better.
The right choice depends on: What result you need, Whether duplicates matter, Query readability, Database optimizer, Data structure
Focus on understanding the logic rather than memorizing one preferred method.
2๏ธโฃ6๏ธโฃ A Very Common Interview Problem
Question:
Find employees earning more than their department's average salary.
A correlated subquery solution:
SELECT
e.Name,
e.Department,
e.Salary
FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.Department = e.Department
);
This is an excellent interview question because it tests: Subqueries, Aggregation, Correlation, Business logic
2๏ธโฃ7๏ธโฃ Another Interview Problem
Question:
Find customers whose total sales are greater than โน1,00,000.
Using a derived table:
SELECT
Customer_ID,
Total_Sales
FROM (
SELECT
Customer_ID,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
) AS Customer_Sales
WHERE Total_Sales > 100000;
Or using a CTE:
WITH Customer_Sales AS (
SELECT
Customer_ID,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
)
SELECT
Customer_ID,
Total_Sales
FROM Customer_Sales
WHERE Total_Sales > 100000;
Both approaches produce the same analytical idea.
๐งช Practical Interview Challenge
Suppose you have Employees table
Q1. Find employees earning above the overall average.
SELECT
Name,
Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
Q2. Find employees earning above their department average.
SELECT
e.Name,
e.Department,
e.Salary
FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.Department = e.Department
);
Q3. Create a CTE containing average salary by department.
WITH Department_Salary AS (
SELECT
Department,
AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department
)
SELECT *
FROM Department_Salary;
Q4. Find departments whose average salary exceeds โน70,000.
WITH Department_Salary AS (
SELECT
Department,
AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department
)
SELECT
Department,
Average_Salary
FROM Department_Salary
WHERE Average_Salary > 70000;
Q5. Find customers who have placed at least one order.
SELECT
c.Customer_ID,
c.Customer_Name
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.Customer_ID = c.Customer_ID
);
๐ Double Tap โค๏ธ For More
โค6
๐ ๐ ๐ฎ๐๐๐ฒ๐ฟ ๐๐
๐ฐ๐ฒ๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐ | ๐ฑ ๐ฃ๐ผ๐๐ฒ๐ฟ๐ณ๐๐น ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
๐ฅ Top 5 FREE Excel Courses:
1๏ธโฃ Goldman Sachs โ Excel Skills for Business
2๏ธโฃ PwC โ Problem Solving with Excel
3๏ธโฃ Corporate Finance Institute โ Excel Fundamentals
4๏ธโฃ Great Learning โ Excel for Beginners
5๏ธโฃ Simplilearn โ Introduction to MS Excel
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlink.in/3UQ8S09
๐ Learn Excel for FREE and upgrade your career skills!
๐ฅ Top 5 FREE Excel Courses:
1๏ธโฃ Goldman Sachs โ Excel Skills for Business
2๏ธโฃ PwC โ Problem Solving with Excel
3๏ธโฃ Corporate Finance Institute โ Excel Fundamentals
4๏ธโฃ Great Learning โ Excel for Beginners
5๏ธโฃ Simplilearn โ Introduction to MS Excel
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlink.in/3UQ8S09
๐ Learn Excel for FREE and upgrade your career skills!
โค3
Scenario based Interview Questions & Answers for Data Analyst
1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
Question:
- Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
SELECT CustomerID, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CustomerID;
2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
Question:
- Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
SELECT Name
FROM Employees
WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;
Power BI Scenario-Based Questions
1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
Expected Answer:
- Load the dataset into Power BI.
- Create relationships if necessary.
- Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
- Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
- Use the "Filters" pane to filter data as needed.
- Format the visualization to enhance clarity and readability.
2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
Expected Answer:
- Use Power BI Desktop to connect to the API.
- Go to "Get Data" > "Web" and enter the API URL.
- Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
- Create visualizations using the imported data.
- Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.
3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
Expected Answer:
- Analyze the current performance using Performance Analyzer.
- Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
- Use aggregated tables to pre-compute results.
- Simplify DAX calculations.
- Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
- Ensure proper indexing on the data source.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like if you need more similar content
Hope it helps :)
1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
Question:
- Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
SELECT CustomerID, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CustomerID;
2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
Question:
- Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
SELECT Name
FROM Employees
WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;
Power BI Scenario-Based Questions
1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
Expected Answer:
- Load the dataset into Power BI.
- Create relationships if necessary.
- Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
- Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
- Use the "Filters" pane to filter data as needed.
- Format the visualization to enhance clarity and readability.
2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
Expected Answer:
- Use Power BI Desktop to connect to the API.
- Go to "Get Data" > "Web" and enter the API URL.
- Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
- Create visualizations using the imported data.
- Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.
3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
Expected Answer:
- Analyze the current performance using Performance Analyzer.
- Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
- Use aggregated tables to pre-compute results.
- Simplify DAX calculations.
- Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
- Ensure proper indexing on the data source.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like if you need more similar content
Hope it helps :)
โค6
๐ ๐๐ฒ๐๐ฒ๐น ๐จ๐ฝ ๐ฌ๐ผ๐๐ฟ ๐๐ฎ๐ฟ๐ฒ๐ฒ๐ฟ ๐๐ถ๐๐ต ๐๐ฅ๐๐ ๐ ๐ถ๐ฐ๐ฟ๐ผ๐๐ผ๐ณ๐ ๐๐ฒ๐ฎ๐ฟ๐ป๐ถ๐ป๐ด! ๐ป
Microsoft-focused learning paths can help you strengthen your resume and prepare for in-demand tech and data roles.
๐ฅ Top 5 Courses / Certification Paths:
โ Beginner-friendly options
โ Build practical, job-ready skills
โ Learn Azure, Power BI, Excel & SQL
โ Strengthen your resume & career profile
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlink.in/3UNpPs7
๐ซPerfect for students, freshers, data analysts and professionals looking to upgrade their skills.
Microsoft-focused learning paths can help you strengthen your resume and prepare for in-demand tech and data roles.
๐ฅ Top 5 Courses / Certification Paths:
โ Beginner-friendly options
โ Build practical, job-ready skills
โ Learn Azure, Power BI, Excel & SQL
โ Strengthen your resume & career profile
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlink.in/3UNpPs7
๐ซPerfect for students, freshers, data analysts and professionals looking to upgrade their skills.
โค5
๐ ๐ง๐ผ๐ฝ ๐๐ผ๐บ๐ฝ๐ฎ๐ป๐ถ๐ฒ๐ ๐ข๐ณ๐ณ๐ฒ๐ฟ๐ถ๐ป๐ด ๐๐ฅ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
Learn in-demand skills โข Add valuable credentials to your resume
๐ข TATA :- https://pdlink.in/3QiwLvx
๐ป Infosys :- https://pdlink.in/4eBH3Aa
โก IBM :- https://pdlink.in/45KgqDR
๐ซ Amazon :- https://pdlink.in/47XuBGz
๐ Cisco :- https://pdlink.in/4gaeVVV
๐ช Microsoft :- https://pdlink.in/4zhGTX6
๐ข Save & share this with your friends โ start upskilling for FREE!
Learn in-demand skills โข Add valuable credentials to your resume
๐ข TATA :- https://pdlink.in/3QiwLvx
๐ป Infosys :- https://pdlink.in/4eBH3Aa
โก IBM :- https://pdlink.in/45KgqDR
๐ซ Amazon :- https://pdlink.in/47XuBGz
๐ Cisco :- https://pdlink.in/4gaeVVV
๐ช Microsoft :- https://pdlink.in/4zhGTX6
๐ข Save & share this with your friends โ start upskilling for FREE!
๐ Data Analyst Roadmap โ Part 16
๐ง SQL Level 6 โ Window Functions
Window functions are one of the most important SQL skills for a Data Analyst.
They allow you to perform calculations across related rows without losing the individual rows.
Instead of collapsing data like GROUP BY, window functions let you analyze each row in the context of other rows.
๐น 1. GROUP BY vs Window Functions
Suppose you have:
With GROUP BY:
You get one row per department.
With a window function:
You keep every employee while also seeing their department's average salary.
๐ GROUP BY reduces rows.
๐ Window functions preserve rows.
๐น 2. Understanding OVER()
Every window function uses the OVER() clause.
The three important concepts are:
โข OVER() โ Defines the window.
โข PARTITION BY โ Divides rows into groups.
โข ORDER BY โ Defines the order inside each group.
๐น 3. ROW_NUMBER()
Assigns a unique sequential number to each row.
Result:
โ ๏ธ If salaries are tied, ROW_NUMBER() still assigns different numbers.
๐น 4. RANK()
Gives the same rank to tied values.
For: 100, 100, 90
RANK() produces: 1, 1, 3
The next rank is skipped.
๐น 5. DENSE_RANK()
Also gives the same rank to tied values, but doesn't skip the next rank.
For: 100, 100, 90
DENSE_RANK() produces: 1, 1, 2
๐ง Remember the Difference
For values: 100, 100, 90, 80
This difference is a very common SQL interview topic.
๐น 6. Overall Ranking
Remove PARTITION BY when you want to rank across the entire dataset.
๐น 7. Top N Employees Per Department
One of the most useful real-world applications.
This finds the top 2 employees in every department.
This pattern is extremely important:
Window Function โ CTE/Subquery โ Filter
๐น 8. LAG()
LAG() lets you access a value from a previous row.
For monthly sales:
๐ง SQL Level 6 โ Window Functions
Window functions are one of the most important SQL skills for a Data Analyst.
They allow you to perform calculations across related rows without losing the individual rows.
Instead of collapsing data like GROUP BY, window functions let you analyze each row in the context of other rows.
๐น 1. GROUP BY vs Window Functions
Suppose you have:
Employee | Department | Salary
John | IT | 75,000
Mike | IT | 90,000
Lisa | IT | 90,000
Sarah | HR | 60,000
Alice | HR | 70,000
With GROUP BY:
SELECT Department, AVG(Salary) AS Avg_Salary
FROM Employees
GROUP BY Department;
You get one row per department.
With a window function:
SELECT
Employee,
Department,
Salary,
AVG(Salary) OVER (PARTITION BY Department) AS Avg_Dept_Salary
FROM Employees;
You keep every employee while also seeing their department's average salary.
๐ GROUP BY reduces rows.
๐ Window functions preserve rows.
๐น 2. Understanding OVER()
Every window function uses the OVER() clause.
FUNCTION() OVER (
PARTITION BY column
ORDER BY column
)
The three important concepts are:
โข OVER() โ Defines the window.
โข PARTITION BY โ Divides rows into groups.
โข ORDER BY โ Defines the order inside each group.
๐น 3. ROW_NUMBER()
Assigns a unique sequential number to each row.
SELECT
Employee,
Department,
Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Row_Num
FROM Employees;
Result:
Employee | Department | Salary | Row_Num
Mike | IT | 90,000 | 1
Lisa | IT | 90,000 | 2
John | IT | 75,000 | 3
Alice | HR | 70,000 | 1
Sarah | HR | 60,000 | 2
โ ๏ธ If salaries are tied, ROW_NUMBER() still assigns different numbers.
๐น 4. RANK()
Gives the same rank to tied values.
For: 100, 100, 90
RANK() produces: 1, 1, 3
The next rank is skipped.
RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Salary_Rank
๐น 5. DENSE_RANK()
Also gives the same rank to tied values, but doesn't skip the next rank.
For: 100, 100, 90
DENSE_RANK() produces: 1, 1, 2
๐ง Remember the Difference
For values: 100, 100, 90, 80
Function | Result
ROW_NUMBER() | 1, 2, 3, 4
RANK() | 1, 1, 3, 4
DENSE_RANK() | 1, 1, 2, 3
This difference is a very common SQL interview topic.
๐น 6. Overall Ranking
Remove PARTITION BY when you want to rank across the entire dataset.
SELECT
Employee,
Salary,
RANK() OVER (
ORDER BY Salary DESC
) AS Overall_Rank
FROM Employees;
๐น 7. Top N Employees Per Department
One of the most useful real-world applications.
WITH Ranked_Employees AS (
SELECT
Employee,
Department,
Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS rn
FROM Employees
)
SELECT
Employee,
Department,
Salary
FROM Ranked_Employees
WHERE rn <= 2;
This finds the top 2 employees in every department.
This pattern is extremely important:
Window Function โ CTE/Subquery โ Filter
๐น 8. LAG()
LAG() lets you access a value from a previous row.
For monthly sales:
Month | Sales
Jan | 10,000
Feb | 12,000
Mar | 15,000
SELECT
Sales_Month,
Sales,
LAG(Sales) OVER (
ORDER BY Sales_Month
) AS Previous_Month_Sales
FROM Monthly_Sales;
You can then calculate month-over-month change:
๐น 9. LEAD()
LEAD() does the opposite.
It allows you to access the next row.
Useful for:
โข Comparing future periods
โข Customer activity
โข Event sequences
โข Next purchase analysis
โข Time-based analysis
๐น 10. Running Total
A running total continuously accumulates values.
Example: 10,000 โ 15,000 โ 22,000 becomes 10,000 โ 25,000 โ 47,000
๐น 11. Running Total by Region
You can combine PARTITION BY with a running total.
Each region gets its own running total.
๐น 12. Moving Average
A moving average helps identify trends while reducing short-term fluctuations.
This calculates a 3-month moving average.
Useful for:
๐ Sales trends,
๐ Revenue analysis,
๐ฆ Demand forecasting,
๐ฅ Customer activity
๐น 13. NTILE()
NTILE() divides rows into approximately equal groups.
For example, divide customers into four sales groups:
This can help identify:
โข Top 25% customers
โข Bottom 25% customers
โข Customer segments
โข Performance groups
๐น 14. Removing Duplicates
Window functions are also extremely useful for deduplication.
This keeps the first record from each duplicate group.
๐น 15. Why Window Functions Cannot Usually Be Used Directly in WHERE
This won't generally work:
Why?
Because the window calculation happens after the filtering stage.
Instead, use a CTE:
This is another reason CTEs + Window Functions are such a powerful combination.
๐ผ Real-World Data Analyst Applications
Window functions are commonly used for:
โ Top N products by category
โ Ranking employees by department
โ Customer rankings by region
โ Month-over-month growth
โ Running revenue totals
โ Moving averages
โ Finding first/previous/next transactions
โ Identifying duplicate records
โ Customer purchase sequences
โ Performance comparisons
๐ฏ SQL Interview Challenge
Question: Find the top 3 highest-paid employees in every department.
๐ Double Tap โค๏ธ For More
SELECT
Sales_Month,
Sales,
Sales - LAG(Sales) OVER (
ORDER BY Sales_Month
) AS Sales_Change
FROM Monthly_Sales;
๐น 9. LEAD()
LEAD() does the opposite.
It allows you to access the next row.
SELECT
Sales_Month,
Sales,
LEAD(Sales) OVER (
ORDER BY Sales_Month
) AS Next_Month_Sales
FROM Monthly_Sales;
Useful for:
โข Comparing future periods
โข Customer activity
โข Event sequences
โข Next purchase analysis
โข Time-based analysis
๐น 10. Running Total
A running total continuously accumulates values.
SELECT
Order_Date,
Sales,
SUM(Sales) OVER (
ORDER BY Order_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Running_Sales
FROM Orders;
Example: 10,000 โ 15,000 โ 22,000 becomes 10,000 โ 25,000 โ 47,000
๐น 11. Running Total by Region
You can combine PARTITION BY with a running total.
SELECT
Region,
Order_Date,
Sales,
SUM(Sales) OVER (
PARTITION BY Region
ORDER BY Order_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Regional_Running_Sales
FROM Orders;
Each region gets its own running total.
๐น 12. Moving Average
A moving average helps identify trends while reducing short-term fluctuations.
SELECT
Sales_Month,
Sales,
AVG(Sales) OVER (
ORDER BY Sales_Month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS Three_Month_Avg
FROM Monthly_Sales;
This calculates a 3-month moving average.
Useful for:
๐ Sales trends,
๐ Revenue analysis,
๐ฆ Demand forecasting,
๐ฅ Customer activity
๐น 13. NTILE()
NTILE() divides rows into approximately equal groups.
For example, divide customers into four sales groups:
SELECT
Customer_ID,
Total_Sales,
NTILE(4) OVER (
ORDER BY Total_Sales DESC
) AS Sales_Quartile
FROM Customers;
This can help identify:
โข Top 25% customers
โข Bottom 25% customers
โข Customer segments
โข Performance groups
๐น 14. Removing Duplicates
Window functions are also extremely useful for deduplication.
WITH Ranked_Data AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY Customer_ID, Order_Date, Sales
ORDER BY Order_ID
) AS rn
FROM Orders
)
SELECT *
FROM Ranked_Data
WHERE rn = 1;
This keeps the first record from each duplicate group.
๐น 15. Why Window Functions Cannot Usually Be Used Directly in WHERE
This won't generally work:
SELECT
Employee,
RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
WHERE Salary_Rank <= 3;
Why?
Because the window calculation happens after the filtering stage.
Instead, use a CTE:
WITH Ranked AS (
SELECT
Employee,
Salary,
RANK() OVER (
ORDER BY Salary DESC
) AS Salary_Rank
FROM Employees
)
SELECT *
FROM Ranked
WHERE Salary_Rank <= 3;
This is another reason CTEs + Window Functions are such a powerful combination.
๐ผ Real-World Data Analyst Applications
Window functions are commonly used for:
โ Top N products by category
โ Ranking employees by department
โ Customer rankings by region
โ Month-over-month growth
โ Running revenue totals
โ Moving averages
โ Finding first/previous/next transactions
โ Identifying duplicate records
โ Customer purchase sequences
โ Performance comparisons
๐ฏ SQL Interview Challenge
Question: Find the top 3 highest-paid employees in every department.
WITH Ranked_Employees AS (
SELECT
Employee,
Department,
Salary,
DENSE_RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Salary_Rank
FROM Employees
)
SELECT
Employee,
Department,
Salary
FROM Ranked_Employees
WHERE Salary_Rank <= 3;
๐ Double Tap โค๏ธ For More
โค10๐ฅ1
๐ ๐๐ฟ๐ฒ๐ฎ๐บ๐ถ๐ป๐ด ๐ผ๐ณ ๐ช๐ผ๐ฟ๐ธ๐ถ๐ป๐ด ๐ฎ๐ ๐ง๐ผ๐ฝ ๐ง๐ฒ๐ฐ๐ต ๐๐ผ๐บ๐ฝ๐ฎ๐ป๐ถ๐ฒ๐? ๐ป๐ฅ
Hereโs a collection of company-specific resources to help you understand their interview and hiring processes.
๐ฏ Interview Preparation Guides For:
๐ Amazon โ Interviewing Guide
๐ต Google โ Interview Tips
๐ช Microsoft โ Hiring & Interview Tips
๐ข NVIDIA โ Hiring Process
๐ท Meta โ Software Engineering Interview Prep
๐๐ข๐ง๐ค ๐:-
https://pdlink.in/4i6HkgN
๐ข Save & share this with your friends โ start learning for FREE!
Hereโs a collection of company-specific resources to help you understand their interview and hiring processes.
๐ฏ Interview Preparation Guides For:
๐ Amazon โ Interviewing Guide
๐ต Google โ Interview Tips
๐ช Microsoft โ Hiring & Interview Tips
๐ข NVIDIA โ Hiring Process
๐ท Meta โ Software Engineering Interview Prep
๐๐ข๐ง๐ค ๐:-
https://pdlink.in/4i6HkgN
๐ข Save & share this with your friends โ start learning for FREE!
โค3๐1