What types of complex queries have you implemented in PostgreSQL?

In PostgreSQL, I implemented the following types of complex queries:

• Complex JOINs with multiple tables, including INNER JOIN, LEFT JOIN, and CROSS JOIN for combining data from different sources.
• Recursive CTEs (WITH RECURSIVE) for working with hierarchical structures, such as traversing a category tree.
• Aggregations with GROUP BY and window functions for calculating moving averages, ranking, and summing within groups.
• Subqueries in SELECT, FROM, and WHERE for filtering and computing values based on other queries.
• Using JSONB and functions for working with JSON for storing and retrieving semi-structured data.

Example of a recursive query to get all descendants of a node in a hierarchy:

WITH RECURSIVE subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1 -- manager
    UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;