SQL Advanced Interview Questions

“To calculate a running total, we use window functions like SUM() OVER(), which allows us to accumulate values across rows.”

• Best approach: SUM() OVER()

  • Calculates cumulative sum in a defined order
  • ORDER BY sale_date ensures values are added sequentially

  SELECT 
      sale_date,
      sales_amount,
      SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales;
  

• With PARTITION (if needed)

  • Used when we want running totals within a group, like per region

  SELECT 
      sale_date,
      region,
      sales_amount,
      SUM(sales_amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
  FROM sales;
  

“In simple terms, a running total means current value plus all previous values, and SUM() OVER with ORDER BY handles this automatically.”

“LEAD and LAG are window functions used to access values from the next or previous row without using joins.”

• LAG() — Previous row value

  • Fetches data from the previous row based on ordering
  • First row returns NULL since there is no previous row

  SELECT 
      sale_date,
      sales_amount,
      LAG(sales_amount) OVER (ORDER BY sale_date) AS prev_day_sales
  FROM sales;
  

• LEAD() — Next row value

  • Fetches data from the next row
  • Last row returns NULL since there is no next row

  SELECT 
      sale_date,
      sales_amount,
      LEAD(sales_amount) OVER (ORDER BY sale_date) AS next_day_sales
  FROM sales;
  

• Real use case

  • Compare current value with previous value

  SELECT 
      sale_date,
      sales_amount,
      sales_amount - LAG(sales_amount) OVER (ORDER BY sale_date) AS diff
  FROM sales;
  

“In simple terms, LAG is used to look back to the previous row, and LEAD is used to look ahead to the next row.”

“Window functions are used to perform calculations across a set of rows related to the current row, without reducing the number of rows like GROUP BY.”

• Basic Syntax

  function_name(column) OVER (
      PARTITION BY column
      ORDER BY column
  )
  - OVER() defines the window
  - PARTITION BY splits data into groups
  - ORDER BY defines the sequence within each group
  

1. Aggregate Window Functions

   • Perform calculations like SUM, AVG, COUNT, MIN, MAX without collapsing rows
   • Example: running total

   SUM(sales_amount) OVER (ORDER BY sale_date)
   

2. Ranking Functions

   • Used to assign ranks to rows
   • ROW_NUMBER() → unique sequence
   • RANK() → same rank with gaps
   • DENSE_RANK() → same rank without gaps
   • NTILE(n) → divides rows into groups

3. Value Functions

   • Access values from other rows
   • LAG() → previous row
   • LEAD() → next row
   • FIRST_VALUE() → first value in window
   • LAST_VALUE() → last value in window

4. Window Frame (Advanced)

   • Defines the range of rows used in calculation

   SUM(sales) OVER (
       ORDER BY date
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   )
   - Means from first row to current row
   

“In simple terms, window functions allow us to perform calculations across rows while still keeping each row in the result.”

Suppose we have employees table.

Approach 1 -> Using DENSE_RANK():

DENSE_RANK() assigns the same rank to duplicate salaries and does not skip ranks. Best approach for finding Nth highest salary when duplicates exist.

Find 2nd Highest Salary:

SELECT emp_id, emp_name, salary
FROM (
    SELECT emp_id, emp_name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

Inner query result (how ranks are assigned):

Final Result (rnk = 2):

Both Arjun and Kiran get rank 2 because they have the same salary. DENSE_RANK gives rank 3 to ₹72,000, no rank is skipped.

For Nth Highest, just change the WHERE clause:

WHERE rnk = N  -- Replace N with 3, 4, 5...

Approach 2 -> Using ROW_NUMBER():

ROW_NUMBER() assigns a unique sequential number to every row — even if salaries are duplicate. No two rows get the same number. The result depends on which duplicate row happens to be picked first.

Find 2nd Highest Salary:

SELECT emp_id, emp_name, salary
FROM (
    SELECT emp_id, emp_name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 2;

Inner query result (how row numbers are assigned):

Final Result (rn = 2):

Only one row is returned even though Kiran has the same salary. ROW_NUMBER does not handle duplicates, it just picks one arbitrarily. Use this only when you want exactly one row regardless of ties.

Approach 3 -> Using Subquery:

The classic approach, no window functions needed. Works in older SQL versions too. The idea is: the 2nd highest salary is the maximum salary that is less than the overall maximum.

Find 2nd Highest Salary:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Step 1: Inner query finds the highest salary:

SELECT MAX(salary) FROM employees;  -- ₹95,000

Step 2: Outer query finds the MAX salary below ₹95,000:

SELECT MAX(salary) FROM employees WHERE salary < 95000;  -- ₹85,000

Final Result:

For Nth Highest using Subquery, use LIMIT/OFFSET:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;  -- For 2nd highest: OFFSET 1, for 3rd: OFFSET 2

DISTINCT ensures duplicate salaries are treated as one. OFFSET 1 skips the highest and picks the next.

Window functions perform calculations across a set of related rows without collapsing them into a single group result. Each row keeps its identity while also getting a computed value based on its 'window' of neighboring rows.

The OVER() clause defines the window: PARTITION BY (like GROUP BY but keeps rows), ORDER BY (row order within the partition).

ROW_NUMBER(): Assigns a unique sequential number to each row within the partition. No ties, always 1,2,3,4. Use case: Deduplication (keep rn=1 per group), pagination.

• Example: We have a table called, monthly_sales(region, month, sales)

SELECT
    region, month, sales,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS row_num
FROM monthly_sales
ORDER BY region, month;

RANK(): Same rank for tied rows, then SKIPS the next rank. 1,1,3,4 for ties on position 1. Use case: Sports leaderboards where tied players share the same place.

SELECT
    region, month, sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rnk
FROM monthly_sales
ORDER BY region, month;

DENSE_RANK(): Same rank for tied rows, NO gaps. 1,1,2,3 for ties. Use case: Finding top-N salaries where ties should both count (top 3 salaries per department).

SELECT
    region, month, sales,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS dense_rnk
FROM monthly_sales
ORDER BY region, month;

NTILE(n): Divides rows into n approximately equal groups (buckets) within the partition. Use case: Quartile analysis, divide customers/products into top 25%, bottom 25%, etc.

SELECT
    region, month, sales,
    NTILE(4) OVER (PARTITION BY region ORDER BY sales DESC) AS quartile
FROM monthly_sales
ORDER BY region, month;

LAG(col, n): Returns the value from n rows BEFORE the current row in the ordered window. Use case: Month-over-month comparison, calculate change from previous period.

SELECT
    region, month, sales,
    LAG(sales, 1, 0) OVER (PARTITION BY region ORDER BY month) AS prev_month_sales
FROM monthly_sales
ORDER BY region, month;

• Month-over-Month change using LAG

SELECT
    region, month, sales,
    sales - LAG(sales, 1) OVER (PARTITION BY region ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY region, month;

LEAD(col, n): Returns the value from n rows AFTER the current row. Use case: Predict next period, calculate days until next event.

SELECT
    region, month, sales,
    LEAD(sales, 1, 0) OVER (PARTITION BY region ORDER BY month) AS next_month_sales
FROM monthly_sales
ORDER BY region, month;

FIRST_VALUE(col): Returns the FIRST value in the ordered window. Use case: Compare current row with the highest/lowest value in the partition.

SELECT
    region, month, sales,
    FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY sales DESC) AS highest_sales
FROM monthly_sales
ORDER BY region, month;

LAST_VALUE(col): Returns the LAST value in the ordered window. Use case: Compare current row with the lowest/latest value in the partition.

SELECT
    region, month, sales,
    LAST_VALUE(sales) OVER (PARTITION BY region ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_sales
FROM monthly_sales
ORDER BY region, month;

LAG() and LEAD() are used to access values from the previous and next rows without using self joins. Useful for time-series analysis like month-over-month comparison.

Example Table: monthly_sales

SELECT
    region,
    sale_month,
    sales_amount AS current_month_sales,

    LAG(sales_amount, 1) OVER (PARTITION BY region ORDER BY sale_month) AS previous_month_sales,

    LEAD(sales_amount, 1) OVER (PARTITION BY region ORDER BY sale_month) AS next_month_sales

FROM monthly_sales
ORDER BY region, sale_month;

Result:

Window aggregate functions can be used to calculate running totals and moving averages without grouping rows together.

Example Table: daily_sales

SELECT
    date,
    region,
    sales_amount,

    SUM(sales_amount) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,

    AVG(sales_amount) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7_days

FROM daily_sales
ORDER BY region, date;

Result:

Window functions like DENSE_RANK() can be used to find top-N records within each group while handling ties correctly.

Example Table: sales

SELECT
    category,
    product_name,
    total_sales,
    rank_in_category
FROM (
    SELECT
        category,
        product_name,
        SUM(sale_amount) AS total_sales,

        DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(sale_amount) DESC) AS rank_in_category
    FROM sales
    GROUP BY category, product_name
) ranked_products
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Result:

Year-over-year (YoY) and month-over-month (MoM) growth rates are commonly calculated using window functions like LAG() to compare current revenue with previous periods.

Example Table: monthly_revenue

WITH revenue_data AS (
    SELECT
        year,
        month,
        region,
        revenue,
        LAG(revenue, 1) OVER (PARTITION BY region ORDER BY year, month) AS previous_month_revenue,

        LAG(revenue, 12) OVER (PARTITION BY region ORDER BY year, month) AS previous_year_revenue
    FROM monthly_revenue
)

SELECT
    year,
    month,
    region,
    revenue,

    ROUND(((revenue - previous_month_revenue) * 100.0) / previous_month_revenue, 2) AS mom_growth_percent,

    ROUND(((revenue - previous_year_revenue) * 100.0) / previous_year_revenue, 2) AS yoy_growth_percent
FROM revenue_data
ORDER BY region, year, month

Result:

MERGE statement is commonly used in incremental data loads to perform UPSERT operations, where existing records are updated and new records are inserted in a single statement.

Example Tables: In Prod: production.orders

In Staging: staging.orders

MERGE INTO production.orders AS target

USING (
    SELECT
        order_id,
        customer_id,
        amount,
        status,
        modified_date
    FROM staging.orders
) AS source

ON target.order_id = source.order_id

WHEN MATCHED AND source.modified_date > target.modified_date

THEN UPDATE SET
    target.amount = source.amount,
    target.status = source.status,
    target.modified_date = source.modified_date

WHEN NOT MATCHED BY TARGET

THEN INSERT (
    order_id,
    customer_id,
    amount,
    status,
    modified_date
)

VALUES (
    source.order_id,
    source.customer_id,
    source.amount,
    source.status,
    source.modified_date
);

Result in production.orders:

Explanation:

• order_id = 102 existed already, so it was updated with newer data.
• order_id = 103 was unchanged because modified_date was same.
• order_id = 104 did not exist in target table, so it was inserted as a new record.

ROW_NUMBER() is commonly used to remove duplicate records while keeping only the latest record for each key.

Example Table: orders

Goal:

• Keep only the latest record for each order_id
• Remove older duplicate records

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        amount,
        created_at,

        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn
    FROM orders
)

DELETE FROM ranked_orders
WHERE rn > 1;

Explanation:

• PARTITION BY order_id groups duplicate records for each order.
• ORDER BY created_at DESC assigns rank 1 to the latest record.
• Older duplicate records get ranks 2, 3, ...
• DELETE WHERE rn > 1 removes all older duplicates.

Result After Removing Duplicates:

Customers who placed orders in one year but did not place any orders in the following year can be identified using anti-join techniques like EXCEPT, NOT IN, and NOT EXISTS. This is commonly used for churn detection analysis.

Example Table: orders

Goal:

• Find customers who placed orders in 2025
• But did NOT place any orders in 2026

Method 1: Using EXCEPT

SELECT DISTINCT customer_id FROM orders WHERE YEAR(order_date) = 2025

EXCEPT

SELECT DISTINCT customer_id FROM orders WHERE YEAR(order_date) = 2026

Result:

Explanation:

• Returns customers present in 2025 but missing in 2026.
• EXCEPT performs set difference between two result sets.

Method 2: Using NOT IN

SELECT DISTINCT customer_id
FROM orders
WHERE YEAR(order_date) = 2025
AND customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2026
)

Result:

Explanation:

• First selects customers from 2025.
• Then removes customers who also appeared in 2026.

Method 3: Using NOT EXISTS

SELECT DISTINCT o1.customer_id
FROM orders o1
WHERE YEAR(o1.order_date) = 2025
AND NOT EXISTS (
    SELECT 1 FROM orders o2
    WHERE o2.customer_id = o1.customer_id
      AND YEAR(o2.order_date) = 2026
)

Result:

Explanation:

• Checks each 2025 customer individually.
• Returns only customers for whom no matching 2026 order exists.
• Commonly used in production systems for anti-join filtering.

Consecutive login streaks can be identified using the gaps-and-islands pattern with ROW_NUMBER(). If consecutive dates are shifted by their row number, all continuous login dates produce the same group value.

Example Table: logins

Goal:

• Find each user's longest consecutive login streak
• Return streak start date, end date, and streak length

WITH numbered AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM logins
),

