SELECT DISTINCT col1, col2, expr AS alias
FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE col1 > 100 AND col2 IN ('x', 'y')
GROUP BY col1, col2
HAVING COUNT(*) > 5
ORDER BY col1 DESC, col2 ASC NULLS LAST
LIMIT 50 OFFSET 100;

-- INNER
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- LEFT (keep all customers, even those with no orders)
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- ANTI (customers who never ordered)
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- SEMI (customers who ordered at least once, no dupes)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

fn() OVER (
  PARTITION BY <reset-cols>
  ORDER BY <sequence-cols>
  ROWS BETWEEN <start> AND <end>
)

-- Top 3 per group
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY group_id ORDER BY score DESC) r
  FROM t
) x WHERE r <= 3;

-- Period-over-period
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_delta
FROM monthly;

-- Running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM tx;

-- 7-day moving average
SELECT date, AVG(value) OVER (
  ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) FROM daily;

-- Dedupe to latest
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) rn
  FROM t
) x WHERE rn = 1;

WITH active_users AS (
  SELECT id FROM users WHERE last_seen_at > NOW() - INTERVAL '30 days'
),
recent_orders AS (
  SELECT * FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.id, COUNT(o.id) AS order_count
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
GROUP BY u.id;

WITH RECURSIVE chain AS (
  SELECT employee_id, manager_id, 1 AS depth
  FROM employees
  WHERE employee_id = :start_id
  UNION ALL
  SELECT e.employee_id, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.employee_id = c.manager_id
)
SELECT * FROM chain;

-- INSERT (single + multi-row)
INSERT INTO users (email, name) VALUES ('a@b', 'A'), ('c@d', 'C');

-- INSERT ... SELECT
INSERT INTO archive (id, payload)
SELECT id, payload FROM events WHERE created_at < '2025-01-01';

-- UPSERT (PostgreSQL)
INSERT INTO daily (date, count) VALUES (CURRENT_DATE, 1)
ON CONFLICT (date) DO UPDATE SET count = daily.count + 1;

-- UPDATE with FROM (Postgres) / JOIN (MySQL/SQL Server)
UPDATE orders o
SET status = 'fulfilled'
FROM shipments s
WHERE s.order_id = o.id AND s.shipped_at IS NOT NULL;

-- DELETE
DELETE FROM sessions WHERE expires_at < NOW();

-- Standard composite index — leftmost prefix matters
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date DESC);

-- Partial index — only active rows
CREATE INDEX idx_users_active ON users (last_seen_at)
WHERE deleted_at IS NULL;

-- Covering index (PostgreSQL 11+)
CREATE INDEX idx_orders_cover
ON orders (customer_id) INCLUDE (status, total);

-- Inspect the plan
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... read + write ...
COMMIT;  -- or ROLLBACK; on retryable serialization error