🚀 Data Analyst Roadmap — Part 19

🧠 SQL Level 9 — Query Performance & Optimization

Knowing how to write a SQL query is one skill. Knowing how to write a query that is correct, efficient, scalable, and easy to maintain is another.

As a Data Analyst, you may eventually work with tables containing:

📊 Millions of rows

📦 Large transaction datasets

👥 Millions of customers

📅 Years of historical data

A query that works perfectly on 10,000 rows may become extremely slow on 100 million rows. That's why understanding SQL performance matters.

🔹 1. What Is Query Optimization?

Query optimization means improving a query so that it:

✅ Returns the correct result

✅ Uses fewer resources

✅ Processes less unnecessary data

✅ Executes faster

✅ Scales better as data grows

🔹 2. Select Only the Columns You Need

Avoid:

SELECT * FROM Orders;

when you only need:

SELECT Order_ID, Customer_ID, Sales FROM Orders;

SELECT * can:

• Read unnecessary columns

• Increase data transfer

• Make queries harder to maintain

• Become problematic when tables change

For analytical work, explicitly selecting required columns is usually better.

🔹 3. Filter Data Early

Suppose you only need 2026 orders:

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

Filtering reduces the amount of data that later operations need to process.

🔹 4. Understand Indexes

An index is a database structure that can help locate rows more efficiently. Think of it like the index of a book.

Without an index: Database → potentially scan many rows

With a useful index: Database → locate relevant data more efficiently

Indexes can be especially useful for columns frequently used in: WHERE, JOIN, ORDER BY, and sometimes GROUP BY.

Example:

CREATE INDEX idx_orders_customer ON Orders(Customer_ID);

⚠️ Index syntax and behavior vary by database.

🔹 5. Indexes Are Not Always Better

Indexes have costs. They can:

Consume storage, slow down INSERT, UPDATE, DELETE, and require maintenance.

Therefore:

Don't create indexes on every column. Database systems and workloads determine which indexes are useful.

🔹 6. Index Columns Used for JOINs

SELECT c.Customer_Name, o.Sales 
FROM Customers c 
JOIN Orders o ON c.Customer_ID = o.Customer_ID;

The join depends on: Customer_ID.

Appropriate indexing can improve join performance, especially for large tables. But the database optimizer decides whether an index actually provides a benefit.

🔹 7. Avoid Functions on Filtered Columns When Possible

Consider:

WHERE YEAR(Order_Date) = 2026

A range condition is often preferable:

WHERE Order_Date >= '2026-01-01' AND Order_Date < '2027-01-01'

Why?

Applying a function to every row can make it harder for some database systems to use an index efficiently. This is called maintaining sargability.

🔹 8. What Is Sargability?

A condition is generally considered sargable when the database can efficiently use an index to find matching rows.

Compare:

WHERE Order_Date >= '2026-01-01' 

with:

WHERE YEAR(Order_Date) = 2026

The first form usually gives the optimizer more opportunity to use an index on Order_Date.

🔹 9. Use "EXPLAIN"

One of the most important tools for understanding query performance is:

EXPLAIN 
SELECT * FROM Orders WHERE Customer_ID = 1001;

Depending on the database, you may also see:

EXPLAIN ANALYZE

These tools can show how the database plans to execute or actually executes the query.

You may encounter concepts such as: Table scan, Index scan, Index seek, Join strategy, Sort, Aggregate, Estimated rows, Actual rows, Execution cost.

🔹 10. Table Scan vs Index Access

A table scan may require reading a large portion of a table. An index-based access path can sometimes locate matching records much more efficiently.

But: ⚠️ A table scan is not automatically bad.

If you need most of the table, scanning it may actually be the best strategy.

The optimizer chooses based on: Data size + statistics + indexes + query conditions + database engine.

🔹 11. Avoid Unnecessary "DISTINCT"

This query:

SELECT DISTINCT Customer_ID FROM Orders; 

may be perfectly valid.

But don't use DISTINCT simply to hide duplicate rows created by an incorrect join.

🔹 12. Be Careful With JOIN Multiplication

Suppose:

Customer A → 10 Orders and each order has 5 Order Items

Joining customers → orders → order items can produce many rows.

If you then calculate: SUM(Order_Sales) you could accidentally multiply the order-level sales.

This is a logic problem, not just a performance problem. Always understand the grain before joining and aggregating.

🔹 13. "UNION" vs "UNION ALL"

"UNION" removes duplicates.

SELECT Customer_ID FROM Current_Customers 
UNION 
SELECT Customer_ID FROM Previous_Customers;

"UNION ALL" simply combines the results:

SELECT Customer_ID FROM Current_Customers 
UNION ALL 
SELECT Customer_ID FROM Previous_Customers;

If you don't need duplicate removal, UNION ALL is generally preferable because it avoids the additional deduplication work.

🔹 14. Avoid Repeating Expensive Logic

Suppose the same complex calculation appears multiple times.

Instead of repeating it, consider using: CTEs, Derived tables, Temporary tables, Views, Pre-aggregated tables.

The objective is: Calculate expensive logic only when necessary.

🔹 15. CTEs and Performance

CTEs make SQL much easier to organize:

WITH Customer_Sales AS (
    SELECT Customer_ID, SUM(Sales) AS Total_Sales 
    FROM Orders 
    GROUP BY Customer_ID
) 
SELECT * FROM Customer_Sales WHERE Total_Sales > 10000;

