---
title: "5 SQL Queries: You Should not Miss"
description: "The content outlines five essential SQL queries—recursive, window, self-join, aggregate filtering, and EXISTS—to improve query-writing skills for tough interviews."
url: https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/
date: 2024-10-01
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/10/image-1.png?fit=1024%2C768&ssl=1
categories: ["SQL"]
tags: ["Featured post", "SQL Queries for Data Analytics"]
type: post
lang: en
---

# 5 SQL Queries: You Should not Miss

These five [SQL queries](https://srinimf.com/2014/10/12/db2-sql-with-joins-performance/) (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](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/#1-recursive-sql-query-example)
2. [2. Window Functions for Ranking with Example](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/#2-window-functions-for-ranking-with-example)
3. [3. Self-Join for Time-Based Analysis Example](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/#3-self-join-for-time-based-analysis-example)
4. [4. Aggregated Data with Grouped Filtering Example](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/#4-aggregated-data-with-grouped-filtering-example)
5. [5. Subquery with EXISTS for Filtering Example](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/#5-subquery-with-exists-for-filtering-example)

![Important queries in SQL for interview](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/10/pexels-photo-9166425.jpeg?resize=1880%2C1253&ssl=1)*Photo by cottonbro studio on Pexels.com*

## **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](https://srinimf.com/2016/09/08/2-simple-sql-queries-on-self-join-and-inner-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](https://srinimf.com/2024/03/13/semi-left-distinct-match-antileft-non-match-joins-explained/) 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`.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F01%2F5-sql-queries-you-should-not-miss%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F10%2Fimage-1.png%3Ffit%3D1024%252C768%26ssl%3D1&description=5%20SQL%20Queries%3A%20You%20Should%20not%20Miss)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/10/01/5-sql-queries-you-should-not-miss/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%205%20SQL%20Queries%3A%20You%20Should%20not%20Miss&body=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F01%2F5-sql-queries-you-should-not-miss%2F&share=email)
-

### Like this:

Like Loading…