𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗮𝗻𝗱 𝗟𝗶𝗻𝗸𝗲𝗱𝗜𝗻 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀🎓

Want to strengthen your resume with career-focused professional skills? Explore these free learning paths from Microsoft and LinkedIn.

🔥 Courses Available:
📌 Project Management
📊 Business Analysis
💻 System Administration
📈 Data Analysis

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

https://pdlinks.in/micrlink

💡 Learn → Get Certified → Upgrade Your Resume → Boost Your Career

𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

Explore Google Cloud learning resources covering AI/ML fundamentals through practical and advanced concepts.

🚀 Learn AI → Practice ML → Build Skills → Become Career Ready

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

https://pdlinks.in/eb6

🚀 Learn AI → Practice ML → Build Skills → Become Career Ready

🚀 Data Analyst Roadmap — Part 10

🧹 Excel — Level 9: Power Query for Data Cleaning & Transformation

So far, you've learned how to analyze data using Excel formulas and PivotTables.

But there's a major problem with real-world data:

The data is often messy.

You might receive a monthly Excel file with:

• Duplicate records

• Missing values

• Incorrect data types

• Extra spaces

• Inconsistent names

• Multiple files

• Unnecessary columns

• Data spread across different tables

Cleaning this manually every time is slow and error-prone.

That's where Power Query comes in.

1️⃣ What Is Power Query?

Power Query is a data preparation and transformation tool available in Excel and Power BI.

It allows you to: Connect → Extract → Transform → Load - This is commonly called ETL.

Extract: Get data from a source.

Transform: Clean and reshape the data.

Load: Bring the prepared data into Excel for analysis.

The biggest advantage is repeatability. Instead of cleaning the same file manually every month, you create a transformation process once and refresh it.

2️⃣ Why Should a Data Analyst Learn Power Query?

Imagine your company sends you this file every month:

January.xlsx, February.xlsx, March.xlsx, April.xlsx...

Every file contains 50,000 rows, extra spaces, duplicates, incorrect date formats.

Without Power Query, you repeat the same cleaning every month.

With Power Query: Refresh → Transformations run again

3️⃣ Where Do You Find Power Query?

In modern Excel: Data → Get & Transform Data

Options: From Table/Range, From Workbook, From Text/CSV, From Folder, From Web, From Database

4️⃣ Understand the Power Query Workflow

Data Source → Connect → Power Query Editor → Clean → Transform → Validate → Load → Excel / Data Model → Analysis

Power Query records the transformation steps.

5️⃣ Import Data from Excel & CSV

Excel: Data → Get Data → From File → From Excel Workbook → Select sheet → Open in Power Query Editor

CSV: Data → From Text/CSV → Preview delimiter, headers, data types → Transform Data

6️⃣ Power Query Editor

Left side: Queries

Middle: Data preview

Right side: Applied Steps

Example Applied Steps:

Source → Changed Type → Removed Columns → Filtered Rows → Removed Duplicates → Renamed Columns → Added Custom Column

7️⃣ Changing Data Types

Correct data types are critical.

Order ID → Whole Number, Order Date → Date, Sales → Decimal Number, Customer → Text

Use the data-type icon to change it.

8️⃣ Remove Duplicates

If Order ID should be unique, select the column and use: Remove Rows → Remove Duplicates

🔟 Important: Understand What a Duplicate Means

Don't automatically delete duplicates.

Ask: > Is this actually a duplicate?

Two records with same customer but different orders = Not a duplicate.

Same order appearing twice = Duplicate.

1️⃣1️⃣ Remove & Rename Columns

Remove unnecessary columns: Home → Remove Columns

Rename for clarity: CustNm → Customer Name, SlsAmt → Sales

1️⃣2️⃣ Filter Rows

Filtering in Power Query becomes part of the reusable query.

Example: Keep only orders from 2026, or North region, or Sales > 0

1️⃣3️⃣ Handle Missing Values

Never blindly replace missing values with zero.

A missing salary doesn't mean Salary = 0, it means it wasn't provided.

1️⃣4️⃣ Replace Values

Standardize inconsistent entries:

North, NORTH, north, N → North

Use: Replace Values

1️⃣5️⃣ Trim and Clean Text

Transform " John Smith " → "John Smith"

Trim whitespace, Clean non-printing characters, Change case

1️⃣6️⃣ Split Columns

John-Smith → First Name: John, Last Name: Smith

