📝 PostgreSQL Advanced Queries Cheat Sheet 🏆🐘

Master the art of querying with PostgreSQL! Elevate your database skills with these advanced queries.

Subqueries & Joins

Combine data from multiple tables with JOIN and INNER JOIN. Utilize subqueries for complex filtering and aggregation.

SELECT customers.name, orders.total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total_amount > (SELECT AVG(total_amount) FROM orders);

Window Functions

Calculate rankings, percentiles, and cumulative sums using OVER() clause. Analyze data within specific partitions.

SELECT name, score, RANK() OVER (PARTITION BY department ORDER BY score DESC) AS rank
FROM employees;

Common Table Expressions (CTE)

Create temporary result sets for complex queries. Improve code readability and reusability.

WITH top_customers AS (
   SELECT customer_id, SUM(total_amount) AS total_spent
   FROM orders
   GROUP BY customer_id
   ORDER BY total_spent DESC
   LIMIT 5
)
SELECT customers.name, top_customers.total_spent
FROM top_customers
JOIN customers ON top_customers.customer_id = customers.id;

JSONB Queries

Explore JSONB data with indexing, filtering, and aggregation. Extract values, navigate nested structures, and search for keys.

SELECT data->>'name' AS product_name, SUM((data->>'price')::numeric) AS total_price
FROM products
WHERE data->>'category' = 'Electronics'
GROUP BY product_name;

Full-Text Search

Perform powerful text-based searches using tsvector and tsquery. Rank results by relevance and handle linguistic variations.

SELECT title, ts_rank(document, query) AS rank
FROM articles, plainto_tsquery('magic & potion') AS query
WHERE document @@ query
ORDER BY rank DESC;

PostgreSQLAdvancedQueriesSQLMasteryWebDevelopmentBackendSkills