A CTE is primarily a query organization technique. Whether it improves performance depends on the database engine, query structure, materialization behavior, and optimizer.

🔹 16. Reduce Data Before Expensive Operations

A useful analytical pattern is:

Raw Data → Filter → Select Required Columns → Join → Aggregate → Window Function → Final Result

The exact optimal order depends on the query, but the principle is: Don't process more data than necessary.

🔹 17. Avoid Correlated Subqueries When a Better Approach Exists

A correlated subquery may conceptually execute in relation to each outer row:

SELECT e.Employee, e.Salary 
FROM Employees e 
WHERE e.Salary > (
    SELECT AVG(e2.Salary) 
    FROM Employees e2 
    WHERE e2.Department = e.Department
);

Sometimes a CTE + join or window function can express the same business logic more efficiently:

WITH Employee_Data AS (
    SELECT 
        Employee, 
        Department, 
        Salary, 
        AVG(Salary) OVER (PARTITION BY Department) AS Avg_Dept_Salary 
    FROM Employees
) 
SELECT Employee, Department, Salary 
FROM Employee_Data 
WHERE Salary > Avg_Dept_Salary;

🔹 18. Query Readability Also Matters

A query can be technically fast but difficult to understand.

Bad analytical SQL often contains:

❌ Unclear aliases, repeated logic, huge nested queries, unnecessary columns, unnecessary joins, no explanation of business logic

Good SQL should be:

✅ Correct, efficient, readable, maintainable, easy to troubleshoot

🔹 19. SQL Optimization Checklist

Before finalizing a query, ask:

1. Do I need all these columns?

2. Am I processing unnecessary rows?

3. Are my joins using the correct keys?

4. Did the join change the grain?

5. Am I accidentally multiplying values?

6. Can a date filter be written as a range?

7. Do I really need "DISTINCT"?

8. Could "UNION ALL" be used instead of "UNION"?

9. Can I inspect the execution plan?

10. Will this query still perform well on a much larger dataset?

🎯 SQL Interview Challenge

Question:

A query takes 30 seconds:

SELECT * FROM Orders WHERE YEAR(Order_Date) = 2026;

How could you improve it?

A better approach is:

SELECT Order_ID, Customer_ID, Order_Date, Sales 
FROM Orders 
WHERE Order_Date >= '2026-01-01' 
  AND Order_Date < '2027-01-01';

Why?

✔ Avoids unnecessary columns

✔ Uses a range filter

✔ Can be more index-friendly

✔ Clearly defines the required period

Then use EXPLAIN/EXPLAIN ANALYZE to verify the actual execution plan.

🧠 Double Tap ❤️ For More

🚀 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗟𝗮𝗻𝗱 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯𝘀 𝗶𝗻 𝟮𝟬𝟮𝟲😍

💰 Highest Salary: ₹41 LPA
📈 Average Salary: ₹7.4 LPA
🎓 2,000+ Students Placed
🏢 500+ Hiring Partners

💻 Full Stack :- https://pdlink.in/3SuUeuD

📊 Data Analytics :- https://pdlink.in/45vk5ph

💫AI Engineering :- https://pdlink.in/4fWJVID

🔥 Take the first step towards your high-paying tech career in 2026!

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍

💫Accelerate your career in Data Science

💫Discover the skills, tools and career roadmap needed to enter this high-demand field.

🔥 Beginner-friendly online session—no prior experience required!

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/46adC3l

(Only few slots left )

📅 Date: September 11, 2026
⏰ Time: 7:00 PM

🚀 Data Analyst Roadmap — Part 20

🧠 SQL Level 10 — Cohort Analysis, Retention & Customer Analytics

Now we're moving from writing SQL queries to using SQL for real analytical problems.

Customer analytics is one of the most important areas because businesses want to know:

👥 Who are our customers?

🛒 When did they first purchase?

🔄 Do they come back?

📉 When do they stop returning?

💰 Which customers generate most revenue?

📊 How does behavior change over time?

One of the most powerful techniques for this is Cohort Analysis.

🔹 1. What Is Cohort Analysis?

A cohort is a group who share a common starting point.

• Jan 2026 Cohort = first purchase in Jan 2026

• Feb 2026 Cohort = first purchase in Feb 2026

Instead of mixing everyone, we track each group over time.

🔹 2. Why It Matters

Suppose total monthly customers are increasing.

That sounds positive.

But what if new customers are increasing while existing customers stop returning?

A simple monthly report may hide this problem.

Cohort analysis separates New vs Returning customers.

This makes retention problems much easier to identify.

🔹 3. Step 1 — Find Each Customer's First Purchase

SELECT
    Customer_ID,
    MIN(Order_Date) AS First_Order_Date
FROM Orders
GROUP BY Customer_ID;

This gives us the first purchase date for every customer.

Customer | First Order
C101     | 2026-01-10
C102     | 2026-01-18
C103     | 2026-02-05

🔹 4. Step 2 — Assign a Cohort Month

We can convert the first purchase into a month-level cohort.

First Purchase Date → Cohort Month
C101 → 2026-01
C103 → 2026-02

The exact month-truncation syntax varies between SQL databases.

🔹 5. Step 3 — Join Cohort Back to Orders

Now we need both:

Customer's cohort

and

Customer's subsequent activity

WITH Customer_Cohorts AS (
    SELECT Customer_ID, MIN(Order_Date) AS First_Order_Date
    FROM Orders GROUP BY Customer_ID
)
SELECT o.Customer_ID, c.First_Order_Date, o.Order_Date, o.Sales
FROM Orders o
JOIN Customer_Cohorts c ON o.Customer_ID = c.Customer_ID;

Now every transaction knows which cohort the customer belongs to.

🔹 6. Cohort Month vs Activity Month

• Cohort Month: When first purchased

• Activity Month: When purchase happened

Customer | Cohort | Activity
C101     | Jan    | Jan
C101     | Jan    | Feb
C101     | Jan    | Mar

🔹 7. Measuring Retention

Retention measures how many customers from a cohort remain active in later periods.

Retention = Active in Period / Original Cohort * 100

• Jan cohort: 100 customers

• Feb: 60 active → 60%

• Mar: 40 active → 40%

🔹 8. Retention Month

Months Since Cohort = Activity - Cohort

Eg:

Customer | Cohort | Activity | Months_Since_Cohort
C101     | Jan    | Jan      | 0
C101     | Jan    | Feb      | 1
C101     | Jan    | Mar      | 2

🔹 9. Cohort Retention Matrix

Conceptually, the final result may look like:

Cohort | Month0 | Month1 | Month2 | Month3
Jan    | 100%   | 60%    | 40%    | 30%
Feb    | 100%   | 65%    | 45%    | —
Mar    | 100%   | 70%    | —      | —

This is often called a cohort retention matrix.

It immediately shows whether newer customer cohorts are retaining better or worse.

🔹 10. Customer Lifetime Value (CLV)

Another important customer metric is Customer Lifetime Value (CLV/LTV).

A simplified version can be based on:

Total Revenue Generated by Customer

A more advanced business model may consider:

• Revenue

• Gross margin

• Purchase frequency

• Retention

• Customer lifespan

• Acquisition cost

🔹 11. Average Order Value (AOV)

A basic customer metric is:

Average Order Value = Total Sales ÷ Number of Orders

In SQL:

SELECT Customer_ID, SUM(Sales) / COUNT(DISTINCT Order_ID) AS AOV
FROM Orders GROUP BY Customer_ID;

This tells us how much a customer spends per order on average.

🔹 12. Purchase Frequency

We can also calculate the number of orders per customer:

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Number_of_Orders
FROM Orders GROUP BY Customer_ID;

Customers can then be segmented based on activity.

For example:

• 1 order → One-time customer

• 2–5 orders → Repeat customer

• 6+ orders → Highly active customer

⚠️ These thresholds are business rules, not universal definitions.

🔹 13. Recency

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date
FROM Orders GROUP BY Customer_ID;

Then compare the last order date with a chosen analysis date.

A customer who purchased recently is generally more active than someone whose last purchase was a long time ago.

🔹 14. RFM Analysis

• R → Recency: How recently?

• F → Frequency: How often?

• M → Monetary: How much?

Example:

Customer | Recency | Frequency | Monetary
C101     | 5 days  | 12 orders | ₹85,000
C102     | 20 days | 6 orders  | ₹42,000
C103     | 120 days| 2 orders  | ₹8,000

This allows businesses to identify:

⭐ High-value customers

🔄 Loyal customers

⚠️ Customers at risk

💤 Inactive customers

🔹 15. Segmentation With CASE

You can convert analytical metrics into business segments.

For example:

SELECT Customer_ID, Total_Sales,
  CASE 
    WHEN Total_Sales >= 50000 THEN 'High Value'
    WHEN Total_Sales >= 20000 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS Customer_Segment
FROM Customer_Sales;

This transforms numerical analysis into a business-friendly classification.

🔹 16. Repeat Customers

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Order_Count
FROM Orders GROUP BY Customer_ID
HAVING COUNT(DISTINCT Order_ID) > 1;

This finds customers with more than one order.

🔹 17. First vs Repeat Purchase

You can use ROW_NUMBER() to identify purchase sequence.

WITH Customer_Orders AS (
  SELECT Customer_ID, Order_ID, Order_Date,
  ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date) AS Purchase_Number
  FROM Orders
)
SELECT * FROM Customer_Orders;

Now:

Purchase_Number = 1 means the customer's first purchase.

Purchase_Number = 2 means the second purchase.

And so on.

This opens the door to deeper customer behavior analysis.

🔹 18. Time Between Purchases

SELECT Customer_ID, Order_Date,
LAG(Order_Date) OVER (PARTITION BY Customer_ID ORDER BY Order_Date) AS Previous_Order_Date
FROM Orders;

Now you can calculate the number of days between purchases.

→ Helps answer "How frequently do customers return?"

🔹 19. Churn Analysis

Churn means customers stop using or purchasing from a business.

SQL can help identify customers whose activity has fallen below a defined threshold.

For example:

Last Purchase → Days Since → Business Threshold → Active / At Risk / Inactive

SQL finds pattern, business defines churn.

🎯 Interview Challenge

Find customers with ≥3 orders and >50,000 spent:

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Order_Count, SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
HAVING COUNT(DISTINCT Order_ID) >= 3 AND SUM(Sales) > 50000;

🧠 Double Tap ❤️ For More

