Florian Zeba

Posted on Apr 29 • Originally published at fzeba.com

#34 50 Advanced SQL Queries Every Developer Should Know

#sql #database #dataengineering

SQL is a powerful language for managing and querying relational databases. While basic queries like SELECT, INSERT, UPDATE, and DELETE are essential, mastering advanced SQL techniques can significantly enhance your ability to analyze data, optimize performance, and solve complex problems.

In this article, we’ll explore 50 advanced SQL queries that cover window functions, recursive CTEs, pivoting, performance optimization, and more.

1. Window Functions (Analytical Queries)

Window functions allow computations across a set of table rows related to the current row.

1.1. ROW_NUMBER() – Assign a Unique Row Number

SELECT
    employee_id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.2. RANK() – Rank with Gaps for Ties

SELECT
    employee_id,
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.3. DENSE_RANK() – Rank Without Gaps

SELECT
    employee_id,
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.4. NTILE() – Divide Rows into Buckets

SELECT
    employee_id,
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

1.5. LEAD() – Access Next Row’s Value

SELECT
    employee_id,
    name,
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

1.6. LAG() – Access Previous Row’s Value

SELECT
    employee_id,
    name,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;

1.7. FIRST_VALUE() – Get First Value in a Window

SELECT
    employee_id,
    name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_in_dept
FROM employees;

1.8. LAST_VALUE() – Get Last Value in a Window

SELECT
    employee_id,
    name,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_in_dept
FROM employees;

1.9. Running Total with SUM() OVER

SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales;

1.10. Moving Average

SELECT
    date,
    revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

2. Common Table Expressions (CTEs) and Recursive Queries

CTEs improve readability and allow recursive operations.

2.1. Basic CTE

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners;

2.2. Recursive CTE (Hierarchical Data)

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: Employees with managers
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

2.3. Multiple CTEs in a Single Query

WITH
    dept_stats AS (
        SELECT department, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department
    ),
    high_paying_depts AS (
        SELECT department
        FROM dept_stats
        WHERE avg_salary > 80000
    )
SELECT e.*
FROM employees e
JOIN high_paying_depts hpd ON e.department = hpd.department;

3. Pivoting and Unpivoting Data

3.1. Pivot with CASE

SELECT
    product_id,
    SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS north_sales,
    SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS south_sales,
    SUM(CASE WHEN region = 'East' THEN sales ELSE 0 END) AS east_sales,
    SUM(CASE WHEN region = 'West' THEN sales ELSE 0 END) AS west_sales
FROM sales
GROUP BY product_id;

3.2. Pivot with PIVOT (SQL Server, Oracle)

SELECT *
FROM (
    SELECT product_id, region, sales
    FROM sales
) AS src
PIVOT (
    SUM(sales) FOR region IN ([North], [South], [East], [West])
) AS pvt;

3.3. Unpivot Data

SELECT product_id, region, sales
FROM (
    SELECT product_id, north_sales, south_sales, east_sales, west_sales
    FROM pivoted_sales
) AS src
UNPIVOT (
    sales FOR region IN (north_sales, south_sales, east_sales, west_sales)
) AS unpvt;

4. Advanced Joins and Subqueries

4.1. Self-Join (Find Employees with Same Manager)

SELECT
    e1.name AS employee1,
    e2.name AS employee2,
    e1.manager_id
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id;

4.2. Lateral Join (PostgreSQL)

SELECT
    d.department_name,
    e.name,
    e.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary
    FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e;

4.3. Correlated Subquery (Find Employees Earning Above Avg in Dept)

SELECT
    e1.name,
    e1.salary,
    e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

5. Performance Optimization

5.1. Index Hinting (Force Index Usage)

SELECT * FROM employees WITH (INDEX(idx_salary)) WHERE salary > 50000;

5.2. Query Plan Analysis (EXPLAIN)

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

5.3. Materialized Views (Precompute Expensive Queries)

CREATE MATERIALIZED VIEW mv_high_earners AS
SELECT * FROM employees WHERE salary > 100000;

REFRESH MATERIALIZED VIEW mv_high_earners;

6. Advanced Aggregations

6.1. ROLLUP (Hierarchical Grouping)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, job_title);

6.2. CUBE (All Possible Groupings)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department, job_title);

6.3. GROUPING SETS (Custom Groupings)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (department, job_title),
    (department),
    (job_title),
    ()
);

7. JSON and XML Handling

7.1. Extract JSON Fields

SELECT
    id,
    json_data->>'name' AS name,
    json_data->>'age' AS age
FROM users;

7.2. Query Nested JSON Arrays

SELECT
    id,
    json_array_elements(json_data->'skills') AS skill
FROM users;

7.3. XML Parsing

SELECT
    id,
    xpath('//name/text()', xml_data) AS name,
    xpath('//age/text()', xml_data) AS age
FROM users;