Use: Split Column → By Delimiter → "-"

1️⃣7️⃣ Merge Columns

John + Smith → John Smith

Use: Merge Columns with space separator

2️⃣0️⃣ Add Custom Columns

Sales: 100,000, Cost: 70,000 → Profit = Sales - Cost = 30,000

Profit Margin = Profit / Sales

2️⃣1️⃣ Conditional Columns

IF Sales >= 100000 THEN "High" ELSE IF Sales >= 50000 THEN "Medium" ELSE "Low"

Similar to Excel's IF()

2️⃣2️⃣ Merge Queries (The most important concept)

Sales: Product ID, Sales

Products: Product ID, Product, Category

Use: Merge Queries → Match Product ID → This is like a JOIN in SQL.

SQL: SELECT * FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;

2️⃣3️⃣ Append Queries

Merge = Add columns by matching keys

Append = Add rows by stacking

Jan (1001, 1002) + Feb (1003, 1004) → 1001, 1002, 1003, 1004

2️⃣4️⃣ Group By

Region: North 50K, North 70K → Group by Region, Sum Sales → North 120K

Similar to SQL GROUP BY

2️⃣5️⃣ Pivot and Unpivot

This is critical for reports designed for humans:

Before:

Region | Jan | Feb | Mar

North | 50K | 60K | 70K

After Unpivot:

Region | Month | Sales

North | Jan | 50K

North | Feb | 60K

This structure is much better for analysis.

2️⃣6️⃣ Applied Steps = Your Superpower

Source → Changed Type → Removed Columns → Trimmed Text → Removed Duplicates → Filtered Rows → Added Profit → Merged Products

When new data arrives, just Refresh.

🧪 Practical Interview Challenge

Messy file with: Duplicate Order IDs, Extra spaces, Sales as text, Missing regions, Product info in another file

Strong approach:

1. Import into Power Query

2. Set correct data types

3. Trim and clean text

4. Investigate duplicates

5. Handle missing regions per business rules

6. Merge Product lookup table

7. Add Profit column

8. Filter invalid records

9. Review Applied Steps

10. Load cleaned dataset

🏆 Key Lesson

Instead of: > "How do I clean this file?"

Think: > "How do I build a repeatable process that cleans this type of data every time?"

That's the difference between manually manipulating spreadsheets and building a professional analytics workflow.

Remember:

Merge = Add columns by matching data

Append = Add rows

Group By = Summarize

Unpivot = Convert columns into rows

Applied Steps = Record your process

Refresh = Run the process again

Double Tap ❤️ For Part-11

🚀 Data Analyst Roadmap — Part 11

🗄️ SQL — Level 1: SQL Fundamentals & Databases

You've completed the major Excel section of the roadmap.

Now we're moving to one of the most important skills for a Data Analyst: SQL

If Excel helps you analyze spreadsheet-based data, SQL helps you work directly with data stored in databases.

A Data Analyst should be able to use SQL to:

• Retrieve data

• Filter records

• Sort results

• Summarize information

• Join tables

• Find trends

• Calculate KPIs

• Investigate business problems

1️⃣ What Is SQL?

SQL stands for: Structured Query Language

It's a language used to communicate with relational databases.

For example, suppose a company stores millions of sales records in a database.

Instead of opening a huge spreadsheet, you can ask the database:

"Give me all sales from the North region."

Or:

"What was total revenue last month?"

Or:

"Which 10 products generated the most revenue?"

SQL allows you to ask these questions directly.

2️⃣ Why Is SQL Important for Data Analysts?

Imagine a company has:

50 million transactions.

Excel isn't the right tool for storing and querying all that information.

The data may be stored in a database such as:

• PostgreSQL

• MySQL

• Microsoft SQL Server

• Oracle Database

• Snowflake

• BigQuery

As a Data Analyst, you may connect to the database and use SQL to extract the data you need.

A typical workflow looks like:

Database

↓

SQL Query

↓

Required Data

↓

Analysis

↓

Dashboard / Report

↓

Business Decision

3️⃣ What Is a Database?

A database is a system used to store and manage data.

For example, an e-commerce company might have:

• Customers

• Products

• Orders

• Payments

• Employees

Each represents a different type of information.

Instead of putting everything into one enormous table, relational databases typically organize related information into separate tables.

4️⃣ What Is a Table?

A table is a structured collection of data organized into:

Rows + Columns

For example:

Customers

Customer_ID Customer_Name City

101 John Mumbai

102 Sarah Pune

103 Mike Delhi

Each row represents one customer.

Each column represents an attribute.

This should look familiar from Excel.

5️⃣ Rows vs Columns

Just like Excel:

Row

Represents a record.

Example:

101 | John | Mumbai

represents one customer.

Column

Represents an attribute.

For example:

• Customer_ID

• Customer_Name

• City

A useful rule:

One row = one record

One column = one attribute

6️⃣ What Is a Primary Key?

A Primary Key uniquely identifies each record in a table.

For example:

Customer_ID Customer_Name

101 John

102 Sarah

103 Mike

Here:

Customer_ID

can be the primary key.

Each customer should have a unique ID.

101 → John

102 → Sarah

103 → Mike

You shouldn't have two different customers with the same primary key.

7️⃣ What Is a Foreign Key?

A Foreign Key is a column used to establish a relationship between tables.

Suppose:

Customers

Customer_ID Customer_Name

101 John

102 Sarah

Orders

Order_ID Customer_ID Sales

5001 101 50,000

5002 102 70,000

5003 101 30,000

Here:

Customers.Customer_ID

is the primary key.

Orders.Customer_ID

can be a foreign key.

This allows us to connect orders to customers.

8️⃣ Understanding Relationships

The relationship is:

Customers

Customer_ID

↓

Orders

One customer can have multiple orders.

For example:

John

↓

Order 5001

Order 5003

Order 5010

This is a:

One-to-Many relationship

It's one of the most important database concepts for Data Analysts.

9️⃣ What Is a Relational Database?

A relational database stores data in related tables.

For example:

Customers

↓

Orders

↓

Order Details

↓

Products

Instead of storing the customer's name repeatedly in every order, the database can store:

Customer_ID

and retrieve the customer information through relationships.

This helps reduce unnecessary duplication.

🔟 What Is SQL Syntax?

SQL queries generally consist of keywords and expressions.

For example:

SELECT *

FROM Customers;

This asks:

Return all columns from the Customers table.

Let me break it down.

SELECT: Specifies what you want to retrieve.

FROM: Specifies the table.

Customers: The table you're querying.

1️⃣1️⃣ SELECT

SELECT is one of the first SQL commands you need to learn.

Suppose you have:

Employees

Employee_ID Name Department Salary

101 John IT 75,000

102 Sarah HR 60,000

103 Mike Finance 82,000

To retrieve all columns:

SELECT *

FROM Employees;

1️⃣2️⃣ Selecting Specific Columns

You don't always need every column.

Suppose you only want:

Name and Department

Use:

SELECT Name, Department

FROM Employees;

Result:

Name Department

John IT

Sarah HR

Mike Finance

This is generally better than using SELECT * when you only need specific fields.

1️⃣3️⃣ Why Avoid SELECT * in Production Queries?

You may see beginners writing:

SELECT *

FROM Employees;

all the time.

It's useful while learning and exploring data.

But in production queries, explicitly selecting the required columns is often better because:

• It makes the query clearer

• It avoids retrieving unnecessary data

• It can reduce data transfer

• It makes downstream dependencies more predictable

For example:

SELECT Employee_ID, Name, Salary

FROM Employees;

is more intentional.

1️⃣4️⃣ WHERE

WHERE filters records.

Suppose you want employees from IT.

SELECT *

FROM Employees

WHERE Department = 'IT';

Result:

Employee_ID Name Department Salary

101 John IT 75,000

The database only returns records satisfying the condition.

1️⃣5️⃣ Filtering Numeric Values

Suppose you want employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Salary > 70000;

Result:

Employee_ID Name Department Salary

101 John IT 75,000

103 Mike Finance 82,000

1️⃣6️⃣ Comparison Operators

You should know these operators:

Operator Meaning

= Equal to

<> Not equal to

Greater than

< Less than

= Greater than or equal

<= Less than or equal

Examples:

WHERE Salary >= 80000

WHERE Department <> 'HR'

1️⃣7️⃣ AND

AND requires all conditions to be true.

Suppose you want:

IT employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Department = 'IT'

AND Salary > 70000;

The record must satisfy both conditions.

Think:

IT

AND

Salary > 70,000

1️⃣8️⃣ OR

OR requires at least one condition to be true.

Suppose you want:

IT or Finance employees.

SELECT *

FROM Employees