𝗧𝗼𝗽 𝟱 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗮𝗿𝗲𝗲𝗿 📊

Want to start a career in Data Science without spending money?

Here are 5 beginner-friendly learning resources covering essential skills such as Python, SQL, Machine Learning and hands-on projects.

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

https://pdlink.in/4ilAmok

🎯 Perfect for Students • Freshers • Beginners • Aspiring Data Scientists

💡 Learn → Practice → Build Projects → Create Your Portfolio

🚀 Data Analyst Roadmap — Part 18

🧠 SQL Level 8 — Advanced Analytical Queries & Business Problems

At this stage, you know the core SQL building blocks:

SELECT → WHERE → GROUP BY → HAVING → JOIN → CTE → Window Functions → Date Analysis

Now it's time to combine them.

Real Data Analyst work rarely asks: "Write a query using RANK()"

Instead, you'll get business questions like:

• "Which customers are becoming inactive?"

• "What are our top-selling products in each category?"

• "Which month had the highest revenue growth?"

The real skill is converting a business problem into SQL logic.

🔹 1. Start With the Business Question

Before writing SQL, identify:

• What are we measuring?

• At what level?

• Which tables contain the required data?

• What filters are needed?

• Do we need aggregation?

• Do we need ranking or comparison?

For example: "Find the top 3 products in every category."

Break it down: Product → Category → Sales → Rank within Category → Keep Top 3

🔹 2. Find the Correct Grain

Grain means: What does one row represent?

• Orders → one row per order

• Order_Items → one row per product within an order

• Customers → one row per customer

If you don't understand the grain, you can accidentally double-count revenue.

🔹 3. Revenue by Customer

SELECT
    c.Customer_ID,
    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_ID, c.Customer_Name;

🔹 4. Rank Customers by Revenue

WITH Customer_Sales AS (
    SELECT Customer_ID, SUM(Sales) AS Total_Sales
    FROM Orders GROUP BY Customer_ID
)
SELECT
    Customer_ID,
    Total_Sales,
    RANK() OVER (ORDER BY Total_Sales DESC) AS Sales_Rank
FROM Customer_Sales;

🔹 5. Top 3 Customers in Each Region

WITH Customer_Sales AS (
    SELECT Customer_ID, Region, SUM(Sales) AS Total_Sales
    FROM Orders GROUP BY Customer_ID, Region
),
Ranked_Customers AS (
    SELECT *, RANK() OVER (PARTITION BY Region ORDER BY Total_Sales DESC) AS Sales_Rank
    FROM Customer_Sales
)

SELECT * FROM Ranked_Customers WHERE Sales_Rank <= 3;

🔹 6. Finding the Second-Highest Salary

WITH Ranked_Employees AS (
    SELECT Employee, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
    FROM Employees
)

SELECT Employee, Salary FROM Ranked_Employees WHERE Salary_Rank = 2;

🔹 7. Find Products That Never Sold

SELECT p.Product_ID, p.Product_Name
FROM Products p
LEFT JOIN Order_Items oi ON p.Product_ID = oi.Product_ID
WHERE oi.Product_ID IS NULL;

🔹 8. Customers With No Orders

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;

🔹 9. Customers Above Average Spending

WITH Customer_Sales AS (
    SELECT Customer_ID, SUM(Sales) AS Total_Sales
    FROM Orders GROUP BY Customer_ID
)
SELECT Customer_ID, Total_Sales FROM Customer_Sales
WHERE Total_Sales > (SELECT AVG(Total_Sales) FROM Customer_Sales);

🔹 10. Month-over-Month Sales Growth

WITH Monthly_Sales AS (
    SELECT EXTRACT(YEAR FROM Order_Date) AS Year,
           EXTRACT(MONTH FROM Order_Date) AS Month,
           SUM(Sales) AS Total_Sales
    FROM Orders GROUP BY 1, 2
),
Comparison AS (
    SELECT Year, Month, Total_Sales,
           LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Sales
    FROM Monthly_Sales
)

SELECT Year, Month, Total_Sales, Previous_Sales,
       Total_Sales - Previous_Sales AS Sales_Change
FROM Comparison;

🔹 11. Calculate Growth Percentage

(Total_Sales - Previous_Sales) / NULLIF(Previous_Sales, 0) * 100

NULLIF() prevents division-by-zero errors.

🔹 12. Find the Latest Order for Every Customer

WITH Ranked_Orders AS (
    SELECT Customer_ID, Order_ID, Order_Date,
           ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date DESC) AS rn
    FROM Orders
)

SELECT Customer_ID, Order_ID, Order_Date FROM Ranked_Orders WHERE rn = 1;

🔹 13-14. Inactive Customers & Duplicates

Inactive = MAX(Order_Date) vs 90-day threshold.

Business defines the rule, SQL calculates it.

Detect duplicates:

WITH Duplicate_Check AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Customer_ID, Order_Date, Sales ORDER BY Order_ID) AS rn
    FROM Orders
)

SELECT * FROM Duplicate_Check WHERE rn > 1;

🔹 15. Combining Multiple Tables

SELECT c.Customer_ID, c.Customer_Name, p.Product_Name, oi.Quantity, oi.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
JOIN Order_Items oi ON o.Order_ID = oi.Order_ID
JOIN Products p ON oi.Product_ID = p.Product_ID;

