Data Analytics

🚀 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.

❤2

3.12K views12:23

Data Analytics

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

❤9👍1

3.28K viewsedited 12:23

Data Analytics

🚀 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.

❤4

3K views12:34

Data Analytics

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.

❤1

3.23K views12:34

Data Analytics

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

❤24

3.95K views12:34

Data Analytics

🚀 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;

❤5

2.42K views05:32

Data Analytics

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;

1.7K views05:32

Data Analytics

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;

❤4

1.96K views05:32

Data Analytics

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

❤3👍1

2.47K viewsedited 05:32

Data Analytics

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

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

❤1

2.17K views11:04

Data Analytics

🚀 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;

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;

1.91K views14:49

Data Analytics

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;

👏1

1.65K views14:49

Data Analytics

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;

2.56K views14:50

Data Analytics

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

❤14👍1

3.3K viewsedited 14:50

Data Analytics

🚀 𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍

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!

❤3

2.91K views06:18

Data Analytics

🚀 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

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

❤3

2.42K views15:35

Data Analytics

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;

❤2

3.15K views15:35

Data Analytics

Q5. Find customers whose total sales exceed ₹50,000.

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

❤11

2.88K viewsedited 15:35

Data Analytics

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

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.

❤3👍3

2.03K views05:26

Data Analytics

The CTE is often easier to read when the query becomes complex.

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
);

❤2

2.08K views05:26

About

Blog

Apps

Platform