grouped AS (
    SELECT
        user_id,
        login_date,
        DATEADD(DAY, -rn, login_date) AS grp
    FROM numbered
),

streaks AS (
    SELECT
        user_id,
        MIN(login_date) AS streak_start,
        MAX(login_date) AS streak_end,
        COUNT(*) AS streak_length
    FROM grouped
    GROUP BY user_id, grp
)

SELECT
    user_id,
    streak_start,
    streak_end,
    streak_length
FROM streaks
WHERE streak_length = (
    SELECT MAX(streak_length)
    FROM streaks s2
    WHERE s2.user_id = streaks.user_id
)

ORDER BY streak_length DESC;

Result:

Explanation:

• ROW_NUMBER() assigns sequential numbers to each login date per user.
• DATEADD(DAY, -rn, login_date) creates the same group value for consecutive dates.
• Continuous login days form one group (island).
• Final query returns the longest streak for each user.

Session analysis is commonly used to group user activities into sessions based on inactivity timeout rules. A new session starts when the gap between two consecutive events exceeds 30 minutes.

Example Table: events

Goal:

• Group user events into sessions
• Start a new session if inactivity gap is greater than 30 minutes
• Return session start, end, duration, and event count

WITH session_starts AS (
    SELECT
        user_id,
        event_time,
        event_type,
        page_url,

        CASE
            WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL THEN 1
            WHEN DATEDIFF(MINUTE, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM events
),

session_ids AS (
    SELECT *,
        SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_num
    FROM session_starts
)

SELECT
    user_id,
    session_num,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*) AS event_count,
    COUNT(DISTINCT page_url) AS unique_pages,
    DATEDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS session_duration_min
