SQL Programming Resources

🎓 𝐅𝐑𝐄𝐄 𝐈𝐁𝐌 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 🚀

Explore these beginner-friendly courses and strengthen your resume!

🎯 Perfect for Students, Freshers and Working Professionals
💻 Learn Online at Your Own Pace
📜 Earn Certificates After Successful Completion

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

https://pdlink.in/45KgqDR

🔥 Don’t just collect certificates—build skills that employers value. Share this with your friends!

⚠️ 27. Time Zones Matter

A timestamp may represent UTC while business operates in IST.

• UTC: 2026-09-18 23:30

• India: 2026-09-19 05:00

For global analytics, always understand source timezone, database timezone, reporting timezone, daylight-saving rules.

🎤 SQL Interview Questions

Q1. What is the difference between DATE and TIMESTAMP?

DATE generally stores a calendar date. TIMESTAMP generally stores both date and time.

Q2. How do you extract the year from a date?

A common approach is EXTRACT(YEAR FROM date_column). Some databases also support YEAR(date_column).

Q3. How do you calculate the current date?

CURRENT_DATE

Q4. What is DATE_TRUNC() used for?

It truncates a date/time to a specified period such as year, month, day, hour. Commonly used for time-based grouping.

Q5. How can you calculate month-over-month growth?

Typically aggregate by month → LAG() previous month → calculate percentage difference.

Q6. Why can BETWEEN be problematic with timestamps?

Because an upper bound such as '2026-01-31' may represent the start of that date rather than entire day.

Q7. What is a calendar table?

A table containing dates and associated attributes such as year, month, weekday, fiscal period, holidays.

Q8. How can you calculate customer inactivity?

Find the customer's latest transaction date using MAX(transaction_date) and compare with current date.

Q9. What is LAG() useful for in time-series analysis?

It allows comparison with a previous row, such as previous month revenue.

Q10. Why are time zones important in analytics?

Same timestamp can represent different local dates and times depending on timezone.

📝 Practice Questions

Practice 1 — Calculate annual revenue.

SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS revenue FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY year;

Practice 2 — Calculate monthly revenue.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;

Practice 3 — Find the last order date for every customer.

SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id;

Practice 4 — Find orders placed in 2026.

SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Practice 5 — Calculate delivery duration.

SELECT order_id, delivery_date - order_date AS delivery_duration FROM orders;

🧪 Mini SQL Challenge

You have: orders (order_id, customer_id, order_date, delivery_date, amount)

Create a query that returns Order ID, Customer ID, Order month, Order amount, Delivery duration, SLA status, Running customer revenue. Assume SLA is 3 days.

Solution:

SELECT
    order_id,
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month,
    amount,
    delivery_date - order_date AS delivery_duration,
    CASE WHEN delivery_date - order_date <= 3 THEN 'Within SLA' ELSE 'SLA Breach' END AS sla_status,
    SUM(amount) OVER (
        PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_customer_revenue
FROM orders;

💡 Double Tap ❤️ For More

SELECT transaction_date,
    CASE WHEN EXTRACT(DOW FROM transaction_date) IN (0, 6) THEN 'Weekend' ELSE 'Weekday' END AS day_type
FROM transactions;

📊 20. Business Example — Peak Sales Day

Suppose we want to find which date generated the highest revenue.

WITH daily_sales AS (
    SELECT order_date, SUM(amount) AS daily_revenue FROM orders GROUP BY order_date
)
SELECT order_date, daily_revenue FROM daily_sales ORDER BY daily_revenue DESC LIMIT 1;

🧠 21. Date Truncation vs Date Extraction

Extraction gets a component: EXTRACT(YEAR FROM order_date) → 2026

Truncation converts to a larger bucket: DATE_TRUNC('month', order_date) → 2026-09-01

• EXTRACT → Give the part

• DATE_TRUNC → Give the period bucket

🔄 22. Adding Intervals to Dates

Sometimes you need to calculate a future or previous date.

For example, PostgreSQL:

SELECT order_date, order_date + INTERVAL '7 days' AS follow_up_date FROM orders;

This can be useful for follow-up dates, SLA deadlines, subscription periods, reminder dates.

📉 23. Finding Inactive Customers

One approach is:

SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id;

Then filter:

WITH customer_activity AS (
    SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id
)

SELECT customer_id, last_order_date FROM customer_activity WHERE last_order_date < CURRENT_DATE - INTERVAL '90 days';

📈 24. Year-over-Year Analysis

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders GROUP BY DATE_TRUNC('month', order_date)
)

SELECT month, revenue, LAG(revenue, 12) OVER (ORDER BY month) AS previous_year_revenue
FROM monthly_sales;

Here LAG(..., 12) looks 12 rows backward. If months are missing, LAG(..., 12) may not correspond to same calendar month one year earlier. For robust YoY, explicitly align dates or build a complete calendar.

📅 25. Calendar Tables

A calendar table contains one row for each date in a period.

Example:

• 2026-01-01 | 2026 | 1 | January | Thursday

• 2026-01-02 | 2026 | 1 | January | Friday

