Common Table Expressions (CTEs)

Q1: Please share the top 5 real-time questions along with their SQL queries for Common Table Expressions (CTEs) in SQL.

Ans: Here are five real-time questions along with their SQL queries that involve the use of Common Table Expressions (CTEs):

1. Employee Hierarchy:

• Question: Retrieve the hierarchical structure of employees along with their managers using a CTE.
• Query:

  WITH RecursiveCTE 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 RecursiveCTE r ON e.manager_id = r.employee_id
  )
  SELECT * FROM RecursiveCTE;

2. Monthly Sales Growth:

• Question: Calculate the month-over-month growth percentage in sales using a CTE.
• Query:

  WITH MonthlySalesCTE AS (
      SELECT sale_date, sales,
          LAG(sales) OVER (ORDER BY sale_date) AS prev_month_sales
      FROM sales
  )
  SELECT
      sale_date,
      sales,
      (sales - prev_month_sales) / prev_month_sales * 100 AS growth_percentage
  FROM MonthlySalesCTE;

3. Recursive CTE for Organization Chart:

• Question: Create an organization chart using a recursive CTE to show employees and their subordinates.
• Query:

  WITH RecursiveOrgChart 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 RecursiveOrgChart r ON e.manager_id = r.employee_id
  )
  SELECT * FROM RecursiveOrgChart;

4. Top N Products by Category:

• Question: Retrieve the top N products by sales within each category using a CTE.
• Query:

  WITH RankedProductsCTE AS (
      SELECT
          product_id,
          product_name,
          category,
          sales,
          ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
      FROM products
  )
  SELECT * FROM RankedProductsCTE WHERE rank <= 5;

5. Running Total of Revenue:

• Question: Calculate the running total of revenue over time using a CTE.
• Query:

  WITH RunningTotalCTE AS (
      SELECT
          sale_date,
          SUM(revenue) OVER (ORDER BY sale_date) AS running_total
      FROM sales
  )
  SELECT * FROM RunningTotalCTE;

These questions and queries cover various scenarios where Common Table Expressions can be applied to solve real-world problems, such as hierarchical data representation, sales analysis, organizational charts, and ranking.