FROM session_ids
GROUP BY user_id, session_num
ORDER BY user_id, session_num;

Result:

Explanation:

• LAG() retrieves the previous event time for each user.
• If the gap between events is greater than 30 minutes, a new session starts.
• SUM(is_new_session) generates unique session numbers.
• Final aggregation calculates session metrics like duration, event count, and pages visited.

Slowly Changing Dimension (SCD) Type 2 is used to maintain full historical changes in dimension tables. Instead of overwriting old data, the existing record is expired and a new version is inserted with updated values.

Example Table: dim_customer

Source Table: source_customers

Goal:

• Keep full history of customer changes
• Expire old records when values change
• Insert new versions with updated information

-- Step 1: Expire existing changed records

MERGE INTO dim_customer AS tgt
USING (
    SELECT
        customer_id,
        name,
        address
    FROM source_customers
) AS src

ON tgt.customer_id = src.customer_id AND tgt.is_current = 1

WHEN MATCHED AND tgt.address <> src.address

THEN UPDATE SET
    tgt.end_date   = CAST(GETDATE() AS DATE),
    tgt.is_current = 0;

-- Step 2: Insert brand new customers

MERGE INTO dim_customer AS tgt
USING (
    SELECT
        customer_id,
        name,
        address
    FROM source_customers
) AS src
ON tgt.customer_id = src.customer_id
AND tgt.is_current = 1