Calendar tables are extremely useful for time-series analysis, missing-date detection, reporting, fiscal calendars, holidays, business days, YoY comparisons.

🏦 26. Financial Year Analysis

Business calendars don't always follow January–December. For example April → March. You can create fiscal-year logic using date functions and CASE.

Conceptually:

CASE
    WHEN EXTRACT(MONTH FROM order_date) >= 4 THEN EXTRACT(YEAR FROM order_date) + 1
    ELSE EXTRACT(YEAR FROM order_date)
END AS fiscal_year

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders GROUP BY DATE_TRUNC('month', order_date)
),
monthly_comparison AS (
    SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous_revenue
    FROM monthly_sales
)
SELECT
    month,
    revenue,
    previous_revenue,
    (revenue - previous_revenue) * 100.0 / NULLIF(previous_revenue, 0) AS mom_growth
FROM monthly_comparison
ORDER BY month;

⏳ 10. Calculating Date Differences

A common business question is: How many days were there between two dates?

PostgreSQL:

SELECT order_id, delivery_date - order_date AS delivery_days FROM orders;

Other databases may use DATEDIFF():

SELECT order_id, DATEDIFF(day, order_date, delivery_date) AS delivery_days FROM orders;

The exact syntax depends on the database.

🚚 11. Delivery Time Analysis

We can calculate delivery duration:

SELECT order_id, order_date, delivery_date, delivery_date - order_date AS delivery_duration FROM orders;

This can help answer: Which orders took longer than the target SLA?

🎯 12. SLA Analysis

Suppose the target delivery time is 3 days.

SELECT
    order_id,
    order_date,
    delivery_date,
    CASE WHEN delivery_date - order_date <= 3 THEN 'Within SLA' ELSE 'SLA Breach' END AS sla_status
FROM orders;

🧮 13. Calculating Ageing

Ageing is common in Banking, Finance, Accounts payable, Receivables, Operations, Ticket management.

You can calculate days overdue:

SELECT invoice_id, due_date, CURRENT_DATE - due_date AS days_overdue
FROM invoices
WHERE due_date < CURRENT_DATE;

💰 14. Ageing Buckets

We can combine date calculations with CASE.

SELECT
    invoice_id,
    due_date,
    CASE
        WHEN CURRENT_DATE - due_date <= 0 THEN 'Not Due'
        WHEN CURRENT_DATE - due_date <= 30 THEN '1-30 Days'
        WHEN CURRENT_DATE - due_date <= 60 THEN '31-60 Days'
        WHEN CURRENT_DATE - due_date <= 90 THEN '61-90 Days'
        ELSE '90+ Days'
    END AS ageing_bucket
FROM invoices;

📆 15. Filtering by Date

Suppose we want orders from 2026:

SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

This half-open date range is especially useful when order_date is a timestamp.

⚠️ 16. Why BETWEEN Can Be Risky with Timestamps

Suppose order_timestamp contains 2026-01-31 15:30:00.

Using:

WHERE order_timestamp BETWEEN '2026-01-01' AND '2026-01-31'

may not include all records on January 31.

A safer pattern is:

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

🕐 17. Extracting the Hour

For timestamp data, you may want to analyze activity by hour.

SELECT EXTRACT(HOUR FROM transaction_timestamp) AS transaction_hour, COUNT(*) AS transaction_count
FROM transactions GROUP BY EXTRACT(HOUR FROM transaction_timestamp) ORDER BY transaction_hour;

This can help identify peak transaction times, system load, customer activity patterns.

📅 18. Day-of-Week Analysis

You can also analyze transactions by weekday.

Some systems provide EXTRACT(DOW FROM transaction_date) or DAYOFWEEK().

SELECT EXTRACT(DOW FROM transaction_date) AS day_of_week, COUNT(*) AS transactions
FROM transactions GROUP BY EXTRACT(DOW FROM transaction_date) ORDER BY day_of_week;

🏪 19. Weekend vs Weekday Analysis

Using date functions with CASE:

🚀 SQL Roadmap 2026 — Part 15

📅 SQL Date & Time Functions — Working with Dates, Time & Time-Based Analytics

Almost every real-world analytics project involves dates.

Examples:

• When was the order placed?

• How many days did delivery take?

• What was the revenue in January?

• Which month had the highest sales?

• How many customers purchased this year?

• How long has an account been inactive?

• What is the month-over-month growth?

• Which transactions breached the SLA?

SQL provides powerful date and time functions to answer these questions.

🧠 1. Common Date & Time Data Types

Different databases support different date/time types, but commonly you'll encounter:

• DATE

• TIME

• TIMESTAMP

• DATETIME

DATE stores only the date: 2026-09-18

TIME stores only time: 14:30:25

TIMESTAMP stores both date and time: 2026-09-18 14:30:25

The exact data types and behavior vary by database.

📅 2. Current Date

Many databases provide a function for the current date.

For example:

SELECT CURRENT_DATE;

Result: 2026-09-18

The exact function can vary by SQL dialect.

⏰ 3. Current Date and Time

A common SQL expression is:

SELECT CURRENT_TIMESTAMP;