⚠️ Every additional join can change the number of rows. Always check the grain.

🔹 16. The Most Important Analytical Pattern

1. Filter raw data → 2. Join tables → 3. Aggregate to correct grain → 4. Apply window functions → 5. Filter analytical result → 6. Present final output

💼 Real-World Business Problems to Practice

Sales: Top 5 products by revenue, Top products within each category, Month with highest sales, Revenue growth by month

Customers: Customers with no orders, declining purchases, most recent purchase, repeat customers, AOV per customer

Operations: Orders taking longer than expected, Products never sold, Duplicate transactions, Most active regions

🎯 SQL Interview Challenge: Find the highest-selling product in each category.

WITH Product_Sales AS (
    SELECT Product_ID, Category, SUM(Sales) AS Total_Sales
    FROM Product_Sales_Data GROUP BY Product_ID, Category
),
Ranked_Products AS (
    SELECT *, RANK() OVER (PARTITION BY Category ORDER BY Total_Sales DESC) AS Sales_Rank
    FROM Product_Sales
)
SELECT Product_ID, Category, Total_Sales FROM Ranked_Products WHERE Sales_Rank = 1;

🧠 SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ❤️ For More

🚀 𝗧𝗼𝗽 𝟯 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 🔥

💫 Artificial Intelligence (AI)
📊 Data Analytics
🔐 Cybersecurity

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

https://pdlink.in/4y2XyN1

🎯 Perfect for Students • Freshers • Beginners • Tech Enthusiasts

💡 Learn for FREE → Build Skills → Upgrade Your Career

🚀 Data Analyst Roadmap — Part 22

📊 Power BI Level 1 — Introduction to Power BI & Business Intelligence

After learning Excel and SQL, it's time to move into one of the most important tools in the modern Data Analyst toolkit:

Microsoft Power BI

Power BI helps you transform raw data into:

📊 Interactive dashboards

📈 Reports

🔍 Business insights

🎯 KPIs

📉 Trends and comparisons

💼 Decision-making tools

The goal isn't simply to create attractive charts.

The goal is to turn data into information that people can use to make better decisions.

🔹 1. What Is Power BI?

Power BI is Microsoft's business intelligence and

data visualization platform.

It allows you to:

• Connect to different data sources

• Clean and transform data

• Build data models

• Create calculations

• Create interactive visualizations

• Build dashboards and reports

• Share insights with others

A typical workflow looks like:

Data Sources

↓

Power Query

↓

Data Model

↓

DAX Calculations

↓

Visualizations

↓

Report / Dashboard

↓

Business Insights

🔹 2. Why Should a Data Analyst Learn Power BI?

Companies generate huge amounts of data.

But raw tables aren't easy for business users to understand.

Imagine giving management this:

Date | Region | Product | Sales | Profit

They may have thousands or millions of rows.

Instead, Power BI can turn that data into:

Total Sales: ₹12.5 Cr

Profit: ₹3.1 Cr

Top Region: West

Top Product: Product A

Monthly Trend: 📈

Sales by Region: Interactive chart

Now decision-makers can understand the situation quickly.

🔹 3. Power BI vs Excel

You already learned Excel in the earlier parts of this roadmap.

Both tools are valuable, but they are commonly used differently.

Excel| Power BI

Spreadsheet-based| BI platform

Great for ad-hoc analysis| Great for interactive reporting

Cell-based calculations| Model + DAX-based calculations

Manual dashboard updates can be common| Reports can refresh from data sources

Excellent for detailed individual analysis| Excellent for scalable business reporting

This doesn't mean:

Power BI replaces Excel.

Strong Data Analysts often use both.

🔹 4. Main Components of Power BI

You should become familiar with the Power BI ecosystem.

The major concepts you'll encounter are:

Power BI Desktop

Used to build reports, transform data, create models, and write DAX.

Power BI Service

Used for publishing, sharing, collaboration, refresh, and managing reports in the cloud.

Power BI Mobile

Allows users to view and interact with reports on mobile devices.

For a beginner, Power BI Desktop is where most hands-on learning starts.

🔹 5. Power BI Desktop Interface

When you open Power BI Desktop, you'll work with several important areas.

Report View

Used to create visualizations and report pages.

Data View

Allows you to inspect the data loaded into your model.

Model View

Shows relationships between tables.

These three views are important because Power BI isn't just a visualization tool.

It's also a data modeling and analytical environment.

🔹 6. Connecting Power BI to Data

Power BI can connect to many sources.

For example:

📁 Excel

📄 CSV

🗄️ SQL databases

☁️ Cloud data sources

🌐 Web sources

📊 Other business systems

A common beginner workflow is:

Excel/CSV → Power BI → Dashboard

Later, you'll learn how to connect Power BI directly to SQL databases and other enterprise sources.

🔹 7. Importing Data

A typical process is:

Home

↓

Get Data

↓

Choose Source

↓

Select Table/File

↓

Transform Data

↓

Load

Don't immediately start creating charts.

First understand:

What data did I load?

🔹 8. Power Query

Power Query is Power BI's data preparation and transformation engine.

You'll use it to:

• Remove unwanted columns

• Rename columns

• Change data types

• Remove duplicates

• Handle missing values

• Split columns

• Merge tables

• Append tables

• Filter rows

• Create transformation steps

This is similar to the Power Query work you learned in Excel.