WHEN NOT MATCHED BY TARGET

THEN INSERT (
    customer_id,
    name,
    address,
    start_date,
    end_date,
    is_current
)

VALUES (
    src.customer_id,
    src.name,
    src.address,
    CAST(GETDATE() AS DATE),
    NULL,
    1
);

-- Step 3: Insert new version for updated customers

INSERT INTO dim_customer (
    customer_id,
    name,
    address,
    start_date,
    end_date,
    is_current
)

SELECT
    src.customer_id,
    src.name,
    src.address,
    CAST(GETDATE() AS DATE),
    NULL,
    1
FROM source_customers src
JOIN dim_customer tgt ON src.customer_id = tgt.customer_id
WHERE tgt.is_current = 0
AND tgt.end_date = CAST(GETDATE() AS DATE);

Result in dim_customer:

Explanation:

• Customer 101 changed address from Bangalore to Hyderabad.
• Existing record was expired by setting end_date and is_current = 0.
• A new active version was inserted with updated address.
• Customer 103 was a new customer, so a new row was inserted directly.
• SCD Type 2 preserves complete historical data instead of overwriting old values.

Running balance calculation is commonly implemented using window functions with cumulative aggregation. Deposits increase the balance, while withdrawals decrease it.

Example Table: transactions

Goal:

• Compute running account balance after each transaction
• Treat deposits as positive amounts
• Treat withdrawals as negative amounts
• Detect overdrawn accounts

SELECT
    txn_id,
    account_id,
    txn_date,
    txn_type,
    amount,

    CASE
        WHEN txn_type = 'DEPOSIT' THEN amount
        ELSE -amount
    END AS signed_amount,

    SUM(
        CASE
            WHEN txn_type = 'DEPOSIT' THEN amount
            ELSE -amount
        END
    ) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance,
    CASE
        WHEN SUM(
            CASE
                WHEN txn_type = 'DEPOSIT' THEN amount
                ELSE -amount
            END
        ) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) < 0 THEN 'OVERDRAWN'
        ELSE 'OK'
    END AS balance_status
FROM transactions
ORDER BY account_id, txn_date, txn_id;

Result:

Explanation:

• Deposits are converted to positive amounts.
• Withdrawals are converted to negative amounts.
• SUM() OVER() calculates cumulative running balance for each account.
• ORDER BY txn_date, txn_id ensures correct transaction sequence.
• Accounts with negative balance are marked as OVERDRAWN.

Pivoting is used to transform row-based data into column-based format for reporting and analytics. This can be implemented using either CASE WHEN aggregation or database-specific PIVOT syntax.

Example Table: monthly_sales

Goal:

• Show one row per region
• Convert months into separate columns
• Display monthly sales values

Method 1: Using PIVOT

SELECT
    region,
    [Jan],
    [Feb],
    [Mar],
    [Apr]
FROM monthly_sales
PIVOT (
    SUM(sales_amount)
    FOR month IN (
        [Jan],
        [Feb],
        [Mar],
        [Apr]
    )
) AS pivot_result
ORDER BY region;

Result:

Explanation:

• PIVOT automatically transforms row values into columns.
• FOR month IN (...) defines pivoted column names.
• Commonly used in SQL Server reporting queries.

Method 2: Using CASE WHEN

SELECT
    region,

    SUM(
        CASE
            WHEN month = 'Jan' THEN sales_amount
            ELSE 0
        END
    ) AS Jan_Sales,

    SUM(
        CASE
            WHEN month = 'Feb' THEN sales_amount
            ELSE 0
        END
    ) AS Feb_Sales,

    SUM(
        CASE
            WHEN month = 'Mar' THEN sales_amount
            ELSE 0
        END
    ) AS Mar_Sales,

    SUM(
        CASE
            WHEN month = 'Apr' THEN sales_amount
            ELSE 0
        END
    ) AS Apr_Sales,

    SUM(sales_amount) AS Total_Sales
FROM monthly_sales
GROUP BY region
ORDER BY region;

Result:

Explanation:

• CASE WHEN converts month values into separate conditional columns.
• SUM() aggregates sales for each month.
• Works across almost all relational databases.

Overlapping date ranges are commonly checked to identify conflicting bookings, employee assignments, or resource scheduling issues. Two date ranges overlap when:

A (start) <= B (end) AND A (end) >= B (start)

Example Table: bookings

Goal:

• Find bookings that overlap for the same resource
• Identify conflicting booking periods
• Return overlap start and end dates

