Advanced SQL Queries Coding Practice Challenges | CodeNivo

Question 1

Practical Example: Rank Employees by Salary

Accepted Answer

WITH employees(employee_id, employee_name, department, salary) AS (
    VALUES
        (1, 'Asha', 'Engineering', 90000),
        (2, 'Ravi', 'Engineering', 85000),
        (3, 'Meera', 'Engineering', 85000),
        (4, 'Kabir', 'Sales', 70000),
        (5, 'Neha', 'Sales', 75000)
)
SELECT
    employee_id,
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC, employee_id
    ) AS row_num,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_dense_rank
FROM employees
ORDER BY department, salary DESC, employee_id;

Answer

-- Practice query for Ranking with ROW_NUMBER, RANK, and DENSE_RANK

Question 2

Real-World Example: Employee Management Hierarchy

Accepted Answer

WITH RECURSIVE employee_tree AS (
    -- Anchor: begin with Asha
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth,
        ARRAY[employee_id] AS path
    FROM employees
    WHERE employee_id = 1

UNION ALL

-- Recursive member: find direct reports
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        t.depth + 1,
        t.path || e.employee_id
    FROM employees AS e
    JOIN employee_tree AS t
        ON e.manager_id = t.employee_id
    WHERE t.depth < 10
      AND NOT (e.employee_id = ANY(t.path))
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    depth,
    path
FROM employee_tree
ORDER BY path;

Answer

-- Practice query for Recursive CTEs

Question 3

Cycle Detection in Hierarchical Data

Accepted Answer

WITH RECURSIVE walk(node, path) AS (
    SELECT
        1,
        ARRAY[1]

UNION ALL

SELECT
        edge.child_id,
        walk.path || edge.child_id
    FROM walk
    JOIN (
        VALUES
            (1, 2),
            (2, 3),
            (3, 1)
    ) AS edge(parent_id, child_id)
        ON edge.parent_id = walk.node
    WHERE NOT (edge.child_id = ANY(walk.path))
)
SELECT node, path
FROM walk
ORDER BY cardinality(path);

Answer

-- Practice query for Recursive CTEs

Question 4

5. Use CTEs with Filtering and Aggregation

Accepted Answer

WITH completed_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'completed'
),
customer_spending AS (
    SELECT
        customer_id,
        SUM(total_amount) AS completed_spending
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id, completed_spending
FROM customer_spending
WHERE completed_spending >= 1000
ORDER BY completed_spending DESC;

Answer

-- Practice query for Common Table Expressions (CTEs)

Question 5

PARTITION BY: Calculate Within Groups

Accepted Answer

WITH sales(sale_id, employee, amount) AS (
    VALUES
        (1, 'Asha', 100),
        (2, 'Ravi', 200),
        (3, 'Asha', 150),
        (4, 'Ravi', 300)
)
SELECT
    sale_id,
    employee,
    amount,
    SUM(amount) OVER (
        PARTITION BY employee
    ) AS employee_total
FROM sales
ORDER BY sale_id;

Answer

-- Practice query for Window Functions

Question 6

Top N With Ties: RANK and DENSE_RANK

Accepted Answer

WITH employees(employee_name, department, salary) AS (
    VALUES
        ('Asha', 'Engineering', 100000),
        ('Ravi', 'Engineering', 90000),
        ('Meera', 'Engineering', 90000),
        ('Kabir', 'Engineering', 80000)
),
ranked AS (
    SELECT
        employee_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_rank,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_dense_rank
    FROM employees
)
SELECT
    employee_name,
    salary,
    salary_rank,
    salary_dense_rank
FROM ranked
WHERE salary_rank <= 2
ORDER BY salary DESC, employee_name;

Answer

-- Practice query for Ranking with ROW_NUMBER, RANK, and DENSE_RANK

Question 7

Running Totals and Cumulative Sums

Accepted Answer

WITH transactions(transaction_id, amount) AS (
    VALUES
        (1, 100),
        (2, 50),
        (3, -20),
        (4, 80)
)
SELECT
    transaction_id,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM transactions
ORDER BY transaction_id;

Answer

-- Practice query for Window Functions

Question 8

Nested Categories and Hierarchy Paths

Accepted Answer

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_id,
        0 AS depth,
        ARRAY[category_id] AS path
    FROM categories
    WHERE category_id = 1

UNION ALL

SELECT
        c.category_id,
        c.category_name,
        c.parent_id,
        t.depth + 1,
        t.path || c.category_id
    FROM categories AS c
    JOIN category_tree AS t
        ON c.parent_id = t.category_id
    WHERE t.depth < 20
      AND NOT (c.category_id = ANY(t.path))
)
SELECT
    category_id,
    category_name,
    parent_id,
    depth,
    path
FROM category_tree
ORDER BY path;

Answer

-- Practice query for Recursive CTEs

Question 9

Ranking: ROW_NUMBER, RANK, and DENSE_RANK

Accepted Answer

WITH scores(player, score) AS (
    VALUES
        ('Asha', 100),
        ('Ravi', 90),
        ('Meera', 90),
        ('Kabir', 80)
)
SELECT
    player,
    score,
    ROW_NUMBER() OVER (
        ORDER BY score DESC, player
    ) AS row_num,
    RANK() OVER (
        ORDER BY score DESC
    ) AS rank_num,
    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS dense_rank_num
FROM scores
ORDER BY score DESC, player;

Answer

-- Practice query for Window Functions

Question 10

6. CTEs with Window Functions

Accepted Answer

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS row_num
    FROM orders
)
SELECT order_id, customer_id, order_date
FROM ranked_orders
WHERE row_num = 1
ORDER BY customer_id;

Answer

-- Practice query for Common Table Expressions (CTEs)

Question 11

11. Practice Exercises

Accepted Answer

WITH qualifying_products AS (
    SELECT product_id, product_name, price
    FROM products
    WHERE price > 500
)
SELECT product_id, product_name, price
FROM qualifying_products
ORDER BY price DESC, product_id;

Answer

-- Practice query for Common Table Expressions (CTEs)

Question 12

LAG and LEAD: Compare Adjacent Rows

Accepted Answer

WITH monthly_sales(month_number, amount) AS (
    VALUES
        (1, 100),
        (2, 130),
        (3, 120),
        (4, 180)
),
compared AS (
    SELECT
        month_number,
        amount,
        LAG(amount) OVER (
            ORDER BY month_number
        ) AS previous_amount
    FROM monthly_sales
)
SELECT
    month_number,
    amount,
    previous_amount,
    amount - previous_amount AS change
FROM compared
ORDER BY month_number;

Answer

-- Practice query for Window Functions

node	path
1	{1}
2	{1,2}
3	{1,2,3}

employee_id	employee_name	department	salary	row_num	salary_rank	salary_dense_rank
1	Asha	Engineering	90000	1	1	1
2	Ravi	Engineering	85000	2	2	2
3	Meera	Engineering	85000	3	2	2
5	Neha	Sales	75000	1	1	1
4	Kabir	Sales	70000	2	2	2

employee_id	employee_name	manager_id	depth	path
1	Asha	NULL	0	{1}
2	Ravi	1	1	{1,2}
3	Meera	1	1	{1,3}
4	Arjun	2	2	{1,2,4}
5	Neha	2	2	{1,2,5}
6	Kabir	3	2	{1,3,6}

Advanced SQL Queries Problems