Learning this structure will make many analytical SQL problems much easier.
๐งช Practical Interview Challenge
Suppose you have Orders with:
1001 North Electronics 80,000
1002 North Furniture 40,000
1003 South Electronics 70,000
1004 South Furniture 50,000
1005 North Electronics 60,000
Q1. Find total sales.
Q2. Find average sales.
Q3. Find sales by region.
Q4. Count orders by region.
Q5. Find average sales by category.
Q6. Show only regions with sales greater than โน100,000.
Q7. Sort regions by highest sales.
๐ Double Tap โค๏ธ For More
๐งช Practical Interview Challenge
Suppose you have Orders with:
1001 North Electronics 80,000
1002 North Furniture 40,000
1003 South Electronics 70,000
1004 South Furniture 50,000
1005 North Electronics 60,000
Q1. Find total sales.
SELECT SUM(Sales) AS Total_Sales FROM Orders;
Q2. Find average sales.
SELECT AVG(Sales) AS Average_Sales FROM Orders;
Q3. Find sales by region.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region;
Q4. Count orders by region.
SELECT Region, COUNT(*) AS Order_Count FROM Orders GROUP BY Region;
Q5. Find average sales by category.
SELECT Category, AVG(Sales) AS Average_Sales FROM Orders GROUP BY Category;
Q6. Show only regions with sales greater than โน100,000.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region HAVING SUM(Sales) > 100000;
Q7. Sort regions by highest sales.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region ORDER BY Total_Sales DESC;
๐ Double Tap โค๏ธ For More
โค3๐1
๐ง๐ผ๐ฝ ๐๐ป-๐๐ฒ๐บ๐ฎ๐ป๐ฑ ๐ฆ๐ธ๐ถ๐น๐น๐ ๐๐ผ ๐๐๐๐๐ฟ๐ฒ-๐ฃ๐ฟ๐ผ๐ผ๐ณ ๐ฌ๐ผ๐๐ฟ ๐๐ฎ๐ฟ๐ฒ๐ฒ๐ฟ ๐
๐ฅ Skills Worth Learning:
โ๏ธ Blockchain
โ๏ธ Cloud Computing
โพ๏ธ DevOps Engineering
๐ค Artificial Intelligence & Machine Learning
๐ Data Science & Analytics
๐ Cybersecurity
๐ฏ Leadership & Communication
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlinks.in/i89
Donโt just collect certificates โ build projects, gain practical experience and showcase your skills on your resume & LinkedIn.
๐ฅ Skills Worth Learning:
โ๏ธ Blockchain
โ๏ธ Cloud Computing
โพ๏ธ DevOps Engineering
๐ค Artificial Intelligence & Machine Learning
๐ Data Science & Analytics
๐ Cybersecurity
๐ฏ Leadership & Communication
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:-
https://pdlinks.in/i89
Donโt just collect certificates โ build projects, gain practical experience and showcase your skills on your resume & LinkedIn.
๐ Data Analyst Roadmap โ Part 13
๐๏ธ SQL โ Level 3: CASE WHEN, NULL Handling & Conditional Logic
In the previous part, you learned how to summarize data using GROUP BY and aggregate functions.
Now we're going to make SQL more powerful by learning how to create categories, handle missing data, and apply business rules.
These skills are extremely important because real-world datasets are rarely perfect.
You may need to answer questions like:
This is where CASE WHEN and NULL-handling functions become essential.
1๏ธโฃ What Is CASE WHEN?
CASE WHEN allows SQL to make decisions.
Think of it as the SQL equivalent of Excel's:
IF()
For example:
SQL evaluates the conditions and returns the appropriate category.
2๏ธโฃ Basic CASE WHEN
Suppose you have:
Order_ID | Sales
1001 | 120,000
1002 | 75,000
1003 | 30,000
You want to classify orders.
Result:
Order_ID | Sales | Sales_Category
1001 | 120,000 | High
1002 | 75,000 | Medium
1003 | 30,000 | Low
3๏ธโฃ Understand the Evaluation Order
SQL evaluates the WHEN conditions from top to bottom.
For example:
If Sales = 120000:
Is it โฅ 100000? โ
Return High
Stop evaluating the remaining conditions.
That's why the order of conditions matters.
4๏ธโฃ CASE WHEN with Categories
Suppose employees have salaries.
You want:
โน100,000+ โ Senior
โน60,000โ99,999 โ Mid-Level
Below โน60,000 โ Junior
This is a common data transformation technique.
5๏ธโฃ CASE WHEN with Text Conditions
You can also evaluate text.
Suppose:
Department
IT
HR
Finance
Sales
You want to categorize IT and Finance as:
Business-Critical
and everything else as:
Other
6๏ธโฃ CASE WHEN with AND
You can combine multiple conditions.
Suppose an employee qualifies for a bonus if:
Department = IT
Salary > โน80,000
Both conditions must be true.
7๏ธโฃ CASE WHEN with OR
Suppose employees from IT or Finance should receive a particular classification.
๐๏ธ SQL โ Level 3: CASE WHEN, NULL Handling & Conditional Logic
In the previous part, you learned how to summarize data using GROUP BY and aggregate functions.
Now we're going to make SQL more powerful by learning how to create categories, handle missing data, and apply business rules.
These skills are extremely important because real-world datasets are rarely perfect.
You may need to answer questions like:
Which orders are High, Medium, or Low value?
How many customers have missing information?
What should we display when a value is NULL?
How many employees are above their target?
This is where CASE WHEN and NULL-handling functions become essential.
1๏ธโฃ What Is CASE WHEN?
CASE WHEN allows SQL to make decisions.
Think of it as the SQL equivalent of Excel's:
IF()
For example:
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END
SQL evaluates the conditions and returns the appropriate category.
2๏ธโฃ Basic CASE WHEN
Suppose you have:
Order_ID | Sales
1001 | 120,000
1002 | 75,000
1003 | 30,000
You want to classify orders.
SELECT
Order_ID,
Sales,
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category
FROM Orders;
Result:
Order_ID | Sales | Sales_Category
1001 | 120,000 | High
1002 | 75,000 | Medium
1003 | 30,000 | Low
3๏ธโฃ Understand the Evaluation Order
SQL evaluates the WHEN conditions from top to bottom.
For example:
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END
If Sales = 120000:
Is it โฅ 100000? โ
Return High
Stop evaluating the remaining conditions.
That's why the order of conditions matters.
4๏ธโฃ CASE WHEN with Categories
Suppose employees have salaries.
You want:
โน100,000+ โ Senior
โน60,000โ99,999 โ Mid-Level
Below โน60,000 โ Junior
SELECT
Name,
Salary,
CASE
WHEN Salary >= 100000 THEN 'Senior'
WHEN Salary >= 60000 THEN 'Mid-Level'
ELSE 'Junior'
END AS Salary_Level
FROM Employees;
This is a common data transformation technique.
5๏ธโฃ CASE WHEN with Text Conditions
You can also evaluate text.
Suppose:
Department
IT
HR
Finance
Sales
You want to categorize IT and Finance as:
Business-Critical
and everything else as:
Other
SELECT
Name,
Department,
CASE
WHEN Department IN ('IT', 'Finance')
THEN 'Business-Critical'
ELSE 'Other'
END AS Department_Type
FROM Employees;
6๏ธโฃ CASE WHEN with AND
You can combine multiple conditions.
Suppose an employee qualifies for a bonus if:
Department = IT
Salary > โน80,000
SELECT
Name,
Department,
Salary,
CASE
WHEN Department = 'IT'
AND Salary > 80000
THEN 'Bonus Eligible'
ELSE 'Not Eligible'
END AS Bonus_Status
FROM Employees;
Both conditions must be true.
7๏ธโฃ CASE WHEN with OR
Suppose employees from IT or Finance should receive a particular classification.
SELECT
Name,
Department,
CASE
WHEN Department = 'IT'
OR Department = 'Finance'
THEN 'Priority'
ELSE 'Standard'
END AS Employee_Type
FROM Employees;
At least one condition must be true.
8๏ธโฃ CASE WHEN with Aggregation
Here's where CASE WHEN becomes extremely powerful.
Suppose you want to count high-value orders.
You can write:
This counts only orders where Sales is at least โน100,000.
9๏ธโฃ Conditional SUM
Suppose you want:
Use:
This calculates sales only for qualifying orders.
This technique is called conditional aggregation.
๐ Conditional Aggregation by Region
Suppose you want to compare:
North sales
South sales
in the same result.
Result:
North_Sales | South_Sales
500,000 | 350,000
This is extremely useful when building analytical reports.
1๏ธโฃ1๏ธโฃ CASE WHEN with GROUP BY
You can create categories and then aggregate them.
For example:
This tells you how many orders belong to each sales category.
1๏ธโฃ2๏ธโฃ What Is NULL?
NULL represents missing or unknown information.
It is important to understand:
For example:
Salary = 0
means the salary value is explicitly zero.
But:
Salary = NULL
means the value is missing or unknown.
Similarly:
Discount = NULL
doesn't necessarily mean:
Discount = 0
It means:
No value is available.
1๏ธโฃ3๏ธโฃ NULL Is Not an Empty String
These are different:
NULL
''
' '
0
NULL
Missing/unknown value.
Empty string
A text value containing no characters.
Space
A string containing a space.
Zero
A numeric value equal to zero.
This distinction is extremely important when cleaning data.
1๏ธโฃ4๏ธโฃ Don't Use = NULL
A common beginner mistake is:
This is incorrect for testing NULL.
Instead, use:
To find non-NULL values:
1๏ธโฃ5๏ธโฃ Find Missing Values
Suppose you want customers whose phone numbers are missing:
This is useful for data-quality analysis.
1๏ธโฃ6๏ธโฃ Count Missing Values
You can use conditional aggregation:
Now you can see:
Total_Customers | Missing_Phone
10,000 | 350
So:
350 customers have missing phone numbers.
1๏ธโฃ7๏ธโฃ COALESCE()
COALESCE() returns the first non-NULL value.
For example:
8๏ธโฃ CASE WHEN with Aggregation
Here's where CASE WHEN becomes extremely powerful.
Suppose you want to count high-value orders.
You can write:
SELECT
COUNT(
CASE
WHEN Sales >= 100000 THEN 1
END
) AS High_Value_Orders
FROM Orders;
This counts only orders where Sales is at least โน100,000.
9๏ธโฃ Conditional SUM
Suppose you want:
Total sales from high-value orders.
Use:
SELECT
SUM(
CASE
WHEN Sales >= 100000 THEN Sales
ELSE 0
END
) AS High_Value_Sales
FROM Orders;
This calculates sales only for qualifying orders.
This technique is called conditional aggregation.
๐ Conditional Aggregation by Region
Suppose you want to compare:
North sales
South sales
in the same result.
SELECT
SUM(
CASE
WHEN Region = 'North' THEN Sales
ELSE 0
END
) AS North_Sales,
SUM(
CASE
WHEN Region = 'South' THEN Sales
ELSE 0
END
) AS South_Sales
FROM Orders;
Result:
North_Sales | South_Sales
500,000 | 350,000
This is extremely useful when building analytical reports.
1๏ธโฃ1๏ธโฃ CASE WHEN with GROUP BY
You can create categories and then aggregate them.
For example:
SELECT
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category,
COUNT(*) AS Order_Count
FROM Orders
GROUP BY
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END;
This tells you how many orders belong to each sales category.
1๏ธโฃ2๏ธโฃ What Is NULL?
NULL represents missing or unknown information.
It is important to understand:
NULL is not the same as zero.
For example:
Salary = 0
means the salary value is explicitly zero.
But:
Salary = NULL
means the value is missing or unknown.
Similarly:
Discount = NULL
doesn't necessarily mean:
Discount = 0
It means:
No value is available.
1๏ธโฃ3๏ธโฃ NULL Is Not an Empty String
These are different:
NULL
''
' '
0
NULL
Missing/unknown value.
Empty string
A text value containing no characters.
Space
A string containing a space.
Zero
A numeric value equal to zero.
This distinction is extremely important when cleaning data.
1๏ธโฃ4๏ธโฃ Don't Use = NULL
A common beginner mistake is:
WHERE Email = NULLThis is incorrect for testing NULL.
Instead, use:
WHERE Email IS NULLTo find non-NULL values:
WHERE Email IS NOT NULL1๏ธโฃ5๏ธโฃ Find Missing Values
Suppose you want customers whose phone numbers are missing:
SELECT *
FROM Customers
WHERE Phone IS NULL;
This is useful for data-quality analysis.
1๏ธโฃ6๏ธโฃ Count Missing Values
You can use conditional aggregation:
SELECT
COUNT(*) AS Total_Customers,
COUNT(
CASE
WHEN Phone IS NULL THEN 1
END
) AS Missing_Phone
FROM Customers;
Now you can see:
Total_Customers | Missing_Phone
10,000 | 350
So:
350 customers have missing phone numbers.
1๏ธโฃ7๏ธโฃ COALESCE()
COALESCE() returns the first non-NULL value.
For example:
SELECT
Customer_Name,
COALESCE(Phone, 'Not Available') AS Phone
FROM Customers;
If Phone is NULL, SQL returns:
Not Available
Otherwise, it returns the actual phone number.
1๏ธโฃ8๏ธโฃ COALESCE() with Multiple Options
You can provide multiple alternatives.
SQL checks:
1. Work Email
2. Personal Email
3. "No Email"
It returns the first non-NULL value.
This is extremely useful when combining multiple possible sources of information.
1๏ธโฃ9๏ธโฃ NULLIF()
NULLIF() returns NULL if two expressions are equal.
For example:
If Sales is:
0
the result becomes:
NULL
Otherwise, the original Sales value is returned.
2๏ธโฃ0๏ธโฃ Why NULLIF() Is Useful
Suppose you're calculating:
Profit Margin = Profit / Sales
If Sales is zero:
Profit / Sales
could cause a division-by-zero error.
You can use:
If Sales = 0:
So the division doesn't attempt to divide by zero.
This is an important practical technique.
2๏ธโฃ1๏ธโฃ CASE WHEN + NULL
You can also explicitly handle missing values.
Result:
Customer_Name | Phone_Status
John | Available
Sarah | Missing
Mike | Available
This is useful for data-quality reports.
2๏ธโฃ2๏ธโฃ Categorize Customers
Suppose you want to classify customers based on total spending:
โน1,00,000+ โ VIP
โน50,000+ โ Premium
โน20,000+ โ Standard
Below โน20,000 โ Basic
After calculating customer-level sales, you could use:
This type of segmentation is widely used in business analytics.
2๏ธโฃ3๏ธโฃ CASE WHEN for KPI Status
Suppose the target is:
โน10,00,000
and actual sales are stored in Total_Sales.
You could create:
This turns a raw number into a business interpretation.
2๏ธโฃ4๏ธโฃ CASE WHEN for Profitability
Suppose:
Profit > 0 โ Profitable
Profit = 0 โ Break-even
Profit < 0 โ Loss
Use:
This is a simple but powerful analytical transformation.
2๏ธโฃ5๏ธโฃ CASE WHEN for Data Cleaning
Suppose your dataset contains:
India
INDIA
india
IN
You can standardize values with a CASE expression:
For a small number of known inconsistencies, this can be useful.
For larger or recurring transformations, you may want to handle standardization upstream in your data pipeline.
2๏ธโฃ6๏ธโฃ A Powerful Interview Pattern
You will frequently encounter queries like:
Not Available
Otherwise, it returns the actual phone number.
1๏ธโฃ8๏ธโฃ COALESCE() with Multiple Options
You can provide multiple alternatives.
SELECT
COALESCE(Work_Email, Personal_Email, 'No Email')
AS Contact_Email
FROM Customers;
SQL checks:
1. Work Email
2. Personal Email
3. "No Email"
It returns the first non-NULL value.
This is extremely useful when combining multiple possible sources of information.
1๏ธโฃ9๏ธโฃ NULLIF()
NULLIF() returns NULL if two expressions are equal.
For example:
NULLIF(Sales, 0)If Sales is:
0
the result becomes:
NULL
Otherwise, the original Sales value is returned.
2๏ธโฃ0๏ธโฃ Why NULLIF() Is Useful
Suppose you're calculating:
Profit Margin = Profit / Sales
If Sales is zero:
Profit / Sales
could cause a division-by-zero error.
You can use:
SELECT
Profit / NULLIF(Sales, 0) AS Profit_Margin
FROM Orders;
If Sales = 0:
NULLIF(0,0) โ NULLSo the division doesn't attempt to divide by zero.
This is an important practical technique.
2๏ธโฃ1๏ธโฃ CASE WHEN + NULL
You can also explicitly handle missing values.
SELECT
Customer_Name,
CASE
WHEN Phone IS NULL THEN 'Missing'
ELSE 'Available'
END AS Phone_Status
FROM Customers;
Result:
Customer_Name | Phone_Status
John | Available
Sarah | Missing
Mike | Available
This is useful for data-quality reports.
2๏ธโฃ2๏ธโฃ Categorize Customers
Suppose you want to classify customers based on total spending:
โน1,00,000+ โ VIP
โน50,000+ โ Premium
โน20,000+ โ Standard
Below โน20,000 โ Basic
After calculating customer-level sales, you could use:
CASE
WHEN Total_Sales >= 100000 THEN 'VIP'
WHEN Total_Sales >= 50000 THEN 'Premium'
WHEN Total_Sales >= 20000 THEN 'Standard'
ELSE 'Basic'
END
This type of segmentation is widely used in business analytics.
2๏ธโฃ3๏ธโฃ CASE WHEN for KPI Status
Suppose the target is:
โน10,00,000
and actual sales are stored in Total_Sales.
You could create:
CASE
WHEN Total_Sales >= 1000000 THEN 'Target Achieved'
ELSE 'Below Target'
END
This turns a raw number into a business interpretation.
2๏ธโฃ4๏ธโฃ CASE WHEN for Profitability
Suppose:
Profit > 0 โ Profitable
Profit = 0 โ Break-even
Profit < 0 โ Loss
Use:
CASE
WHEN Profit > 0 THEN 'Profitable'
WHEN Profit = 0 THEN 'Break-even'
ELSE 'Loss'
END AS Profit_Status
This is a simple but powerful analytical transformation.
2๏ธโฃ5๏ธโฃ CASE WHEN for Data Cleaning
Suppose your dataset contains:
India
INDIA
india
IN
You can standardize values with a CASE expression:
CASE
WHEN Country IN ('India', 'INDIA', 'india', 'IN')
THEN 'India'
ELSE Country
END AS Standardized_Country
For a small number of known inconsistencies, this can be useful.
For larger or recurring transformations, you may want to handle standardization upstream in your data pipeline.
2๏ธโฃ6๏ธโฃ A Powerful Interview Pattern
You will frequently encounter queries like:
SELECT
Region,
SUM(Sales) AS Total_Sales,
CASE
WHEN SUM(Sales) >= 1000000
THEN 'Target Achieved'
ELSE 'Below Target'
END AS Target_Status
FROM Orders
GROUP BY Region;
This combines:
GROUP BY
SUM()
CASE WHEN
to create a business-ready result.
2๏ธโฃ7๏ธโฃ Important SQL Execution Concept
A simplified logical order of SQL processing is:
FROM
โ
WHERE
โ
GROUP BY
โ
HAVING
โ
SELECT
โ
ORDER BY
This helps explain why SQL behaves differently from how the query appears visually.
For example:
Think:
Get data โ filter rows โ group โ calculate โ filter groups โ sort
Understanding SQL's logical processing order will become increasingly important as queries get more complex.
๐งช Practical Interview Challenge
Suppose you have:
Orders
Order_ID | Region | Sales | Profit
1001 | North | 120,000 | 20,000
1002 | South | 75,000 | 10,000
1003 | North | 30,000 | -5,000
1004 | West | 150,000 | 30,000
1005 | South | NULL | 8,000
Q1. Categorize orders by sales.
Q2. Find orders with missing sales.
Q3. Replace missing sales with zero for display.
Remember: this changes the display/calculation result, not necessarily the underlying data.
Q4. Categorize profitability.
Q5. Count high-value orders.
Q6. Calculate profit margin safely.
๐ Double Tap โค๏ธ For More
GROUP BY
SUM()
CASE WHEN
to create a business-ready result.
2๏ธโฃ7๏ธโฃ Important SQL Execution Concept
A simplified logical order of SQL processing is:
FROM
โ
WHERE
โ
GROUP BY
โ
HAVING
โ
SELECT
โ
ORDER BY
This helps explain why SQL behaves differently from how the query appears visually.
For example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;
Think:
Get data โ filter rows โ group โ calculate โ filter groups โ sort
Understanding SQL's logical processing order will become increasingly important as queries get more complex.
๐งช Practical Interview Challenge
Suppose you have:
Orders
Order_ID | Region | Sales | Profit
1001 | North | 120,000 | 20,000
1002 | South | 75,000 | 10,000
1003 | North | 30,000 | -5,000
1004 | West | 150,000 | 30,000
1005 | South | NULL | 8,000
Q1. Categorize orders by sales.
SELECT
Order_ID,
Sales,
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category
FROM Orders;
Q2. Find orders with missing sales.
SELECT *
FROM Orders
WHERE Sales IS NULL;
Q3. Replace missing sales with zero for display.
SELECT
Order_ID,
COALESCE(Sales, 0) AS Sales
FROM Orders;
Remember: this changes the display/calculation result, not necessarily the underlying data.
Q4. Categorize profitability.
SELECT
Order_ID,
CASE
WHEN Profit > 0 THEN 'Profitable'
WHEN Profit = 0 THEN 'Break-even'
ELSE 'Loss'
END AS Profit_Status
FROM Orders;
Q5. Count high-value orders.
SELECT
COUNT(
CASE
WHEN Sales >= 100000 THEN 1
END
) AS High_Value_Orders
FROM Orders;
Q6. Calculate profit margin safely.
SELECT
Order_ID,
Profit / NULLIF(Sales, 0) AS Profit_Margin
FROM Orders;
๐ Double Tap โค๏ธ For More
โค12
๐ ๐๐ฅ๐๐ ๐๐ฒ๐ป๐๐ + ๐๐น๐ฎ๐๐ฑ๐ฒ ๐ข๐ป๐น๐ถ๐ป๐ฒ ๐ ๐ฎ๐๐๐ฒ๐ฟ๐ฐ๐น๐ฎ๐๐ ๐
Want to work faster, create better content and save hours every week using AI?
Join this beginner-friendly masterclass and discover how to use ๐ฎ๐ฑ+ powerful AI tools to:
โ Automate repetitive tasks
โ Create professional content in minutes
โ Improve productivity and efficiency
โ Save valuable time every week
โ Use GenAI and Claude effectively
๐ก No technical knowledge or previous AI experience required!
๐ ๐ฅ๐ฒ๐ด๐ถ๐๐๐ฒ๐ฟ ๐ณ๐ผ๐ฟ ๐๐ฅ๐๐ ๐
https://pdlink.in/46wurp9
โก Limited slots availableโregister now and start working smarter with AI!
Want to work faster, create better content and save hours every week using AI?
Join this beginner-friendly masterclass and discover how to use ๐ฎ๐ฑ+ powerful AI tools to:
โ Automate repetitive tasks
โ Create professional content in minutes
โ Improve productivity and efficiency
โ Save valuable time every week
โ Use GenAI and Claude effectively
๐ก No technical knowledge or previous AI experience required!
๐ ๐ฅ๐ฒ๐ด๐ถ๐๐๐ฒ๐ฟ ๐ณ๐ผ๐ฟ ๐๐ฅ๐๐ ๐
https://pdlink.in/46wurp9
โก Limited slots availableโregister now and start working smarter with AI!
โค2
๐ Data Analyst Roadmap โ Part 14
๐๏ธ SQL โ Level 4: JOINs
One of the most important SQL skills for a Data Analyst is understanding JOINs.
In real-world databases, information is rarely stored in one giant table. Instead, data is usually split across multiple related tables.
For example:
Customers โ Orders โ Products โ Payments
JOINs allow you to bring related information together.
1๏ธโฃ What Is a JOIN?
A JOIN combines rows from two or more tables using a related column.
Suppose you have:
Customers
Orders
Both tables have Customer_ID. That common field allows us to connect them.
2๏ธโฃ Why Are JOINs Important?
Imagine your manager asks: "Show me each customer's name along with their total sales."
The customer name is in Customers. The sales amount is in Orders. You need to combine the tables. That's a JOIN problem.
3๏ธโฃ Basic JOIN Syntax
The ON condition tells SQL: How are these two tables related?
4๏ธโฃ INNER JOIN
INNER JOIN returns only records where a match exists in both tables.
If Customer 104 exists only in Orders, it won't be returned.
Result after INNER JOIN:
John | 50,000
Sarah | 70,000
5๏ธโฃ INNER JOIN โ Simple Rule
INNER JOIN = Only matching records
Think: Table A โฉ Table B
6๏ธโฃ LEFT JOIN
LEFT JOIN returns All rows from the left table plus matching rows from the right table.
Query:
Result:
John | 50,000
Sarah | 70,000
Mike | NULL
Mike doesn't have an order, but because Customers is the left table, Mike remains in the result.
7๏ธโฃ Why LEFT JOIN Is Extremely Important
To find customers who have never placed an order:
This is a very common analytical pattern.
8๏ธโฃ RIGHT JOIN
RIGHT JOIN is the reverse of LEFT JOIN. It returns All rows from the right table plus matching rows from the left.
9๏ธโฃ Do Data Analysts Need RIGHT JOIN?
You should understand it. However, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN because LEFT JOIN is often easier to read.
A RIGHT JOIN B can be rewritten as B LEFT JOIN A.
๐ FULL OUTER JOIN
A FULL OUTER JOIN returns:
โข Matching rows
โข Unmatched rows from the left
โข Unmatched rows from the right
1๏ธโฃ1๏ธโฃ FULL OUTER JOIN Example
Useful for identifying data inconsistencies and missing relationships.
1๏ธโฃ2๏ธโฃ JOIN Comparison
โข INNER JOIN: Matching rows only
โข LEFT JOIN: All left + matching right
โข RIGHT JOIN: All right + matching left
โข FULL OUTER JOIN: Everything from both
Most important for Data Analysts: INNER JOIN and LEFT JOIN. Master these first.
1๏ธโฃ3๏ธโฃ JOIN with Multiple Columns
Composite join conditions are common in real-world datasets.
1๏ธโฃ4๏ธโฃ Joining More Than Two Tables
๐๏ธ SQL โ Level 4: JOINs
One of the most important SQL skills for a Data Analyst is understanding JOINs.
In real-world databases, information is rarely stored in one giant table. Instead, data is usually split across multiple related tables.
For example:
Customers โ Orders โ Products โ Payments
JOINs allow you to bring related information together.
1๏ธโฃ What Is a JOIN?
A JOIN combines rows from two or more tables using a related column.
Suppose you have:
Customers
Customer_ID | Customer_Name | City
101 | John | Pune
102 | Sarah | Mumbai
103 | Mike | Delhi
Orders
Order_ID | Customer_ID | Sales
5001 | 101 | 50,000
5002 | 102 | 70,000
5003 | 101 | 30,000
Both tables have Customer_ID. That common field allows us to connect them.
2๏ธโฃ Why Are JOINs Important?
Imagine your manager asks: "Show me each customer's name along with their total sales."
The customer name is in Customers. The sales amount is in Orders. You need to combine the tables. That's a JOIN problem.
3๏ธโฃ Basic JOIN Syntax
SELECT
Customers.Customer_Name,
Orders.Sales
FROM Customers
JOIN Orders
ON Customers.Customer_ID = Orders.Customer_ID;
The ON condition tells SQL: How are these two tables related?
4๏ธโฃ INNER JOIN
INNER JOIN returns only records where a match exists in both tables.
If Customer 104 exists only in Orders, it won't be returned.
Result after INNER JOIN:
John | 50,000
Sarah | 70,000
5๏ธโฃ INNER JOIN โ Simple Rule
INNER JOIN = Only matching records
Think: Table A โฉ Table B
6๏ธโฃ LEFT JOIN
LEFT JOIN returns All rows from the left table plus matching rows from the right table.
Query:
SELECT
Customers.Customer_Name,
Orders.Sales
FROM Customers
LEFT JOIN Orders
ON Customers.Customer_ID = Orders.Customer_ID;
Result:
John | 50,000
Sarah | 70,000
Mike | NULL
Mike doesn't have an order, but because Customers is the left table, Mike remains in the result.
7๏ธโฃ Why LEFT JOIN Is Extremely Important
To find customers who have never placed an order:
SELECT c.Customer_ID, c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;
This is a very common analytical pattern.
8๏ธโฃ RIGHT JOIN
RIGHT JOIN is the reverse of LEFT JOIN. It returns All rows from the right table plus matching rows from the left.
9๏ธโฃ Do Data Analysts Need RIGHT JOIN?
You should understand it. However, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN because LEFT JOIN is often easier to read.
A RIGHT JOIN B can be rewritten as B LEFT JOIN A.
๐ FULL OUTER JOIN
A FULL OUTER JOIN returns:
โข Matching rows
โข Unmatched rows from the left
โข Unmatched rows from the right
1๏ธโฃ1๏ธโฃ FULL OUTER JOIN Example
SELECT c.Customer_ID, c.Customer_Name, o.Order_ID
FROM Customers c
FULL OUTER JOIN Orders o ON c.Customer_ID = o.Customer_ID;
Useful for identifying data inconsistencies and missing relationships.
1๏ธโฃ2๏ธโฃ JOIN Comparison
โข INNER JOIN: Matching rows only
โข LEFT JOIN: All left + matching right
โข RIGHT JOIN: All right + matching left
โข FULL OUTER JOIN: Everything from both
Most important for Data Analysts: INNER JOIN and LEFT JOIN. Master these first.
1๏ธโฃ3๏ธโฃ JOIN with Multiple Columns
ON A.Product_ID = B.Product_ID
AND A.Region = B.Region
Composite join conditions are common in real-world datasets.
1๏ธโฃ4๏ธโฃ Joining More Than Two Tables
โค1
SELECT c.Customer_Name, o.Order_ID, p.Product_Name, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
JOIN Products p ON o.Product_ID = p.Product_ID;
1๏ธโฃ5๏ธโฃ Table Aliases
Customers c, Orders o, Products p
Instead of Customers.Customer_ID, you can write c.Customer_ID. Much easier to read.
1๏ธโฃ6๏ธโฃ JOIN + GROUP BY
This is one of the most important patterns.
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;
JOIN + SUM + GROUP BY is a very common interview question.
1๏ธโฃ7๏ธโฃ JOIN + WHERE
SELECT c.Customer_Name, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE c.Department = 'IT';
JOIN connects, WHERE filters.
1๏ธโฃ8๏ธโฃ JOIN + HAVING
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) > 100000;
Pattern: JOIN โ GROUP BY โ SUM โ HAVING
1๏ธโฃ9๏ธโฃ The Most Important LEFT JOIN Pattern
SELECT c.Customer_ID, c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;
Answers: Which customers have no orders?
General pattern: LEFT JOIN + WHERE right_table.key IS NULL
2๏ธโฃ0๏ธโฃ JOIN and NULL
After a LEFT JOIN, unmatched columns become NULL. That NULL tells us: No matching order was found.
2๏ธโฃ1๏ธโฃ Self JOIN
A Self JOIN joins a table to itself. Useful for employee-manager relationships.
SELECT e.Employee_Name AS Employee, m.Employee_Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.Manager_ID = m.Employee_ID;
2๏ธโฃ2๏ธโฃ Many-to-One Relationships
Customers โ Orders is One-to-Many. From Orders perspective, it's Many-to-One. Understanding direction is critical.
2๏ธโฃ3๏ธโฃ The Duplicate Row Problem
If John has 3 orders, after JOIN John appears 3 times. That's correct, not an error. Always understand relationship cardinality.
2๏ธโฃ4๏ธโฃ JOIN Multiplication
If a customer has 3 orders and 4 payments, an incorrect join can produce 12 combinations and inflate Sales, Counts, Profit. Always check grain before aggregating.
2๏ธโฃ5๏ธโฃ What Is Table Grain?
Grain means: What does one row represent?
Customers: One row = one customer
Orders: One row = one order
2๏ธโฃ6๏ธโฃ JOIN vs UNION
โข JOIN: Combines tables horizontally. Adds columns.
โข UNION: Combines results vertically. Adds rows.
2๏ธโฃ7๏ธโฃ Practical Business Example
Question: What is total sales by region?
SELECT c.Region, SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Region
ORDER BY Total_Sales DESC;
๐งช Practical Interview Challenge
Q1. Show customer names with their orders.
SELECT c.Customer_Name, o.Order_ID, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID;
Q2. Show all customers, including those without orders.
SELECT c.Customer_Name, o.Order_ID, o.Sales
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID;
Q3. Find customers who have never ordered.
SELECT c.Customer_ID, c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;
Q4. Find total sales per customer.
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;
โค1
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