SELECT
    a.booking_id AS booking_a,
    b.booking_id AS booking_b,
    a.resource_id,
    a.customer_id AS customer_a,
    b.customer_id AS customer_b,
    a.start_date AS a_start,
    a.end_date   AS a_end,
    b.start_date AS b_start,
    b.end_date   AS b_end,

    CASE
        WHEN a.start_date > b.start_date THEN a.start_date
        ELSE b.start_date
    END AS overlap_start,

    CASE
        WHEN a.end_date < b.end_date THEN a.end_date
        ELSE b.end_date
    END AS overlap_end
FROM bookings a
JOIN bookings b ON a.resource_id = b.resource_id
                AND a.booking_id < b.booking_id
                AND a.start_date <= b.end_date
                AND a.end_date   >= b.start_date
ORDER BY a.resource_id, overlap_start;

Result:

Explanation:

• Bookings are self-joined on the same resource_id.
• a.booking_id < b.booking_id avoids duplicate comparisons like (A,B) and (B,A).
• Overlap condition checks whether two date ranges intersect.
• overlap_start and overlap_end calculate the actual conflicting period between bookings.

Recursive CTEs are commonly used to traverse hierarchical data such as employee-manager relationships, category trees, or organizational charts.

A recursive CTE contains:

1. Anchor query → starting/root rows
2. Recursive query → repeatedly joins child rows to parent rows

Example Table: employees

Goal:

• Display full employee hierarchy
• Show manager → subordinate relationships
• Display organization depth level

WITH org_chart AS (
    SELECT
        emp_id,
        name,
        manager_id,
        0 AS level,
        CAST(name AS VARCHAR(500)) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.name,
        e.manager_id,
        o.level + 1,
        CAST(
            o.hierarchy_path + ' > ' + e.name
            AS VARCHAR(500)
        ) AS hierarchy_path
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.emp_id
)

SELECT
    emp_id,
    REPLICATE('  ', level) + name AS indented_name,
    level AS org_level,
    hierarchy_path
FROM org_chart
ORDER BY hierarchy_path
OPTION (MAXRECURSION 100)

Result:

Explanation:

• Anchor query starts from top-level employees where manager_id IS NULL.
• Recursive query repeatedly joins employees with their managers.
• level tracks hierarchy depth.
• hierarchy_path shows the complete reporting chain.
• MAXRECURSION prevents infinite loops caused by cyclic hierarchy data.

Gap detection is commonly used in data quality checks to identify missing sequence values such as missing order IDs, invoice numbers, or ticket numbers.

Example Table: orders

Goal:

• Identify missing order IDs
• Detect gaps in sequential numbering
• Return missing ranges and individual missing IDs

Method 1: Using LAG() to Detect Gap Ranges

SELECT
    prev_id + 1 AS gap_start,
    order_id - 1 AS gap_end,
    order_id - prev_id - 1 AS gap_size
FROM (
    SELECT
        order_id,
        LAG(order_id) OVER (ORDER BY order_id) AS prev_id
    FROM orders
) t
WHERE order_id - prev_id > 1
ORDER BY gap_start

Result:

Explanation:

• LAG() retrieves the previous order ID.
• If difference between current and previous ID is greater than 1, a gap exists.
• Query returns start and end of missing ranges.

Method 2: Generate Expected Sequence + Anti Join

WITH expected_ids AS (
    SELECT TOP (100)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
),

min_max AS (
    SELECT
        MIN(order_id) AS min_id,
        MAX(order_id) AS max_id
    FROM orders
)

SELECT
    e.id AS missing_id
FROM expected_ids e
CROSS JOIN min_max mm
WHERE e.id BETWEEN mm.min_id AND mm.max_id
AND NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.order_id = e.id
)
ORDER BY e.id;

Result:

Explanation:

• Generates complete expected ID sequence.
• NOT EXISTS identifies IDs missing from the orders table.
• Commonly used in data validation and reconciliation processes.

Method 3: PostgreSQL generate_series()

SELECT generate_series AS missing_id
FROM generate_series(
    (SELECT MIN(order_id) FROM orders),
    (SELECT MAX(order_id) FROM orders)
)

EXCEPT

SELECT order_id FROM orders
ORDER BY missing_id;

Result:

Explanation:

• generate_series() creates complete sequential IDs.
• EXCEPT removes existing IDs.
• Remaining values are missing IDs.
• Very clean and efficient PostgreSQL solution.