It returns the current date and time.

For example: 2026-09-18 21:39:00

Again, exact formatting depends on the database.

🔍 4. Extracting Year, Month & Day

Suppose: order_date = 2026-09-18

We may want: Year → 2026, Month → 9, Day → 18

A common SQL approach is:

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

Some databases use functions such as YEAR(order_date), MONTH(order_date), DAY(order_date).

So always check the syntax for your SQL dialect.

📊 5. Revenue by Year

Suppose we want annual revenue.

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY order_year;

Result:

• 2024 | 850000

• 2025 | 1120000

• 2026 | 1380000

This lets us analyze year-over-year business performance.

📆 6. Revenue by Month

We can group transactions by month.

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY
    year,
    month;

Important: Don't group only by month number if your data spans multiple years.

For example January 2025 and January 2026 both have month = 1. Grouping only by month could incorrectly combine them.

🗓️ 7. Month-Level Grouping

Some databases provide functions that truncate a date to the beginning of a month.

For example, PostgreSQL:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

This produces values such as 2026-01-01, 2026-02-01 representing each month. This is often convenient for time-series analysis.

📈 8. Month-over-Month Analysis

Suppose we have monthly revenue: Jan 100000, Feb 120000, Mar 110000. We can use LAG():

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue
FROM monthly_sales;

📊 9. Month-over-Month Growth %

We can extend the previous query:

𝗜𝗻𝗳𝗼𝘀𝘆𝘀 𝗠𝗼𝘀𝘁 𝗔𝘀𝗸𝗲𝗱 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 & 𝗔𝗻𝘀𝘄𝗲𝗿𝘀😍
​
✅ Real Interview Experiences
✅ Company-specific Handbook
✅ Interview Process & Preparation Roadmap
✅ FREE Preparation Resources
​
Specialist Programmer :- https://pdlink.in/4xDH2lD
​
​ Systems Engineer :- https://pdlink.in/4xAhGoL
​
​Infosys Digital Specialist Engineer :- https://pdlink.in/4yJ98gb
​
​The best way to prepare is to learn from candidates who've already been through the process.
​

SELECT
    customer_id,
    order_date,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total

FROM orders;

Practice 5

Assign a unique order number to each customer's orders.

SELECT
    customer_id,
    order_id,
    order_date,

    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_number

FROM orders;

🧪 Mini SQL Challenge

You have:

sales

sale_id

customer_id

sale_date

amount

Write a query that returns:

• Customer ID

• Sale date

• Amount

• Previous sale amount

• Difference from previous sale

• Running customer spending

• Customer's transaction number

Solution

SELECT
    customer_id,
    sale_date,
    amount,

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS previous_amount,

    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS difference_from_previous,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_spending,

    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS transaction_number

FROM sales;

What did we use?

LAG()

→ Previous transaction

SUM() OVER()

→ Running spending

ROW_NUMBER()

→ Transaction sequence

PARTITION BY

→ Separate calculations for each customer

ORDER BY

→ Establish chronological order

💡 Double Tap ❤️ For More

SELECT
    customer_id,
    order_date,
    amount,

    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_number,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_spending,

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount

FROM orders;

Now we have:

Order number

+

Running spending

+

Previous order amount

all while preserving the order-level rows.

🏢 26. Real-World Business Example

Suppose a company wants to analyze customer transactions.

We want:

• Transaction date

• Transaction amount

• Previous transaction

• Difference from previous transaction

• Running customer spending

SELECT
    customer_id,
    transaction_date,
    amount,

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) AS previous_amount,

    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) AS amount_change,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_spending

FROM transactions;

This is a realistic analytical SQL pattern.

🎤 SQL Interview Questions

Q1. What is a Window Function?

A function that performs calculations across a set of related rows while retaining the individual rows in the result.

Q2. What does "OVER()" do?

It defines the window of rows over which the function operates.

Q3. What does "PARTITION BY" do?

It divides rows into logical groups for the Window Function.

Q4. Difference between GROUP BY and Window Functions?

"GROUP BY" collapses rows into groups.

Window Functions calculate across rows without collapsing them.

Q5. Difference between RANK() and DENSE_RANK()?

"RANK()" leaves gaps after ties.

"DENSE_RANK()" does not.

Example:

RANK → 1, 2, 2, 4

DENSE_RANK → 1, 2, 2, 3

Q6. Difference between ROW_NUMBER() and RANK()?

"ROW_NUMBER()" gives every row a unique number.

"RANK()" gives tied rows the same rank.

Q7. What does LAG() do?

Returns a value from a previous row.

Q8. What does LEAD() do?

Returns a value from a subsequent row.

Q9. How do you calculate a running total?

SUM(amount) OVER (
    ORDER BY date_column
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
)

Q10. How do you find the top customer in each region?

Use a ranking Window Function with:

PARTITION BY region
ORDER BY total_spending DESC

Then filter the ranking in an outer query or CTE.

📝 Practice Questions

Practice 1

Rank employees by salary.

SELECT
    employee_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Practice 2

Calculate total spending for each customer while keeping every order.