WHERE Department = 'IT'

OR Department = 'Finance';

Both departments will be included.

1️⃣9️⃣ IN

When checking multiple values, IN makes your query cleaner.

Instead of:

WHERE Department = 'IT'

OR Department = 'Finance'

OR Department = 'HR'

you can write:

WHERE Department IN ('IT', 'Finance', 'HR');

This is easier to read and maintain.

2️⃣0️⃣ NOT IN

You can exclude multiple values.

SELECT *

FROM Employees

WHERE Department NOT IN ('HR', 'Finance');

This returns employees who aren't in those departments.

2️⃣1️⃣ BETWEEN

BETWEEN checks whether a value falls within a range.

For example:

SELECT *

FROM Employees

WHERE Salary BETWEEN 50000 AND 80000;

This returns salaries within the specified range.

For numeric data, this is often useful for:

• Salary ranges

• Sales ranges

• Age ranges

• Scores

• Transaction values

2️⃣2️⃣ LIKE

LIKE is used for pattern matching.

Suppose you want employees whose names start with J.

SELECT *

FROM Employees

WHERE Name LIKE 'J%';

% means:

Any number of characters.

So this could match:

• John

• James

• Jennifer

2️⃣3️⃣ LIKE with Wildcards

•

Starts with J

LIKE 'J%'

•

Ends with n

LIKE '%n'

•

Contains "oh"

LIKE '%oh%'

Wildcards are extremely useful when searching text data.

2️⃣4️⃣ DISTINCT

DISTINCT removes duplicate values from the result.

Suppose your employee table contains:

• IT

• HR

• IT

• Finance

• HR

• IT

Use:

SELECT DISTINCT Department

FROM Employees;

Result:

IT

HR

Finance

This is useful for discovering categories in a dataset.

2️⃣5️⃣ ORDER BY

ORDER BY sorts your results.

Suppose you want employees with the highest salary first.

SELECT *

FROM Employees

ORDER BY Salary DESC;

DESC means:

Descending

Highest → Lowest

2️⃣6️⃣ ASC

ASC means ascending.

SELECT *

FROM Employees

ORDER BY Salary ASC;

Lowest → Highest

Ascending is generally the default sort direction.

2️⃣7️⃣ LIMIT / TOP

The syntax depends on the database system.

In systems such as PostgreSQL and MySQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 5;

This returns the top 5 employees by salary.

In SQL Server, you would commonly use:

SELECT TOP 5 *

FROM Employees

ORDER BY Salary DESC;

This is an important point:

SQL is a language, but different database systems have slightly different syntax.

2️⃣8️⃣ Aliases

Aliases give columns or tables temporary names within a query.

For example:

SELECT

Name AS Employee_Name,

Salary AS Annual_Salary

FROM Employees;

The result displays:

Employee_Name Annual_Salary

John 75,000

Sarah 60,000

Aliases make results easier to understand.

2️⃣9️⃣ SQL Comments

You can add comments to explain your queries.

For example:

-- Get employees earning more than 70,000

SELECT Name, Salary

FROM Employees

WHERE Salary > 70000;

Comments don't affect the query result.

They're useful when queries become complex.

🧪 Practical Interview Challenge

Suppose you have:

Employees

ID Name Department Salary

101 John IT 75,000

102 Sarah HR 60,000

103 Mike Finance 82,000

104 David IT 90,000

105 Alice HR 65,000

Q1. Retrieve all employees.

SELECT *

FROM Employees;

Q2. Retrieve only names and salaries.

SELECT Name, Salary

FROM Employees;

Q3. Find employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Salary > 70000;

Q4. Find IT employees.

SELECT *

FROM Employees

WHERE Department = 'IT';

Q5. Find IT or Finance employees.

SELECT *

FROM Employees

WHERE Department IN ('IT', 'Finance');

Q6. Sort employees by salary from highest to lowest.

SELECT *

FROM Employees

ORDER BY Salary DESC;

Q7. Find the top 3 highest-paid employees.

PostgreSQL/MySQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 3;

SQL Server:

SELECT TOP 3 *

FROM Employees

ORDER BY Salary DESC;

Q8. List unique departments.

SELECT DISTINCT Department

FROM Employees;

🏆 Double Tap ❤️ For More

🚀 Data Analyst Roadmap — Part 12

🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING

Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.

This is one of the most important SQL skills for Data Analysts.

