Data Analytics

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

• Always check whether the resulting grain is still what you expect.

🔹 16. The Most Important Analytical Pattern

Many advanced SQL problems can be solved using this structure:

•

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

For example:

• Orders → Filter → GROUP BY Customer → Calculate Sales → RANK() → Keep Top 3 → Final Report

• Learning this thought process is more valuable than memorizing individual queries.

💼 Real-World Business Problems You Should Practice

Sales

✅ Top 5 products by revenue

✅ Top products within each category

✅ Month with highest sales

✅ Revenue growth by month

✅ Customers contributing most revenue

Customers

✅ Customers with no orders

✅ Customers with declining purchases

✅ Most recent purchase per customer

✅ Repeat customers

✅ Average order value per customer

Operations

✅ Orders taking longer than expected

✅ Products never sold

✅ Duplicate transactions

✅ Most active regions

✅ Employees with above-average performance

🎯 SQL Interview Challenge

Question: Find the highest-selling product in each category.

Think about the problem before writing the query:

• Product → Category → Total Sales → Rank Within Category → Keep Rank 1

A possible solution:

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 Product_ID, Category, Total_Sales,
           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;

💡 Double Tap ❤️ For More

• This can help marketing teams identify potential customers who need activation campaigns.

🔹 9. Customers Above Average Spending

First calculate customer totals. Then compare them with the overall average.

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

• Notice how concepts from earlier parts work together: CTE + Aggregation + Subquery

🔹 10. Month-over-Month Sales Growth

First aggregate sales by month. Then use LAG().

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 EXTRACT(YEAR FROM Order_Date), EXTRACT(MONTH FROM Order_Date)
),
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;

• This produces a time-based comparison instead of just a total.

🔹 11. Calculate Growth Percentage

You can extend the previous query:

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

• NULLIF() is important because it prevents division-by-zero errors.

• A practical analyst must always think about edge cases.

🔹 12. Find the Latest Order for Every Customer

From Part 17, combine PARTITION BY + ORDER BY + ROW_NUMBER()

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;

This is useful for:

• Customer activity

• Churn analysis

• Last purchase reporting

• CRM segmentation

🔹 13. Identify Potentially Inactive Customers

First find the last purchase date:

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

Then compare the last order against a chosen inactivity threshold.

• The business rule might be: No purchase for 90 days → Potentially inactive

• The important lesson: SQL provides the calculation. The business defines what "inactive" means.

🔹 14. Find Duplicate Records

Window functions are excellent for detecting 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;

• These records can then be investigated before cleaning the dataset.

🔹 15. Combining Multiple Tables

Real analysis often requires several joins.

For example: Customers → Orders → Order_Items → Products

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;

🚀 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

• This approach prevents complicated SQL from becoming confusing.

🔹 2. Find the Correct Grain

One of the most important analytical concepts is grain.

Grain means: What does one row represent?

For example:

• 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

Suppose you have:

Customers

• Customer_ID

• Customer_Name

and Orders

• Order_ID

• Customer_ID

• Order_Date

• Sales

You can calculate customer revenue:

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;

• Now you have one row per customer.

🔹 4. Rank Customers by Revenue

Combine aggregation with a window function:

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;

• This answers: Who are our highest-value customers?

🔹 5. Top 3 Customers in Each Region

Now add another business dimension.

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

• This is a classic advanced SQL interview problem.

🔹 6. Finding the Second-Highest Salary

A common interview question.

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;

• Using DENSE_RANK() is useful when multiple employees share the same salary.

🔹 7. Find Products That Never Sold

This is a classic LEFT JOIN problem.

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;

Business interpretation: Products exist in the catalog but have no sales.

This could indicate:

• Poor demand

• Pricing problems

• Inventory issues

• Product visibility problems

🔹 8. Customers With No Orders

The same logic can identify customers who have never purchased.

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;