SELECT
    order_id,
    customer_id,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total

FROM orders;

Practice 3

Find the previous order amount for every customer.

SELECT
    customer_id,
    order_date,
    amount,

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_amount

FROM orders;

Practice 4

Calculate a running total for each customer.

SELECT
    order_date,
    amount,

    amount - LAG(amount) OVER (
        ORDER BY order_date
    ) AS change_from_previous

FROM orders;

If:

Previous = 100

Current = 200

then:

200 - 100 = 100

📊 18. Percentage Change

We can calculate percentage change:

SELECT
    order_date,
    amount,

    (
        amount - LAG(amount) OVER (
            ORDER BY order_date
        )
    ) * 100.0
    / NULLIF(
        LAG(amount) OVER (
            ORDER BY order_date
        ),
        0
    ) AS percentage_change

FROM orders;

Here we use:

LAG()

→ Previous value

NULLIF()

→ Prevent division by zero

This combines concepts from earlier parts.

📐 19. Moving Average

Suppose we want a 3-row moving average.

SELECT
    order_date,
    amount,

    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING
                 AND CURRENT ROW
    ) AS moving_average

FROM orders;

For each row, SQL considers:

Current row

+

Previous row

+

Previous 2nd row

This is useful for smoothing short-term fluctuations in time-series data.

🧮 20. Window Functions for Percentage of Total

Suppose we want each product's percentage of total revenue.

SELECT
    product_id,
    revenue,

    revenue * 100.0
    / SUM(revenue) OVER () AS percentage_of_total

FROM product_sales;

Example:

Product A → 20%

Product B → 30%

Product C → 50%

The key advantage:

We don't need a separate query to calculate total revenue.

📊 21. Percentage Within a Group

Suppose we want each customer's percentage of their region's revenue.

SELECT
    customer_id,
    region,
    revenue,

    revenue * 100.0
    / SUM(revenue) OVER (
        PARTITION BY region
    ) AS region_percentage

FROM customer_sales;

The denominator is calculated separately for each region.

🧠 22. Window Functions with CASE

You can combine Window Functions with "CASE".

Example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,

    CASE
        WHEN RANK() OVER (
            ORDER BY total_spending DESC
        ) <= 10
        THEN 'Top 10'
        ELSE 'Other'
    END AS customer_group

FROM customer_sales;

This allows analytical results to be converted into business categories.

⚠️ 23. Window Functions Don't Replace GROUP BY

Window Functions and "GROUP BY" solve different problems.

GROUP BY

Use when you want:

1 row per group

Example:

SELECT
    region,
    SUM(revenue)
FROM sales
GROUP BY region;

Window Function

Use when you want:

Original rows + group-level calculation

Example:

SELECT
    customer_id,
    region,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY region
    ) AS regional_revenue
FROM sales;

⚠️ 24. Window Function Order Matters

For rankings:

RANK() OVER (
    ORDER BY revenue DESC
)

and:

RANK() OVER (
    ORDER BY revenue ASC
)

produce completely different rankings.

Always ask:

«What should rank 1 represent?»

Highest value?

Use:

DESC

Lowest value?

Use:

ASC

🔍 25. Multiple Window Functions

You can use several Window Functions in one query.

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_num
FROM employees;

Even if two employees have the same salary, they receive different row numbers.

Example:

90000 → 1

80000 → 2

80000 → 3

70000 → 4

🆚 10. RANK vs DENSE_RANK vs ROW_NUMBER

ROW_NUMBER

→ Every row gets a unique number

RANK

→ Ties share rank + gaps appear

DENSE_RANK

→ Ties share rank + no gaps

🏆 11. Top Customer per Region

Suppose we want the highest-spending customer in each region.

First calculate spending:

WITH customer_sales AS (
    SELECT
        customer_id,
        region,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY
        customer_id,
        region
)

SELECT
    customer_id,
    region,
    total_spending,

    RANK() OVER (
        PARTITION BY region
        ORDER BY total_spending DESC
    ) AS regional_rank

FROM customer_sales;

The important part is:

PARTITION BY region

This restarts the ranking for each region.

🎯 12. Top 1 Per Group

If we want only the top customer from each region:

WITH customer_sales AS (
    SELECT
        customer_id,
        region,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY
        customer_id,
        region
),

ranked_customers AS (
    SELECT
        customer_id,
        region,
        total_spending,

        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY total_spending DESC
        ) AS rn

    FROM customer_sales
)

SELECT
    customer_id,
    region,
    total_spending
FROM ranked_customers
WHERE rn = 1;

This is one of the most frequently used Window Function patterns in SQL interviews.

➕ 13. Running Total

Suppose we have:

order_date| amount

Jan 1| 100

Jan 2| 200

Jan 3| 150

Jan 4| 300

We want:

order_date| amount| running_total

Jan 1| 100| 100

Jan 2| 200| 300

Jan 3| 150| 450

Jan 4| 300| 750

Use:

SELECT
    order_date,
    amount,

    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total

FROM orders;

The calculation builds cumulatively:

100

100 + 200

100 + 200 + 150