8. Dynamic SQL

8.1. Execute Dynamic Query (SQL Injection Safe)

EXECUTE format('SELECT * FROM %I WHERE salary > %L', 'employees', 50000);

8.2. Generate and Run SQL in a Loop

DO $$
DECLARE
    query TEXT;
BEGIN
    FOR i IN 1..10 LOOP
        query := format('INSERT INTO logs (message) VALUES (%L)', 'Log ' || i);
        EXECUTE query;
    END LOOP;
END $$;

9. Advanced Joins and Set Operations

9.1. FULL OUTER JOIN (Find All Matches and Non-Matches)

SELECT
    e.employee_id,
    e.name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

9.2. NATURAL JOIN (Join on Columns with Same Name)

SELECT * FROM employees NATURAL JOIN departments;

9.3. INTERSECT (Find Common Records Between Two Queries)

SELECT employee_id FROM full_time_employees
INTERSECT
SELECT employee_id FROM high_performers;

9.4. EXCEPT (Find Records in First Query but Not Second)

SELECT employee_id FROM all_employees
EXCEPT
SELECT employee_id FROM terminated_employees;

9.5. UNION ALL (Combine Results with Duplicates)

SELECT name, salary FROM current_employees
UNION ALL
SELECT name, salary FROM former_employees;

10. Advanced Subqueries

10.1. EXISTS (Check for Related Records)

SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM sales s
    WHERE s.employee_id = e.employee_id AND s.amount > 10000
);

10.2. NOT EXISTS (Find Records Without Related Data)

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
);

10.3. IN with Subquery (Filter Based on Another Query)

SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

10.4. ALL (Compare Against All Values in Subquery)

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Intern'
);

10.5. ANY/SOME (Compare Against Any Value in Subquery)

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'Management'
);

11. Advanced Data Modification

11.1. UPSERT (INSERT or UPDATE on Conflict)

INSERT INTO employees (id, name, salary)
VALUES (101, 'John Doe', 75000)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, salary = EXCLUDED.salary;

11.2. MERGE (Conditional INSERT/UPDATE/DELETE)

MERGE INTO employees e
USING updated_employees ue
ON e.id = ue.id
WHEN MATCHED THEN
    UPDATE SET e.name = ue.name, e.salary = ue.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (ue.id, ue.name, ue.salary);

11.3. DELETE with JOIN

DELETE FROM employees
USING departments
WHERE employees.department_id = departments.department_id
AND departments.location = 'Remote';

11.4. UPDATE from Another Table

UPDATE employees e
SET salary = e.salary * 1.1
FROM departments d
WHERE e.department_id = d.department_id
AND d.budget > 1000000;

12. Database Administration & Meta-Queries

12.1. List All Tables in a Database

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

12.2. Find Column Names in a Table

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'employees';

12.3. Check Table Size (PostgreSQL)

SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(table_name)) AS size
FROM information_schema.tables
WHERE table_schema = 'public';

12.4. Find Long-Running Queries

SELECT
    pid,
    query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

12.5. Kill a Running Query

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%long_running_query%';

13. Advanced Date & Time Operations

13.1. Generate Date Series

SELECT generate_series(
    '2023-01-01'::date,
    '2023-12-31'::date,
    '1 day'::interval
) AS date;

13.2. Calculate Business Days Between Dates

SELECT
    date1,
    date2,
    COUNT(*) FILTER (WHERE EXTRACT(DOW FROM day) BETWEEN 1 AND 5) AS business_days
FROM (
    SELECT
        '2023-01-01'::date AS date1,
        '2023-01-31'::date AS date2,
        generate_series(
            '2023-01-01'::date,
            '2023-01-31'::date,
            '1 day'::interval
        ) AS day
) t;

13.3. Find Last Day of Month

SELECT
    date_trunc('month', current_date) + INTERVAL '1 month - 1 day' AS last_day_of_month;

14. Advanced String Manipulation

14.1. Regex Extract

SELECT
    regexp_matches(email, '([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+)\.([A-Za-z]{2,})')
FROM users;

14.2. Split String into Rows

SELECT
    id,
    unnest(string_to_array(tags, ',')) AS tag
FROM products;

14.3. Concatenate Rows into String

SELECT
    department_id,
    string_agg(name, ', ') AS employees
FROM employees
GROUP BY department_id;

15. Advanced Security & Permissions

15.1. Grant Column-Level Permissions

GRANT SELECT (name, email) ON employees TO analyst_role;

15.2. Create a Read-Only User

CREATE USER readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Conclusion

With these 20 additional advanced SQL queries, we now have a complete list of 50 essential SQL techniques covering:

✅ Window Functions

✅ CTEs & Recursive Queries

✅ Pivoting & Unpivoting

✅ Advanced Joins & Subqueries

✅ Performance Optimization

✅ JSON/XML Handling

✅ Dynamic SQL

✅ Database Administration