5 SQL Queries: You Should not Miss

These five SQL queries (Recursive, CTE, Join, Aggregate, Window, and Exists) are crucial for mastering data manipulation and retrieval. Understanding these queries enables effective operations and optimizes application performance. Familiarity with each will enhance your problem-solving skills in real-time data scenarios.

1. 1. Recursive SQL Query Example
2. 2. Window Functions for Ranking with Example
3. 3. Self-Join for Time-Based Analysis Example
4. 4. Aggregated Data with Grouped Filtering Example
5. 5. Subquery with EXISTS for Filtering Example

1. Recursive SQL Query Example

This query fetches all employees under a manager in an organizational hierarchy. It uses a recursive WITH clause (Common Table Expression – CTE).

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT employee_id, employee_name, manager_id
  FROM employees
  WHERE manager_id IS NULL -- Fetch the top-level manager

  UNION ALL

  SELECT e.employee_id, e.employee_name, e.manager_id
  FROM employees e
  INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Explanation:

• The WITH RECURSIVE clause creates a CTE to recursively fetch employees reporting to their managers.
• The base query fetches the top-level manager (where manager_id is NULL).
• The recursive query joins employees with their managers (in the hierarchy).
• It returns all employees in the hierarchy, starting from the top-level manager.

---

2. Window Functions for Ranking with Example

This query ranks products by their sales volume within each category using the RANK() window function.

SELECT category_id, product_id, product_name, sales_volume, 
       RANK() OVER (PARTITION BY category_id ORDER BY sales_volume DESC) as rank
FROM products;

Explanation:

• The RANK() function is used to assign ranks based on sales_volume.
• PARTITION BY category_id ensures that the ranking is done within each category.
• ORDER BY sales_volume DESC orders products by sales within each category.

---

3. Self-Join for Time-Based Analysis Example

This query calculates the percentage increase in sales month-over-month for each product using a self-join.

SELECT p1.product_id, p1.month, p1.sales AS current_sales, 
       p2.sales AS previous_sales, 
       ((p1.sales - p2.sales) / p2.sales) * 100 AS percentage_increase
FROM sales p1
JOIN sales p2 
  ON p1.product_id = p2.product_id
  AND p1.month = p2.month + INTERVAL '1 month';

Explanation:

• The table sales is joined with itself using product_id and month, where the month difference is 1 month.
• The percentage increase is calculated in two steps. First, subtract the previous month’s sales from the current month’s sales. Second, divide the result by the previous month’s sales.

---

4. Aggregated Data with Grouped Filtering Example

This query finds the average salary of employees in each department. It only includes departments of more than 10 employees.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 10;

Explanation:

• GROUP BY department_id groups employees by department.
• AVG(salary) calculates the average salary within each group.
• HAVING COUNT(employee_id) > 10 filters departments to include only those with more than 10 employees.

---

5. Subquery with EXISTS for Filtering Example

This query finds customers who have placed at least one order.

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

Explanation:

• The EXISTS subquery checks if there is at least one matching row in the orders table for each customer.
• It filters the customers who have placed at least one order.

---

These complex queries show various SQL features like recursive CTEs, window functions, self-joins, aggregate functions, and subqueries with EXISTS.