100 + 200 + 150 + 300

📅 14. Running Total by Customer

We can combine "PARTITION BY" and "ORDER BY".

SELECT
    customer_id,
    order_date,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total

FROM orders;

Now each customer's running total is calculated independently.

⏮️ 15. LAG()

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

Example:

SELECT
    order_date,
    amount,

    LAG(amount) OVER (
        ORDER BY order_date
    ) AS previous_amount

FROM orders;

Result:

order_date| amount| previous_amount

Jan 1| 100| NULL

Jan 2| 200| 100

Jan 3| 150| 200

Jan 4| 300| 150

This is extremely useful for:

• Month-over-month analysis

• Previous transaction comparison

• Trend analysis

• Customer behavior

⏭️ 16. LEAD()

"LEAD()" does the opposite.

It looks at the next row.

SELECT
    order_date,
    amount,

    LEAD(amount) OVER (
        ORDER BY order_date
    ) AS next_amount

FROM orders;

Result:

order_date| amount| next_amount

Jan 1| 100| 200

Jan 2| 200| 150

Jan 3| 150| 300

Jan 4| 300| NULL

Easy memory:

LAG

→ Look backward

LEAD

→ Look forward

📈 17. Calculate Change from Previous Row

"LAG()" becomes more useful when combined with arithmetic.

🚀 SQL Roadmap 2026 — Part 14

🪟 SQL Window Functions — Advanced Analytics Without Losing Rows

Window Functions are one of the most powerful features in SQL.

They allow you to perform calculations across related rows without collapsing the rows into a single result.

This is the key difference between:

GROUP BY

→ combines rows

Window Function

→ keeps the rows and calculates across them

Window Functions are heavily used for:

• Rankings

• Running totals

• Moving averages

• Previous/next row comparisons

• Customer analysis

• Sales analysis

• Time-series analysis

• Percentage calculations

• Top-N analysis

🧠 1. The Problem Window Functions Solve

Suppose we have:

order_id| customer_id| amount

1| 101| 500

2| 101| 800

3| 102| 300

4| 102| 700

If we use:

SELECT
    customer_id,
    SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id;

we get:

customer_id| total_spending

101| 1300

102| 1000

The individual orders disappear.

But what if we want:

order_id| customer_id| amount| customer_total

1| 101| 500| 1300

2| 101| 800| 1300

3| 102| 300| 1000

4| 102| 700| 1000

This is where a Window Function is useful.

🪟 2. Basic Window Function Syntax

The general structure is:

function_name(...) OVER (
    PARTITION BY ...
    ORDER BY ...
)

Example:

SELECT
    order_id,
    customer_id,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total

FROM orders;

The result keeps every order while calculating the customer's total.

🔹 3. What Does OVER() Mean?

"OVER()" tells SQL:

«Perform this calculation across a window of rows.»

For example:

SUM(amount) OVER ()

means:

«Calculate the sum across all rows while keeping every row.»

Example:

SELECT
    order_id,
    amount,
    SUM(amount) OVER () AS total_revenue
FROM orders;

Every row will show the overall revenue.

📊 4. PARTITION BY

"PARTITION BY" divides the data into logical groups.

Example:

SUM(amount) OVER (
    PARTITION BY customer_id
)

means:

«Calculate the sum separately for each customer.»

All Orders

↓

Partition by customer

↓

Customer 101 → calculate separately

Customer 102 → calculate separately

Customer 103 → calculate separately

Unlike "GROUP BY", the original rows remain visible.

🆚 5. GROUP BY vs Window Function

GROUP BY

SELECT
    customer_id,
    SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id;

Result:

1 row per customer

Window Function

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS total_spending
FROM orders;

Result:

1 row per order

Remember:

GROUP BY reduces rows.

Window Functions preserve rows.

🏆 6. RANK()

"RANK()" assigns a ranking to rows.

Example:

SELECT
    employee_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

If salaries are:

90000

80000

70000

the ranks are:

1

2

3

🥇 7. Ranking with Ties

Suppose salaries are:

90000

80000

80000

70000

Using "RANK()":

90000 → 1

80000 → 2

80000 → 2

70000 → 4

Notice that rank 3 is skipped.

That's how "RANK()" handles ties.

🔢 8. DENSE_RANK()

"DENSE_RANK()" also handles ties but does not skip the next rank.

For:

90000

80000

80000

70000

the result is:

90000 → 1

80000 → 2

80000 → 2

70000 → 3

Difference:

RANK()

1, 2, 2, 4

DENSE_RANK()

1, 2, 2, 3

🔢 9. ROW_NUMBER()

"ROW_NUMBER()" assigns a unique sequential number to every row.

𝗧𝗼𝗽 𝟭𝟱 𝗣𝘆𝘁𝗵𝗼𝗻 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗬𝗼𝘂 𝗠𝗨𝗦𝗧 𝗞𝗻𝗼𝘄! 🔥

Preparing for a Python Developer or Data Analyst interview?

Strengthen your fundamentals with these essential interview topics.

🎯 Perfect for Students • Freshers • Python Learners • Data Analyst Aspirants