The important idea is:

Power Query prepares the data before analysis.

🔹 9. Power Query vs DAX

This distinction is extremely important.

Power Query

→ Used mainly for data preparation and transformation

DAX

→ Used mainly for calculations and analysis inside the data model

Think:

Power Query

"Prepare the data."

DAX

"Analyze the data."

You'll learn both in detail in later parts.

🔹 10. Data Modeling

Suppose you have:

Sales

• Order_ID

• Customer_ID

• Product_ID

• Date

• Sales

Customers

• Customer_ID

• Customer_Name

• Region

Products

• Product_ID

• Product_Name

• Category

Date

• Date

• Month

• Quarter

• Year

Instead of putting everything into one giant table, Power BI can connect these tables through relationships.

This is called data modeling.

🔹 11. Relationships

For example:

Customers

Customer_ID

↓

Sales

↑

Product_ID

Products

The relationship allows Power BI to understand how tables are connected.

For example:

Customer → Sales

allows you to analyze sales by customer region.

Product → Sales

allows you to analyze sales by product category.

🔹 12. Fact Tables and Dimension Tables

A common data-modeling structure is the star schema.

At the center:

⭐ Fact Table

Around it:

🔹 Dimension Tables

Example:

Customers

Products — Sales — Date

Region

The "Sales" table contains business events or measurements.

The dimension tables provide descriptive context.

This structure is extremely important for Power BI.

🔹 13. Measures vs Columns

Another fundamental concept.

Suppose you have:

"Sales"

A calculated column could calculate something for each row.

A measure calculates a value based on the current report context.

Example measure:

Total Sales =

SUM(Sales[Sales_Amount])

When you put this measure into a visual, Power BI calculates it according to the selected:

• Region

• Product

• Date

• Customer

• Filters

This makes measures extremely powerful.

🔹 14. Your First Visualization

Suppose you have:

Month| Sales

Jan| 100,000

Feb| 120,000

Mar| 150,000

You could create a line chart.

The chart immediately communicates:

📈 Sales are increasing over time.

But visualization choice matters.

You shouldn't select a chart because it looks attractive.

Choose it because it communicates the business message clearly.

🔹 15. Common Power BI Visuals

You should become familiar with:

📊 Bar Chart

📈 Line Chart

🥧 Pie / Donut Chart

🔢 Card

📋 Table

📑 Matrix

🎯 KPI

🗺️ Map

📊 Column Chart

🎛️ Slicer

Each visual serves a different analytical purpose.

🔹 16. Cards

Cards are useful for displaying important KPIs.

For example:

┌─────────────────┐

│ TOTAL SALES │

│ ₹12.5 Cr │

└─────────────────┘

Other examples:

Total Profit

Total Customers

Total Orders

Average Order Value

A dashboard should make its most important KPIs easy to find.

🔹 17. Slicers

Slicers allow users to interactively filter a report.

For example:

Region:

[All ▼]

Year:

[2026 ▼]

Category:

[Electronics ▼]

Selecting a region can update multiple visuals on the report page.

This is one of the features that makes Power BI dashboards interactive.

🔹 18. Filters

Power BI provides filtering at different levels.

Common concepts include:

Visual-level filter

Affects one visual.

Page-level filter

Affects visuals on a particular page.

Report-level filter

Can affect the entire report.

Understanding filter behavior becomes extremely important when building complex reports.

🔹 19. Dashboard vs Report

These terms are often confused.

A report can contain multiple pages with interactive visuals.

A dashboard in the Power BI Service is a single-page canvas made from pinned tiles.

In everyday conversation, people sometimes use "dashboard" to mean any Power BI report page.

But technically, they're different concepts.

🔹 20. The Real Purpose of a Power BI Dashboard

A good dashboard should answer business questions.

For example:

Sales Dashboard

«How much are we selling?»

«Which regions are performing best?»

«Which products drive revenue?»

«Is revenue increasing or decreasing?»

«Where are we underperforming?»

The dashboard should make these answers easy to discover.

🔹 21. Common Beginner Mistakes

Avoid:

❌ Adding too many visuals

❌ Using every available chart type

❌ Creating unnecessary colors and decorations

❌ Building dashboards before understanding the data

❌ Ignoring relationships

❌ Creating everything as calculated columns

❌ Using measures incorrectly

❌ Showing numbers without business context

A professional dashboard should be:

Clear + Accurate + Interactive + Business-focused

🚀 Double Tap ❤️ For More

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝘁𝗼 𝗚𝗲𝘁 𝗮 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯 𝗶𝗻 𝟮𝟬𝟮𝟲 📊

Build job-ready skills through live online classes, practical assignments and real-world projects.

💼 End-to-End Placement Support
🤝 500+ Partner Companies
🎓 2000+ Students Placed
🏆 Highest Salary: ₹41 LPA

📞 Get FREE career counselling and check your eligibility!

🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇

https://pdlink.in/45vk5ph

⚡Prepare for roles such as Data Analyst, Business Analyst, BI Analyst and Reporting Analyst.

🚀 Data Analyst Roadmap — Part 23

📊 Power BI Level 2 — Power Query: Data Cleaning & Transformation

Power Query is used in Power BI to clean, transform, and prepare data before building reports.

🔹 1. Open Power Query

In Power BI Desktop:

Home → Transform Data

