This asks:

Which employees earn more than the average salary of their own department?

The inner query depends on the current employee's department. This is more advanced than a basic subquery.

2️⃣4️⃣ Why Correlated Subqueries Matter

Suppose: IT Average salary = ₹80,000, HR Average salary = ₹60,000

An employee earning ₹75,000: Could be below IT average, Could be above HR average

So comparing everyone to the overall company average isn't enough. A correlated subquery allows you to compare each employee to the relevant group.

2️⃣5️⃣ Subquery vs JOIN

Sometimes the same problem can be solved using either a subquery or a JOIN.

For example, finding customers with orders can be done with: WHERE EXISTS (...) or: JOIN Orders ...

Neither is universally better.

The right choice depends on: What result you need, Whether duplicates matter, Query readability, Database optimizer, Data structure

Focus on understanding the logic rather than memorizing one preferred method.

2️⃣6️⃣ A Very Common Interview Problem

Question:

Find employees earning more than their department's average salary.

A correlated subquery solution:

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

This is an excellent interview question because it tests: Subqueries, Aggregation, Correlation, Business logic

2️⃣7️⃣ Another Interview Problem

Question:

Find customers whose total sales are greater than ₹1,00,000.

Using a derived table:

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

Or using a CTE:

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

Both approaches produce the same analytical idea.

🧪 Practical Interview Challenge

Suppose you have Employees table

Q1. Find employees earning above the overall average.

SELECT
    Name,
    Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

Q2. Find employees earning above their department average.

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

Q3. Create a CTE containing average salary by department.

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

Q4. Find departments whose average salary exceeds ₹70,000.

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

SELECT
    Department,
    Average_Salary
FROM Department_Salary
WHERE Average_Salary > 70000;

Q5. Find customers who have placed at least one order.

SELECT
    c.Customer_ID,
    c.Customer_Name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.Customer_ID = c.Customer_ID
);

🏆 Double Tap ❤️ For More

📊 𝗠𝗮𝘀𝘁𝗲𝗿 𝗘𝘅𝗰𝗲𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 | 𝟱 𝗣𝗼𝘄𝗲𝗿𝗳𝘂𝗹 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

🔥 Top 5 FREE Excel Courses:

1️⃣ Goldman Sachs – Excel Skills for Business
2️⃣ PwC – Problem Solving with Excel
3️⃣ Corporate Finance Institute – Excel Fundamentals
4️⃣ Great Learning – Excel for Beginners
5️⃣ Simplilearn – Introduction to MS Excel

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

https://pdlink.in/3UQ8S09

🚀 Learn Excel for FREE and upgrade your career skills!

Scenario based  Interview Questions & Answers for Data Analyst

1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
  Question:
  - Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
    SELECT CustomerID, COUNT(*) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID;

2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
  Question:
  - Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
    SELECT Name
    FROM Employees
    WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;

Power BI Scenario-Based Questions

1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
    Expected Answer:
    - Load the dataset into Power BI.
    - Create relationships if necessary.
    - Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
    - Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
    - Use the "Filters" pane to filter data as needed.
    - Format the visualization to enhance clarity and readability.

2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
  Expected Answer:
    - Use Power BI Desktop to connect to the API.
    - Go to "Get Data" > "Web" and enter the API URL.
    - Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
    - Create visualizations using the imported data.
    - Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.

3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
    Expected Answer:
    - Analyze the current performance using Performance Analyzer.
    - Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
    - Use aggregated tables to pre-compute results.
    - Simplify DAX calculations.
    - Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
    - Ensure proper indexing on the data source.

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

Like if you need more similar content

Hope it helps :)

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

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.

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

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

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

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

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

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

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

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

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

🚀 Data Analyst Roadmap — Part 16

🧠 SQL Level 6 — Window Functions

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

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

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

🔹 1. GROUP BY vs Window Functions

Suppose you have:

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

With GROUP BY:

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

You get one row per department.

With a window function:

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

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

👉 GROUP BY reduces rows.

👉 Window functions preserve rows.

🔹 2. Understanding OVER()

Every window function uses the OVER() clause.

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

The three important concepts are:

• OVER() → Defines the window.

• PARTITION BY → Divides rows into groups.

• ORDER BY → Defines the order inside each group.

🔹 3. ROW_NUMBER()

Assigns a unique sequential number to each row.

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

Result:

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

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

🔹 4. RANK()

Gives the same rank to tied values.

For: 100, 100, 90

RANK() produces: 1, 1, 3

The next rank is skipped.

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

🔹 5. DENSE_RANK()

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

For: 100, 100, 90

DENSE_RANK() produces: 1, 1, 2

🧠 Remember the Difference

For values: 100, 100, 90, 80

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

This difference is a very common SQL interview topic.

🔹 6. Overall Ranking

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

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

🔹 7. Top N Employees Per Department

One of the most useful real-world applications.

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

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

This finds the top 2 employees in every department.

This pattern is extremely important:

Window Function → CTE/Subquery → Filter

🔹 8. LAG()

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

For monthly sales:

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

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

You can then calculate month-over-month change:

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

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

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!

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

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!

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

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!

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

✨ 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

🚀 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

🔹 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

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

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

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.

🚀 𝗙𝗥𝗘𝗘 𝗖𝗶𝘁𝗶 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀 😍 | 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

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