🔗 𝗚𝗲𝘁 𝘁𝗵𝗲 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 👇

https://pdlink.in/3TAUwk7

📌Save this for your next interview and share it with a friend!

Practice 4 — Rank customers by total spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
RANK() OVER ( ORDER BY total_spending DESC ) AS spending_rank
FROM customer_sales;

Practice 5 — Create customer segments based on spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
CASE WHEN total_spending >= 100000 THEN 'VIP'
     WHEN total_spending >= 50000 THEN 'Premium'
     ELSE 'Standard' END AS segment
FROM customer_sales;

🧪 Mini SQL Challenge

You have: orders(order_id, customer_id, amount, order_date)

Find the top 5 customers by total spending, but only consider customers who have placed at least 3 orders.

Solution:

WITH customer_metrics AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
),
qualified_customers AS (
    SELECT customer_id, order_count, total_spending
    FROM customer_metrics WHERE order_count >= 3
)

SELECT customer_id, order_count, total_spending
FROM qualified_customers ORDER BY total_spending DESC LIMIT 5;

Logic:

Orders ↓

GROUP BY customer ↓

Calculate order count + spending ↓

Keep customers with ≥ 3 orders ↓

Sort by spending ↓

Return top 5

💡 Double Tap ❤️ For More

Recursive CTEs are an advanced topic, but understanding their purpose is important.

🧠 17. Why CTEs Are Valuable for Data Analysts

Imagine a business request: «Identify customers who increased their spending, classify them by segment, compare them against the average, and calculate their rank.»

Trying to write everything as one giant query can become difficult.

CTEs allow you to think in stages:

• Raw Orders ↓

• Customer Metrics ↓

• Customer Segmentation ↓

• Average Comparison ↓

• Ranking ↓

• Final Report

Each step has a clear purpose.

⚠️ 18. Common CTE Mistakes

Mistake 1 — Forgetting the CTE name

Incorrect: WITH ( SELECT ... )

Correct: WITH customer_sales AS ( SELECT ... )

Mistake 2 — Forgetting the comma between CTEs

Incorrect: Two CTEs without comma

Correct: Separate CTEs with a comma

Mistake 3 — Using a CTE without understanding its grain

A CTE might produce: 1 row = 1 order while you think it produces: 1 row = 1 customer

Always validate the grain.

Mistake 4 — Creating too many unnecessary CTEs

CTEs should make logic clearer. If every two-line transformation becomes its own CTE, the query can become harder to follow. Use them when they improve structure.

🔎 19. Debugging with CTEs

One major advantage is easier debugging.

Suppose your final query produces incorrect revenue. Instead of debugging one huge query, test each stage.

First:

WITH customer_sales AS (
    ...
)
SELECT *
FROM customer_sales;

• Check the results.

• Then add the next CTE.

• This allows you to identify exactly where the numbers become incorrect.

🎤 SQL Interview Questions

•

Q1. What is a CTE?

A Common Table Expression is a named temporary result set defined using the "WITH" clause and available to the query that follows it.

•

Q2. What is the syntax of a CTE?

WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name;

•

Q3. Can you create multiple CTEs?

Yes. WITH cte1 AS ( ... ), cte2 AS ( ... ) SELECT ... FROM cte2;

•

Q4. Can one CTE reference another CTE?

Yes. A later CTE can generally reference an earlier CTE in the same "WITH" clause.

•

Q5. What is the difference between a CTE and a subquery?

Both can represent intermediate query results, but CTEs often make multi-step logic easier to read and reuse within the same statement.

•

Q6. Does a CTE permanently store data?

No. A standard CTE is associated with the SQL statement in which it is defined.

•

Q7. Does using a CTE always improve performance?

No. CTEs primarily improve query organization and readability. Performance depends on the database engine and execution plan.

•

Q8. What is a recursive CTE?

A CTE that references itself, typically used for hierarchical or recursive data.

•

Q9. Why are CTEs useful in analytics?

They allow complex analytical logic to be divided into clear, manageable stages.

•

Q10. What should you check when using multiple CTEs?

Check the grain, row count, joins, aggregations, and filters at each stage.

📝 Practice Questions

Practice 1 — Calculate total spending per customer using a CTE.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)

SELECT * FROM customer_sales;

Practice 2 — Find customers spending more than ₹50,000.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)

SELECT * FROM customer_sales WHERE total_spending > 50000;

Practice 3 — Calculate order count and revenue per customer.

WITH customer_metrics AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_revenue
    FROM orders GROUP BY customer_id
)
SELECT * FROM customer_metrics;

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY customer_id
)

SELECT
    SUM(revenue) AS total_revenue,
    COUNT(*) AS active_customers,
    SUM(revenue) / NULLIF(COUNT(*), 0) AS revenue_per_customer
FROM customer_revenue;

The CTE first creates: 1 row = 1 customer

Then the final query calculates KPIs from that customer-level dataset.

🧱 14. CTE for Multi-Step Analytics

Let's build a slightly more realistic analysis.

• Step 1 — Calculate customer sales

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

• Step 2 — Create customer segments

, segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

• Step 3 — Analyze segments

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

The complete query:

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

This is a good example of structured analytical SQL.

🪟 15. CTE + Window Functions Preview

CTEs become especially powerful when combined with window functions.

For example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,
    RANK() OVER (
        ORDER BY total_spending DESC
    ) AS spending_rank
FROM customer_sales;

• The CTE creates the customer-level metric.

• The window function ranks the customers.

• This pattern is extremely common in analytics.

🔄 16. Recursive CTEs

There is another advanced type of CTE: Recursive CTE

It allows a query to repeatedly reference itself.

Common use cases include:

• organizational hierarchies

• employee-manager structures

• category trees

• folder structures

• graph-like relationships

• generating sequences

Example structure:

WITH RECURSIVE employee_tree AS (
    SELECT
        employee_id,
        employee_name,
        manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id
    FROM employees e
    JOIN employee_tree t
        ON e.manager_id = t.employee_id
)

SELECT *
FROM employee_tree;

The CTE changes the grain to: 1 row = 1 customer

That makes the final filtering straightforward.

📈 6. CTE + JOIN

CTEs become even more useful when combined with JOINs.

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    c.customer_name,
    cs.total_spending
FROM customers c
JOIN customer_sales cs
    ON c.customer_id = cs.customer_id;

• The CTE handles the aggregation.

• The main query handles the customer information.

💰 7. CTE + COALESCE

Want to include customers who haven't placed orders?

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(cs.total_spending, 0) AS total_spending
FROM customers c
LEFT JOIN customer_sales cs
    ON c.customer_id = cs.customer_id;

Now customers without orders appear with: total_spending = 0

🧮 8. CTE + CASE

We can also create business segments.

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,

    CASE
        WHEN total_spending >= 100000 THEN 'VIP'
        WHEN total_spending >= 50000 THEN 'Premium'
        ELSE 'Standard'
    END AS customer_segment

FROM customer_sales;

• The CTE creates the metric.

• "CASE" converts the metric into business categories.

🔍 9. CTE vs Subquery

Both can solve similar problems.

• Subquery

SELECT *
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;

• CTE

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT *
FROM customer_sales
WHERE total_spending > 50000;

The CTE often makes multi-step logic easier to read.

🧠 10. CTE vs Temporary Table

A CTE is not the same as a permanent table.

• CTE

WITH sales AS (...)
SELECT ...

Generally exists only for the duration of that SQL statement.

• Temporary table

CREATE TEMP TABLE sales AS
SELECT ...;

A temporary table can generally be referenced by multiple statements during its session, depending on the database.

Simple distinction:

• CTE → temporary named query result

• Temporary table → temporary database object

⚡ 11. CTE Does Not Automatically Mean Faster

A common misconception is:

CTEs make queries faster. Not necessarily.

CTEs primarily improve:

• readability

• organization

• maintainability

• debugging

• step-by-step logic

Performance depends on the database engine and how it optimizes the query. Some databases may inline a CTE, while others may materialize it in certain situations.

So: Use CTEs for clear logic, not simply because you expect better performance.

🧪 12. CTE for Data Quality Analysis

Suppose we want to identify customers with missing contact information.

First create a cleaned customer dataset:

WITH cleaned_customers AS (
    SELECT
        customer_id,
        TRIM(customer_name) AS customer_name,
        LOWER(TRIM(email)) AS email
    FROM customers
)

SELECT *
FROM cleaned_customers
WHERE email IS NULL;

This creates a clean intermediate layer before analysis.

📊 13. CTE for KPI Calculation

Suppose we want: «Revenue per customer.»

🚀 SQL Roadmap 2026 — Part 13

🧱 SQL CTEs — Writing Complex Queries in Simple Steps

As SQL queries become more advanced, they can become difficult to read.

You may have:

• JOINs

• Subqueries

• GROUP BY

• CASE

• Aggregations

• Multiple calculations

all inside one query.

This is where CTEs become extremely useful.

CTE stands for: «Common Table Expression»

A CTE lets you create a temporary named result set and then use it in your main query.

Think of it as:

• Step 1 → Prepare the data

• Step 2 → Transform the data

• Step 3 → Analyze the data

• Step 4 → Return the result

🧠 1. Basic CTE Syntax

A CTE starts with:

WITH cte_name AS (
    SELECT
        ...
    FROM ...
)
SELECT *
FROM cte_name;

Example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT *
FROM customer_sales;

The CTE customer_sales acts like a temporary result set for the duration of the query.

📊 2. Why Use CTEs?

Without a CTE, a complex query can become difficult to understand.

For example:

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > 50000;

With a CTE:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending
FROM customer_totals
WHERE total_spending > 50000;

The second version is often much easier to read.

🧩 3. CTEs Break Complex Problems into Steps

Suppose the business question is:

«Find customers whose total spending is above the average customer spending.»

Instead of writing everything as one large nested query, break it into logical steps.

• Step 1 — Calculate spending per customer

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

• Step 2 — Calculate average spending

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

average_spending AS (
    SELECT
        AVG(total_spending) AS avg_spending
    FROM customer_totals
)