This opens the Power Query Editor.

You will mainly work with:

• Queries

• Data Preview

• Applied Steps

🔹 2. Change Data Types

Always check whether columns have the correct data type.

For example:

Customer_ID → Text

Quantity → Whole Number

Sales → Decimal Number

Order_Date → Date

Incorrect data types can cause problems in calculations and visuals.

🔹 3. Remove Unnecessary Columns

If your dataset contains columns you don't need, remove them.

For example:

Customer_ID

Customer_Name

Email

Phone

Sales

Internal_Code

If your analysis only needs Customer ID, Customer Name, and Sales, remove the rest.

🔹 4. Filter Unnecessary Rows

Power Query can remove or filter:

• Blank rows

• Invalid records

• Test data

• Unwanted categories

• Records outside the required period

Always understand the business rule before removing data.

🔹 5. Remove Duplicates

Power Query allows you to remove duplicate values based on selected columns.

For example, if "Customer_ID" should be unique in a Customer table, duplicate IDs should be investigated.

But don't remove duplicates blindly.

A Sales table can naturally contain many rows for the same customer.

🔹 6. Handle Missing Values

You may find:

Blank

NULL

N/A

Unknown

Depending on the situation, you can:

• Keep the value blank

• Replace it

• Remove the record

Don't automatically replace blanks with zero.

For example, a blank discount doesn't always mean a discount of 0.

🔹 7. Clean Text

Data often contains unwanted spaces or inconsistent formatting.

Example:

" Mumbai"

"Mumbai "

"MUMBAI"

Useful Power Query transformations include:

Trim → Removes unnecessary spaces

Clean → Removes unwanted non-printable characters

You can also change text to:

• UPPERCASE

• lowercase

• Proper Case

🔹 8. Replace Values

Suppose your data contains:

Mum

Mumbai

MUMBAI

You can replace and standardize values so they are represented consistently.

This is especially useful for:

• City

• Region

• Category

• Department

• Status

🔹 9. Split Columns

Suppose you have:

Full Name

John Smith

Sarah Johnson

You can split it into:

First Name | Last Name

John | Smith

Sarah | Johnson

You can split a column using delimiters such as:

• Space

• Comma

• Dash

• Custom delimiter

🔹 10. Extract Text

You can extract specific parts of a text column.

For example:

john@gmail.com

You could extract:

john

or:

gmail.com

Power Query provides options such as:

• Text Before Delimiter

• Text After Delimiter

• Text Between Delimiters

• First Characters

• Last Characters

🔹 11. Conditional Column

You can create categories based on conditions.

For example:

Sales >= 50,000 → High

Sales >= 20,000 → Medium

Otherwise → Low

This is similar to "CASE WHEN" in SQL.

🔹 12. Custom Column

Power Query also allows you to create calculated columns.

For example:

Total Amount = Quantity × Unit Price

Custom columns use Power Query's formula language, called M.

You don't need to master M immediately. Start by understanding the transformations available through the interface.

🔹 13. Merge Queries

Merge Queries combines related tables using a common column.

For example:

Customers

Customer_ID | Customer_Name

101 | John

102 | Sarah

Orders

Order_ID | Customer_ID | Sales

1 | 101 | 5000

2 | 102 | 7000

You can merge them using:

Customer_ID

This is similar to a SQL "JOIN".

🔹 14. Append Queries

Append combines tables by adding rows.

For example:

January Sales

↓

February Sales

↓

March Sales

becomes one table containing all three months.

Remember:

Merge → Combine columns

Append → Combine rows

🔹 15. Applied Steps

Power Query records every transformation you perform.

For example:

Source

↓

Changed Type

↓

Removed Columns

↓

Filtered Rows

↓

Trimmed Text

↓

Removed Duplicates

This makes the cleaning process repeatable.

When the source data is refreshed, Power Query can apply the same steps again.

🔹 16. Query Folding

Query Folding is an important performance concept.

When possible, Power Query pushes transformations back to the source system.

For example:

Power BI

↓

Filter 2026 Data

↓

Database performs filtering

↓

Power BI receives required data

This can reduce the amount of data transferred and improve refresh performance.

Query folding depends on the data source and the transformations being used.

🔹 17. Power Query vs SQL vs DAX

Remember this simple difference:

SQL

→ Retrieve and analyze data from databases

Power Query

→ Clean and transform data

DAX

→ Create calculations and analyze data inside the Power BI model

A typical workflow is:

SQL

↓

Get Data

Power Query

↓

Clean & Transform

Data Model

↓

Create Relationships

DAX

↓

Create Measures

Visuals

↓

Build Report

🎯 Interview Question

What is the difference between Merge and Append in Power Query?

Merge combines related tables using matching columns.

Append stacks tables with similar structures by adding rows.

Merge → More columns

Append → More rows

💡 Key Lesson

Power Query prepares your data so that your Power BI model and reports are built on clean, reliable data.

🚀 Double Tap ❤️ For Part-24

🚀 Data Analyst Roadmap — Part 24

📊 Power BI Level 3 — Data Modeling & Relationships

Once your data is clean, the next step is to build a proper data model.

This is where you decide how your tables connect and how Power BI should understand your data.

🔹 1. What Is a Data Model?

A data model is the structure that connects your tables.

For example, you might have:

Sales

Order_ID

Customer_ID

