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.
Here you can find SQL Interview Resources👇
https://t.me/DataSimplifier
Like this post if you need more 👍❤️
Share with credits: https://t.me/sqlspecialist
Hope it helps :)