• Step 3 — Compare customers with the average

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

average_spending AS (
    SELECT
        AVG(total_spending) AS avg_spending
    FROM customer_totals
)

SELECT
    c.customer_id,
    c.total_spending
FROM customer_totals c
CROSS JOIN average_spending a
WHERE c.total_spending > a.avg_spending;

Now the logic is much easier to follow.

🔗 4. Multiple CTEs

A single query can contain multiple CTEs.

Structure:

WITH first_cte AS (
    ...
),

second_cte AS (
    ...
),

third_cte AS (
    ...
)

SELECT ...
FROM third_cte;

Later CTEs can reference earlier CTEs.

🏢 5. Real-World Example

Suppose an e-commerce company wants:

«Customers with spending above ₹50,000 and at least 5 orders.»

First calculate customer-level metrics:

WITH customer_metrics AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    order_count,
    total_spending
FROM customer_metrics
WHERE order_count >= 5
  AND total_spending > 50000;

Here are some essential data science concepts from A to Z:

A - Algorithm: A set of rules or instructions used to solve a problem or perform a task in data science.

B - Big Data: Large and complex datasets that cannot be easily processed using traditional data processing applications.

C - Clustering: A technique used to group similar data points together based on certain characteristics.

D - Data Cleaning: The process of identifying and correcting errors or inconsistencies in a dataset.

E - Exploratory Data Analysis (EDA): The process of analyzing and visualizing data to understand its underlying patterns and relationships.

F - Feature Engineering: The process of creating new features or variables from existing data to improve model performance.

G - Gradient Descent: An optimization algorithm used to minimize the error of a model by adjusting its parameters.

H - Hypothesis Testing: A statistical technique used to test the validity of a hypothesis or claim based on sample data.

I - Imputation: The process of filling in missing values in a dataset using statistical methods.

J - Joint Probability: The probability of two or more events occurring together.

K - K-Means Clustering: A popular clustering algorithm that partitions data into K clusters based on similarity.

L - Linear Regression: A statistical method used to model the relationship between a dependent variable and one or more independent variables.

M - Machine Learning: A subset of artificial intelligence that uses algorithms to learn patterns and make predictions from data.

N - Normal Distribution: A symmetrical bell-shaped distribution that is commonly used in statistical analysis.

O - Outlier Detection: The process of identifying and removing data points that are significantly different from the rest of the dataset.

P - Precision and Recall: Evaluation metrics used to assess the performance of classification models.

Q - Quantitative Analysis: The process of analyzing numerical data to draw conclusions and make decisions.

R - Random Forest: An ensemble learning algorithm that builds multiple decision trees to improve prediction accuracy.

S - Support Vector Machine (SVM): A supervised learning algorithm used for classification and regression tasks.

T - Time Series Analysis: A statistical technique used to analyze and forecast time-dependent data.

U - Unsupervised Learning: A type of machine learning where the model learns patterns and relationships in data without labeled outputs.

V - Validation Set: A subset of data used to evaluate the performance of a model during training.

W - Web Scraping: The process of extracting data from websites for analysis and visualization.

X - XGBoost: An optimized gradient boosting algorithm that is widely used in machine learning competitions.

Y - Yield Curve Analysis: The study of the relationship between interest rates and the maturity of fixed-income securities.

Z - Z-Score: A standardized score that represents the number of standard deviations a data point is from the mean.

Credits: https://t.me/free4unow_backup

Like if you need similar content 😄👍

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

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!

⚠️ 23. Common Subquery Mistakes

Mistake 1 — Returning multiple rows with =

Use IN instead of = when multiple rows are expected.

Mistake 2 — Forgetting NULL behavior with NOT IN

Mistake 3 — Ignoring duplicates

Mistake 4 — Making the query unnecessarily complicated

🎤 SQL Interview Questions

Q1. What is a subquery?

A query nested inside another query.

Q2. What is a scalar subquery?

A subquery that returns a single value.

Q3. When should you use IN?

When the subquery returns a set of values.

Q4. What does EXISTS do?

It checks whether at least one matching row exists.

Q5. What is a correlated subquery?

A subquery that references a column from the outer query.

Q6. What is a derived table?

A subquery in the FROM clause treated as a temporary result set.

Q7. Can a subquery be used in SELECT?

Yes.

Q8. What is the difference between IN and EXISTS?

IN compares against a set, while EXISTS checks for existence.

Q9. Why is NOT IN dangerous with NULL?

Three-valued logic can cause unexpected results.

Q10. Can every subquery be replaced with a JOIN?

Many can, but the best approach depends on the logic.

📝 Practice Questions

Practice 1 — Employees earning more than average:

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Practice 2 — Customers with at least one order:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Practice 3 — Customers with more than 5 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

Practice 4 — Products higher than average price:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Practice 5 — Customers who never placed an order:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🧪 Mini SQL Challenge

Find customers whose total spending is greater than the average customer spending.

SELECT customer_id, total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > (
    SELECT AVG(total_spending)
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_spending
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

💡 Double Tap ❤️ For More