1. Window Functions for Advanced Analytics:
Calculate running totals, ranks, and moving averages without subqueries.
SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data;
2. Conditional Aggregation with CASE WHEN:
Segment data within a single query, saving time and creating versatile summaries.
SELECT COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed_orders FROM orders;
3. CTEs for Modular Queries:
Make complex queries more readable and reusable with CTEs.
WITH filtered_sales AS (SELECT * FROM sales_data WHERE region = 'North')
SELECT product, SUM(sales) FROM filtered_sales GROUP BY product;
4. Optimize with EXISTS vs. IN:
Use EXISTS for better performance in larger datasets.
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
5. Self Joins for Row Comparisons:
Compare rows within the same table, helpful for changes over time.
SELECT a.date, (a.sales - b.sales) AS sales_diff FROM sales_data a JOIN sales_data b ON a.date = b.date + INTERVAL '1' MONTH;
6. UNION vs. UNION ALL:
Combine results from multiple queries; UNION ALL is faster as it doesn’t remove duplicates.
7. Handle NULLs with COALESCE:
Replace NULLs with defaults to avoid calculation issues.
SELECT product, COALESCE(sales, 0) AS sales FROM product_sales;
8. Pivot Data with CASE Statements:
Transform rows into columns for clearer insights.
9. Extract Data with STRING Functions:
Useful for semi-structured data; extract domains, product codes, etc.
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain FROM users;10. Indexing for Faster Queries:
Indexes speed up data retrieval, especially on frequently queried columns.
Mastering these SQL tricks will optimize your queries, simplify logic, and enable complex analyses.
Hope it helps :)