🚀 𝗙𝗥𝗘𝗘 𝗖𝗶𝘁𝗶 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀 😍 | Boost Your Resume

Citi offers virtual experience programs designed to help students and freshers develop job-ready skills through real-world tasks.

✅ 100% FREE
✅ Self-paced learning
✅ Real-world projects
✅ Certificate on completion
✅ Add the experience to your Resume & LinkedIn

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

https://pdlink.in/4zZqJ4U

🔥 Learn → Complete Projects → Earn Certificate → Strengthen Your Resume

Starting as a data analyst is a great first step in your career. As you grow, you might discover new interests:

• If you love working with statistics and machine learning, you could move into Data Science.

• If you're excited by building data systems and pipelines, Data Engineering might be your next step.

• If you're more interested in understanding the business side, you could become a Business Analyst.

Even if you decide to stay in your data analyst role, there's always something new to learn, especially with advancements in AI.

There are many paths to explore, but what's important is taking that first step.

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥

Want to upgrade your tech skills without spending money?

Here are some excellent FREE YouTube resources to learn high-demand technologies through tutorials and hands-on practice.

🔥 Learn → Practice → Build Projects → Upgrade Your Resume

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

https://pdlink.in/4x3B9hb

🎯 Perfect for Students • Freshers • Job Seekers • Working Professionals

🔹 16. Days 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;
-- Then: Current - Previous

🔹 17. Finding Inactive Customers

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date
FROM Orders
GROUP BY Customer_ID;
-- Compare with CURRENT_DATE for 90-day inactivity

🔹 18. Common Mistake

Avoid: WHERE YEAR(Order_Date) = 2026

Prefer: Range filter — it's clearer and index-friendly.

💼 Real-World Applications

✅ Monthly revenue, daily sales, YoY/MoM growth, retention, churn, purchase frequency, cohort, subscription expiry

🎯 SQL Interview Challenge: Find each customer's most recent order

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;

💡 Double Tap ❤️ For More

🚀 Data Analyst Roadmap — Part 17

🧠 SQL Level 7 — Date & Time Functions + Time-Based Analysis

Date and time analysis is one of the most important SQL skills for a Data Analyst.

Real business data is heavily time-dependent:

📈 Monthly revenue

📊 Year-over-year growth

🛒 Daily orders

👥 Customer activity

📦 Product demand

⏱️ Response time

📅 Retention and cohort analysis

To become strong in SQL, you need to know how to extract, filter, compare, group, and calculate differences between dates.

🔹 1. Understanding Date & Time Data Types

• DATE → Date only

• TIME → Time only

• DATETIME / TIMESTAMP → Date + time

Order_Date: 2026-01-15
Order_Timestamp: 2026-01-15 14:35:20

🔹 2. Extracting Parts of a Date

SELECT
    Order_Date,
    EXTRACT(YEAR FROM Order_Date) AS Order_Year,
    EXTRACT(MONTH FROM Order_Date) AS Order_Month
FROM Orders;

🔹 3. Grouping Sales by Year

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

🔹 4. Grouping Sales by Month

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

⚠️ Don't group only by month number when data covers multiple years — Jan 2025 + Jan 2026 would merge incorrectly.

🔹 5. Filtering Data by Date

SELECT * FROM Orders
WHERE Order_Date >= '2026-01-01'
  AND Order_Date < '2026-02-01';

🔹 6. Why Date Ranges Matter

If Order_Timestamp = 2026-01-31 23:30:00, then

WHERE Order_Timestamp <= '2026-01-31' will miss it.

Use half-open range:

WHERE Order_Timestamp >= '2026-01-01'
  AND Order_Timestamp < '2026-02-01'

🔹 7. Date Difference

Conceptually: Date_Difference(First_Purchase_Date, Signup_Date)

Dialects vary: DATEDIFF(), DATE_DIFF(), subtraction, etc.

🔹 8. Customers Who Took >30 Days to Purchase