Product_ID

Date

Sales

Quantity

Customers

Customer_ID

Customer_Name

Region

Products

Product_ID

Product_Name

Category

Date

Date

Month

Quarter

Year

These tables are connected through relationships.

🔹 2. Fact Table

A fact table contains business transactions and numerical values.

Example:

Sales

It may contain:

• Sales Amount

• Quantity

• Cost

• Profit

• Order ID

Think:

Fact = What happened?

🔹 3. Dimension Table

Dimension tables describe the facts.

Examples:

Customer → Who?

Product → What?

Date → When?

Region → Where?

For example:

Customer

Customer_ID

Customer_Name

Region

🔹 4. Star Schema

A common Power BI model looks like this:

Customers


Products ──── Sales ──── Date


Region

The fact table is in the middle and dimension tables surround it.

This is called a Star Schema.

🔹 5. Primary Key

A primary key uniquely identifies a record.

For example:

Customer_ID

101

102

103

Each ID identifies one customer.

🔹 6. Foreign Key

The Sales table can contain the same customer multiple times:

Customer_ID

101

101

102

101

103

Here, "Customer_ID" is used to connect Sales with Customers.

So:

Customers → Primary Key

Sales → Foreign Key

🔹 7. One-to-Many Relationship

The most common relationship in Power BI is:

One Customer → Many Sales

Customers              Sales
    1                    *
    |                    |
Customer_ID ───────── Customer_ID

This is called a:

1 : * relationship

🔹 8. Why Relationships Matter

Suppose you select:

Region = West

Power BI needs to know which sales belong to customers from the West region.

The relationship allows the filter to travel from:

Customers

↓

Sales

Without a proper relationship, your visuals may show incorrect results.

🔹 9. Cardinality

Cardinality describes how records relate between two tables.

Common types:

1 : * → One-to-Many

1 : 1 → One-to-One

• : * → Many-to-Many

For most Power BI analytical models, 1-to-many relationships are the most common.

🔹 10. Many-to-Many Relationships

Many-to-many relationships can make models more complicated.

For example:

Customers ↔ Products

A customer can buy many products.

A product can be purchased by many customers.

Instead of directly connecting them in some cases, a bridge table can be used.

Customers

↓

Bridge Table

↓

Products

🔹 11. Date Table

A proper Date table is extremely important for Power BI.

It can contain:

Date

Day

Month

Month Number

Quarter

Year

Year-Month

For example:

Date       | Month   | Quarter | Year
01-Jan-26  | January | Q1      | 2026
02-Jan-26  | January | Q1      | 2026

This makes time-based analysis much easier.

🔹 12. Why Month Number Is Important

If you display:

January

February

March

April

Power BI may sort month names alphabetically depending on the setup.

You need a:

Month Number

January → 1

February → 2

March → 3

Then sort Month by Month Number.

🔹 13. Understand the Grain

Before creating relationships, ask:

"What does one row represent?"

For example:

Sales table

→ One row = One order

or:

Sales table

→ One row = One order item

These are different grains.

If you don't understand the grain, you can accidentally double-count sales.

🔹 14. Example of a Grain Problem

Suppose one order contains:

Order 1001

Laptop → ₹60,000

Mouse → ₹2,000

The order-item table has two rows.

If you join this with another table incorrectly, the ₹62,000 order value could potentially be repeated.

So before creating relationships or calculations:

Always understand the grain of your tables.

🔹 15. Active and Inactive Relationships

Sometimes two tables can have more than one possible relationship.

For example, Sales may contain:

Order_Date

Ship_Date

Both could connect to the Date table.

But Power BI generally allows only one active relationship between the same pair of tables at a time.

The other relationship can be inactive and activated when needed using DAX.

This becomes important when building advanced date analysis.

🔹 16. Filter Direction

Relationships control how filters move between tables.

In a simple star schema:

Customer

↓

Sales

filters usually flow from the dimension toward the fact table.

Avoid using bi-directional filtering everywhere.

It can create:

• Ambiguous relationships

• Unexpected results

• Difficult-to-debug models

• Performance issues

🔹 17. Don't Create Relationships Just Because Column Names Match

For example:

Customer_ID

appearing in two tables doesn't automatically mean they should be connected.

Check:

✔ Same business meaning

✔ Compatible data type

✔ Correct grain

✔ Unique values on the "one" side

✔ Correct cardinality

🎯 Interview Question

What is the difference between a Fact Table and a Dimension Table?

Fact Table

Contains business transactions and measurable values.

Example:

"Sales, Quantity, Cost"

Dimension Table

Contains descriptive information used to analyze those transactions.

Example:

"Customer, Product, Date, Region"

Easy way to remember:

Fact = What happened

Dimension = Describe what happened

💡 Key Lesson

Don't build your Power BI visuals before understanding your data model.

A good model makes your calculations easier, your reports more reliable, and your analysis much easier to maintain.

🚀 Double Tap ❤️ For More

🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥

Explore these FREE certification courses in today’s most in-demand technology fields:

📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF

💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo

💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q

☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn

🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv

🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH

⚡ Start learning today and prepare yourself for better career opportunities in 2026!

Preparing for a SQL interview?

Focus on mastering these essential topics:

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

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

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

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

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

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

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

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

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

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

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

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

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

Like this post if you need more 👍❤️

Hope it helps :)