3 Essential SQL Tricks You Absolutely Need to Know

1. Common Table Expressions (CTEs).sql

// ❌ Without CTEs

SELECT customer_id, COUNT(order_id) AS order_count
FROM (
  SELECT customer_id, order_id
  FROM orders
  WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
) AS yearly_orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

// ✅ With CTEs

WITH yearly_orders AS (
  SELECT customer_id, order_id
  FROM orders
  WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
)
SELECT customer_id, COUNT(order_id) AS order_count
FROM yearly_orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

2. Partial Indexes.sql

// If you frequently need to find orders that are still pending, indexing every order is inefficient. 
//   Instead, you can create a partial index just for those pending orders.

CREATE INDEX idx_orders_pending
ON orders (order_date)
WHERE status = 'pending';

3. Conditional Aggregation.sql

// ❌ Without Conditional Aggregation

SELECT 
  (SELECT SUM(amount) FROM sales WHERE category = 'Books') * 100.0 / SUM(amount) AS books,
  (SELECT SUM(amount) FROM sales WHERE category = 'Electronics') * 100.0 / SUM(amount) AS electronics
FROM sales;

// ✅ With Conditional Aggregation

SELECT 
  (SUM(CASE WHEN category = 'Books' THEN amount ELSE 0 END) / SUM(amount)) * 100 AS books_percentage,
  (SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) / SUM(amount)) * 100 AS electronics_percentage
FROM sales;