SELECT Customer_ID, Signup_Date, First_Purchase_Date
FROM Customers
WHERE DATEDIFF(day, Signup_Date, First_Purchase_Date) > 30;

🔹 9. Adding / Subtracting Dates

Signup_Date + INTERVAL '30' DAY  -- 30 days after signup
Order_Date - INTERVAL '7' DAY    -- 7 days before order

🔹 10. Current Date and Time

CURRENT_DATE, CURRENT_TIMESTAMP — useful for today's sales, active subs, overdue orders.

🔹 11. Recent Orders (Last 30 Days)

SELECT * FROM Orders
WHERE Order_Date >= CURRENT_DATE - INTERVAL '30' DAY;

🔹 12. Year-over-Year Analysis

Formula: (Current - Previous) / Previous * 100

In SQL:

LAG(Sales) OVER (ORDER BY Year)

🔹 13. Month-over-Month 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
)

SELECT
    Year, Month, Total_Sales,
    LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Month_Sales
FROM Monthly_Sales;

🔹 14. Quarter Analysis

EXTRACT(QUARTER FROM Order_Date)
-- Q1: Jan-Mar, Q2: Apr-Jun, Q3: Jul-Sep, Q4: Oct-Dec

🔹 15. First and Last Transaction

ROW_NUMBER() OVER (
    PARTITION BY Customer_ID
    ORDER BY Order_Date
)
-- rn = 1 is first transaction

🚀 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝗢𝗻 𝗔𝘇𝘂𝗿𝗲 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 ☁️

✨ Build practical skills in Cloud AI • Machine Learning • Data Preparation • ML Workflows • Azure Data Services.

🔥 Learn → Practice → Build Projects → Strengthen Your Tech Career

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

https://pdlink.in/3UyljxK

🎓 Perfect for Students • Freshers • Data Science Aspirants • AI/ML Learners • Working Professionals

𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 — 𝗚𝗲𝘁 𝗣𝗹𝗮𝗰𝗲𝗱 𝗜𝗻 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀😍

Learn JAVA/MERN Full Stack Development With GenAI.

🏆 Placement Highlights:-

💰 ₹41 LPA highest salary
📈 ₹7.4 LPA average salary
🎓 2,000+ students placed
🏢 500+ partner companies

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:-

https://pdlink.in/3SuUeuD

⚡ Take the first step toward your dream tech career today!

🔥 𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 — 𝗙𝗿𝗼𝗺 𝗕𝗲𝗴𝗶𝗻𝗻𝗲𝗿 𝘁𝗼 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱! 💻📊

These free learning resources cover everything from database fundamentals to advanced SQL queries, with opportunities to practice real-world problems.

🎯 Top FREE SQL Resources:
1️⃣ Introduction to Databases & SQL — Udemy
2️⃣ Advanced Database & SQL — Udemy
3️⃣ Learn SQL — Codecademy
4️⃣ SQL Tutorial — SQLZoo

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

https://pdlink.in/4gNYHk7

🚀 Start from the basics and work your way toward advanced SQL skills!

🚀 𝗗𝗿𝗲𝗮𝗺𝗶𝗻𝗴 𝗼𝗳 𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝗮𝘁 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀? 💻🔥

Here’s a collection of company-specific resources to help you understand their interview and hiring processes.

🎯 Interview Preparation Guides For:

🟠 Amazon – Interviewing Guide
🔵 Google – Interview Tips
🪟 Microsoft – Hiring & Interview Tips
🟢 NVIDIA – Hiring Process
🔷 Meta – Software Engineering Interview Prep

𝐋𝐢𝐧𝐤 👇:-

https://pdlink.in/4i6HkgN

📢 Save & share this with your friends — start learning for FREE!

You can then calculate month-over-month change:

SELECT
    Sales_Month,
    Sales,
    Sales - LAG(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Sales_Change
FROM Monthly_Sales;

🔹 9. LEAD()

LEAD() does the opposite.

It allows you to access the next row.

SELECT
    Sales_Month,
    Sales,
    LEAD(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Next_Month_Sales
FROM Monthly_Sales;

Useful for:

• Comparing future periods

• Customer activity

• Event sequences

• Next purchase analysis

• Time-based analysis

🔹 10. Running Total

A running total continuously accumulates values.

SELECT
    Order_Date,
    Sales,
    SUM(Sales) OVER (
        ORDER BY Order_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Running_Sales
FROM Orders;

Example: 10,000 → 15,000 → 22,000 becomes 10,000 → 25,000 → 47,000

🔹 11. Running Total by Region

You can combine PARTITION BY with a running total.

SELECT
    Region,
    Order_Date,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY Region
        ORDER BY Order_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Regional_Running_Sales
FROM Orders;

Each region gets its own running total.

🔹 12. Moving Average

A moving average helps identify trends while reducing short-term fluctuations.

SELECT
    Sales_Month,
    Sales,
    AVG(Sales) OVER (
        ORDER BY Sales_Month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS Three_Month_Avg
FROM Monthly_Sales;

This calculates a 3-month moving average.

Useful for:

📈 Sales trends,

📊 Revenue analysis,

📦 Demand forecasting,

👥 Customer activity

🔹 13. NTILE()

NTILE() divides rows into approximately equal groups.

For example, divide customers into four sales groups:

SELECT
    Customer_ID,
    Total_Sales,
    NTILE(4) OVER (
        ORDER BY Total_Sales DESC
    ) AS Sales_Quartile
FROM Customers;

This can help identify:

• Top 25% customers

• Bottom 25% customers

• Customer segments

• Performance groups

🔹 14. Removing Duplicates

Window functions are also extremely useful for deduplication.

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

SELECT *
FROM Ranked_Data
WHERE rn = 1;

This keeps the first record from each duplicate group.

🔹 15. Why Window Functions Cannot Usually Be Used Directly in WHERE

This won't generally work:

SELECT
    Employee,
    RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
WHERE Salary_Rank <= 3;

Why?

Because the window calculation happens after the filtering stage.

Instead, use a CTE:

WITH Ranked AS (
    SELECT
        Employee,
        Salary,
        RANK() OVER (
            ORDER BY Salary DESC
        ) AS Salary_Rank
    FROM Employees
)
SELECT *
FROM Ranked
WHERE Salary_Rank <= 3;

This is another reason CTEs + Window Functions are such a powerful combination.

💼 Real-World Data Analyst Applications

Window functions are commonly used for:

✅ Top N products by category

✅ Ranking employees by department

✅ Customer rankings by region

✅ Month-over-month growth

✅ Running revenue totals

✅ Moving averages

✅ Finding first/previous/next transactions

✅ Identifying duplicate records

✅ Customer purchase sequences

✅ Performance comparisons

🎯 SQL Interview Challenge

Question: Find the top 3 highest-paid employees in every department.

WITH Ranked_Employees AS (
    SELECT
        Employee,
        Department,
        Salary,
        DENSE_RANK() OVER (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS Salary_Rank
    FROM Employees
)
SELECT
    Employee,
    Department,
    Salary
FROM Ranked_Employees
WHERE Salary_Rank <= 3;

🏆 Double Tap ❤️ For More

🚀 Data Analyst Roadmap — Part 16

🧠 SQL Level 6 — Window Functions

Window functions are one of the most important SQL skills for a Data Analyst.

They allow you to perform calculations across related rows without losing the individual rows.

Instead of collapsing data like GROUP BY, window functions let you analyze each row in the context of other rows.

🔹 1. GROUP BY vs Window Functions

Suppose you have:

Employee | Department | Salary
John     | IT         | 75,000
Mike     | IT         | 90,000
Lisa     | IT         | 90,000
Sarah    | HR         | 60,000
Alice    | HR         | 70,000

With GROUP BY:

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

You get one row per department.

With a window function:

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

You keep every employee while also seeing their department's average salary.

👉 GROUP BY reduces rows.

👉 Window functions preserve rows.

🔹 2. Understanding OVER()

Every window function uses the OVER() clause.

FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

The three important concepts are:

• OVER() → Defines the window.

• PARTITION BY → Divides rows into groups.

• ORDER BY → Defines the order inside each group.

🔹 3. ROW_NUMBER()

Assigns a unique sequential number to each row.

SELECT
    Employee,
    Department,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS Row_Num
FROM Employees;

Result:

Employee | Department | Salary | Row_Num
Mike     | IT         | 90,000 | 1
Lisa     | IT         | 90,000 | 2
John     | IT         | 75,000 | 3
Alice    | HR         | 70,000 | 1
Sarah    | HR         | 60,000 | 2

⚠️ If salaries are tied, ROW_NUMBER() still assigns different numbers.

🔹 4. RANK()

Gives the same rank to tied values.

For: 100, 100, 90

RANK() produces: 1, 1, 3

The next rank is skipped.

RANK() OVER (
    PARTITION BY Department
    ORDER BY Salary DESC
) AS Salary_Rank

🔹 5. DENSE_RANK()

Also gives the same rank to tied values, but doesn't skip the next rank.

For: 100, 100, 90

DENSE_RANK() produces: 1, 1, 2

🧠 Remember the Difference

For values: 100, 100, 90, 80

Function     | Result
ROW_NUMBER() | 1, 2, 3, 4
RANK()       | 1, 1, 3, 4
DENSE_RANK() | 1, 1, 2, 3

This difference is a very common SQL interview topic.

🔹 6. Overall Ranking

Remove PARTITION BY when you want to rank across the entire dataset.

SELECT
    Employee,
    Salary,
    RANK() OVER (
        ORDER BY Salary DESC
    ) AS Overall_Rank
FROM Employees;

🔹 7. Top N Employees Per Department

One of the most useful real-world applications.

WITH Ranked_Employees AS (
    SELECT
        Employee,
        Department,
        Salary,
        ROW_NUMBER() OVER (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS rn
    FROM Employees
)

SELECT
    Employee,
    Department,
    Salary
FROM Ranked_Employees
WHERE rn <= 2;

This finds the top 2 employees in every department.

This pattern is extremely important:

Window Function → CTE/Subquery → Filter

🔹 8. LAG()

LAG() lets you access a value from a previous row.

For monthly sales:

Month | Sales
Jan   | 10,000
Feb   | 12,000
Mar   | 15,000

SELECT
    Sales_Month,
    Sales,
    LAG(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Previous_Month_Sales
FROM Monthly_Sales;

🎓 𝗧𝗼𝗽 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀 𝗢𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

Learn in-demand skills • Add valuable credentials to your resume

🏢 TATA :- https://pdlink.in/3QiwLvx

💻 Infosys :- https://pdlink.in/4eBH3Aa

⚡ IBM :- https://pdlink.in/45KgqDR

💫 Amazon :- https://pdlink.in/47XuBGz

🌐 Cisco :- https://pdlink.in/4gaeVVV

🪟 Microsoft :- https://pdlink.in/4zhGTX6

📢 Save & share this with your friends — start upskilling for FREE!

🚀 𝗟𝗲𝘃𝗲𝗹 𝗨𝗽 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 𝘄𝗶𝘁𝗵 𝗙𝗥𝗘𝗘 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴! 💻

Microsoft-focused learning paths can help you strengthen your resume and prepare for in-demand tech and data roles.

🔥 Top 5 Courses / Certification Paths:
✅ Beginner-friendly options
✅ Build practical, job-ready skills
✅ Learn Azure, Power BI, Excel & SQL
✅ Strengthen your resume & career profile

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

https://pdlink.in/3UNpPs7

💫Perfect for students, freshers, data analysts and professionals looking to upgrade their skills.