In real interviews and jobs, you'll frequently be asked questions like:

What is the total sales by region?

What is the average salary by department?

How many customers are in each city?

Which products generated more than ₹10 lakh in sales?

To answer these questions, you need:

Aggregate Functions + GROUP BY + HAVING

1️⃣ What Are Aggregate Functions?

Aggregate functions perform calculations across multiple rows and return a summarized result.

The most important ones are:

SUM()

COUNT()

AVG()

MIN()

MAX()

Think of them as the SQL equivalent of the basic Excel functions you learned earlier.

2️⃣ SUM()

SUM() calculates the total of a numeric column.

Suppose you have:

Order_ID: 1001, Sales: 50,000

Order_ID: 1002, Sales: 70,000

Order_ID: 1003, Sales: 30,000

Query:

SELECT SUM(Sales) AS Total_Sales
FROM Orders;

Result:

Total_Sales = 150,000

Business question

What is our total revenue?

Answer → SUM()

3️⃣ COUNT()

COUNT() counts records.

SELECT COUNT(*) AS Total_Orders
FROM Orders;

If there are 10,000 orders:

Total_Orders = 10,000

Why COUNT(*)?

COUNT(*) counts rows.

This is often useful when you want the total number of records.

4️⃣ COUNT(Column)

You can also count values in a specific column.

SELECT COUNT(Customer_ID) AS Customer_Count
FROM Orders;

One important distinction:

COUNT(column) generally doesn't count NULL values.

Whereas:

COUNT(*)

counts rows regardless of whether individual columns contain NULLs.

5️⃣ COUNT(DISTINCT)

Suppose your Orders table contains:

Order 1001 → Customer 101

Order 1002 → Customer 102

Order 1003 → Customer 101

Order 1004 → Customer 103

There are:

4 orders

but only:

3 unique customers

Use:

SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;

Result:

3

This is extremely important in analytics.

6️⃣ AVG()

AVG() calculates the average.

Suppose salaries are:

50,000, 60,000, 70,000

Query:

SELECT AVG(Salary) AS Average_Salary
FROM Employees;

Result:

60,000

Business questions

What is the average order value?

What is the average employee salary?

What is the average product price?

Answer → AVG()

7️⃣ MIN()

MIN() returns the smallest value.

SELECT MIN(Salary) AS Minimum_Salary
FROM Employees;

Example result:

35,000

Useful for:

Minimum salary

Lowest sales

Earliest date

Lowest transaction value

8️⃣ MAX()

MAX() returns the largest value.

SELECT MAX(Salary) AS Maximum_Salary
FROM Employees;

Result:

150,000

Useful for:

Highest salary

Highest sales

Largest transaction

Latest date

9️⃣ Using Multiple Aggregate Functions

You can use several aggregate functions in one query.

SELECT
    SUM(Sales) AS Total_Sales,
    AVG(Sales) AS Average_Sales,
    MIN(Sales) AS Minimum_Sales,
    MAX(Sales) AS Maximum_Sales,
    COUNT(*) AS Total_Orders
FROM Orders;

The process is:

WHERE → Filter rows

GROUP BY → Create groups

SUM → Calculate totals

HAVING → Filter groups

This sequence is fundamental to SQL analysis.

1️⃣9️⃣ ORDER BY with GROUP BY

You can sort aggregated results.

Suppose you want regions with the highest sales first:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC;

Result:

North: 500,000, South: 350,000, West: 200,000, East: 150,000

2️⃣0️⃣ Top 3 Regions

You can combine:

GROUP BY + ORDER BY + LIMIT

For example, in PostgreSQL/MySQL:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC
LIMIT 3;

This answers:

"Which three regions generated the most sales?"

2️⃣1️⃣ GROUP BY Dates

Suppose you have:

Order_Date and Sales

You might want:

Total sales by year.

The exact date function varies by database system.

For example, in PostgreSQL:

SELECT
    EXTRACT(YEAR FROM Order_Date) AS Sales_Year,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Sales_Year;

Result:

2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000

2️⃣2️⃣ Grouping by Month

In PostgreSQL, you can use:

SELECT
    DATE_TRUNC('month', Order_Date) AS Sales_Month,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Sales_Month;

This creates monthly sales totals.

Different SQL platforms have different date functions, so always check the database you're working with.

2️⃣3️⃣ Calculate Average Order Value

A common business KPI is:

Average Order Value (AOV)

A simple version is:

SELECT
    SUM(Sales) / COUNT(*) AS Average_Order_Value
FROM Orders;

If each row represents exactly one order.

If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:

SELECT
    SUM(Sales) / COUNT(DISTINCT Order_ID) AS Average_Order_Value
FROM Orders;

This distinction is extremely important.

2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics

Suppose a customer places multiple orders:

Customer 101 → Orders 5001, 5002

Customer 102 → Order 5003

Customer 103 → Orders 5004, 5005

Total orders: 5

Unique customers: 3

Query:

SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;

Result: 3

This is commonly used for metrics such as:

Active customers

Unique users

Unique accounts

Distinct orders

Distinct products

2️⃣5️⃣ Conditional Aggregation

One powerful technique is combining CASE WHEN with aggregate functions.

For example:

Count how many orders were above ₹50,000.

SELECT
    SUM(
        CASE
            WHEN Sales > 50000 THEN 1
            ELSE 0
        END
    ) AS High_Value_Orders
FROM Orders;

This allows you to create customized metrics.

You'll use this technique much more in advanced SQL.

2️⃣6️⃣ Common SQL Analytical Pattern

A very common query structure is:

SELECT
    Dimension,
    AGGREGATE_FUNCTION(Metric) AS KPI
FROM Table
WHERE Condition
GROUP BY Dimension
HAVING Aggregate_Condition
ORDER BY KPI DESC;

For example:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;

This produces a compact business summary.

For example:

Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200

🔟 Why Do We Need GROUP BY?

Aggregate functions give you an overall summary.

But what if the business asks:

"What are total sales for each region?"

You need to divide the data into groups.

That's what GROUP BY does.

1️⃣1️⃣ Basic GROUP BY

Suppose:

North: 50,000 and 70,000 → Total 120,000

South: 40,000 and 60,000 → Total 100,000

West: 80,000 → Total 80,000

Query:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region;

Result:

North = 120,000, South = 100,000, West = 80,000

Now you've answered:

"How much did each region sell?"

1️⃣2️⃣ GROUP BY Department

Suppose you have:

John - IT - 75,000

Sarah - HR - 60,000

Mike - IT - 82,000

David - Finance - 90,000

Alice - HR - 65,000

Query:

SELECT
    Department,
    AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department;

Result:

Finance: 90,000, HR: 62,500, IT: 78,500

1️⃣3️⃣ GROUP BY with COUNT()

Question:

How many employees are in each department?

SELECT
    Department,
    COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department;

Result:

IT: 2, HR: 2, Finance: 1

1️⃣4️⃣ GROUP BY with Multiple Columns

You can group by more than one column.

Suppose your sales data contains:

North Electronics: 80,000

North Furniture: 40,000

South Electronics: 70,000

South Furniture: 50,000

Query:

SELECT
    Region,
    Category,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region, Category;

Result:

North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000

This lets you analyze combinations of dimensions.

1️⃣5️⃣ GROUP BY vs PivotTable

This is an important connection.

In Excel:

Region → Rows

Sales → Values

In SQL:

SELECT
    Region,
    SUM(Sales)
FROM Orders
GROUP BY Region;

The analytical concept is very similar.

You're grouping records and calculating an aggregate.

1️⃣6️⃣ HAVING

Now suppose you want:

"Show only regions where total sales are greater than ₹100,000."

You can't simply use WHERE on the aggregate result.

You use: HAVING

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;

Result:

North = 120,000

1️⃣7️⃣ WHERE vs HAVING

This is a very common SQL interview question.

WHERE

Filters individual rows before grouping.

Example:

SELECT *
FROM Orders
WHERE Region = 'North';

HAVING

Filters groups after aggregation.

Example:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;

Remember:

WHERE → Filter rows

HAVING → Filter groups

1️⃣8️⃣ WHERE + GROUP BY + HAVING

You can use all three.

Question:

Find regions where 2026 sales exceed ₹100,000.

Conceptually:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
  AND Order_Date < '2027-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000;

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.

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

𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝘁𝗼 𝗙𝘂𝘁𝘂𝗿𝗲-𝗣𝗿𝗼𝗼𝗳 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 😍

🔥 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:

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:

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 = NULL

This is incorrect for testing NULL.

Instead, use:

WHERE Email IS NULL

To find non-NULL values:

WHERE Email IS NOT NULL

1️⃣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.

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) → NULL

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.

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:

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