SQL Snippets

-- Schema
-- users(id, name, email)
-- orders(id, user_id, total, created_at)

-- INNER JOIN: only rows that match in both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Returns: users who have at least one order

-- LEFT JOIN: all users, NULLs for users with no orders
SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Equivalent with NOT EXISTS (often faster on large tables)
SELECT name FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- FULL OUTER JOIN: all rows from both sides, NULLs where no match
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Self-join: find employees and their managers
-- employees(id, name, manager_id)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Basic aggregation
SELECT
    department,
    COUNT(*)          AS employee_count,
    AVG(salary)       AS avg_salary,
    MAX(salary)       AS max_salary,
    MIN(salary)       AS min_salary,
    SUM(salary)       AS total_payroll
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- HAVING: filter after grouping (WHERE filters before)
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;   -- only departments with avg > 80k

-- GROUP BY multiple columns
SELECT department, job_title, COUNT(*) AS headcount
FROM employees
GROUP BY department, job_title
ORDER BY department, headcount DESC;

-- ROLLUP: subtotals and grand total
SELECT
    department,
    job_title,
    COUNT(*) AS count
FROM employees
GROUP BY ROLLUP(department, job_title);
-- Rows: each (dept, title) pair + dept subtotal + grand total

-- COUNT(DISTINCT): unique values
SELECT
    COUNT(*)              AS total_orders,
    COUNT(DISTINCT user_id) AS unique_customers,
    AVG(total)            AS avg_order_value
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Window functions compute across rows related to the current row
-- without collapsing them into groups (unlike GROUP BY)

-- ROW_NUMBER: unique sequential number within partition
-- RANK: same rank for ties, gaps after (1,2,2,4)
-- DENSE_RANK: same rank for ties, no gaps (1,2,2,3)
SELECT
    name,
    department,
    salary,
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Top-N per group: highest-paid per department
SELECT name, department, salary
FROM (
    SELECT name, department, salary,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 3;

-- Running total
SELECT
    created_at::date AS day,
    SUM(total) AS daily_revenue,
    SUM(SUM(total)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
GROUP BY created_at::date;

-- LAG/LEAD: compare to previous/next row
SELECT
    date,
    revenue,
    LAG(revenue)  OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS change,
    LEAD(revenue) OVER (ORDER BY date) AS next_revenue
FROM daily_sales;

-- CTE: named subquery — improves readability, reusable in query
WITH high_value_orders AS (
    SELECT user_id, SUM(total) AS lifetime_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    HAVING SUM(total) > 1000
),
user_details AS (
    SELECT u.id, u.name, u.email, h.lifetime_value
    FROM users u
    INNER JOIN high_value_orders h ON u.id = h.user_id
)
SELECT name, email, lifetime_value
FROM user_details
ORDER BY lifetime_value DESC;

-- Multiple CTEs chained
WITH
    monthly_sales AS (
        SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
        FROM orders GROUP BY 1
    ),
    ranked AS (
        SELECT month, revenue,
               RANK() OVER (ORDER BY revenue DESC) AS rank
        FROM monthly_sales
    )
SELECT month, revenue
FROM ranked
WHERE rank <= 5;  -- top 5 months

-- Recursive CTE: traverse hierarchies (org charts, categories)
WITH RECURSIVE org_chart AS (
    -- Base case: top-level (no manager)
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- Recursive: employees of previous level
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', level) || name AS hierarchy, level
FROM org_chart
ORDER BY level, name;

-- Scalar subquery: returns a single value
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Correlated subquery: references outer query — runs once per row
-- Employees earning above their department average
SELECT name, department, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department  -- correlated: references e.department
);

-- EXISTS: check for existence (usually faster than IN for large sets)
SELECT DISTINCT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.total > 500
);

-- IN with subquery
SELECT name FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE total > 500
);

-- Subquery in FROM clause (derived table)
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, ROUND(AVG(salary)::numeric, 2) AS avg_sal
    FROM employees
    GROUP BY department
) dept_averages
WHERE avg_sal > 75000;

-- NULL in SQL: unknown — NOT equal to anything, even itself
-- NULL = NULL evaluates to NULL (not TRUE)
-- Always use IS NULL / IS NOT NULL to check for null

-- COALESCE: return first non-NULL argument
SELECT
    name,
    COALESCE(phone, mobile, email, 'no contact') AS contact_info,
    COALESCE(discount, 0) AS discount  -- replace NULL with 0
FROM customers;

-- NULLIF: return NULL if two values are equal — avoids division by zero
SELECT
    revenue,
    costs,
    revenue / NULLIF(costs, 0) AS ratio  -- NULL if costs = 0, not error
FROM financials;

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE shipped_at IS NULL;    -- unshipped orders
SELECT * FROM orders WHERE shipped_at IS NOT NULL; -- shipped orders

-- NULL in aggregations: COUNT(*) counts all rows; COUNT(col) skips NULLs
SELECT
    COUNT(*)        AS total_rows,
    COUNT(email)    AS rows_with_email,    -- NULLs excluded
    COUNT(DISTINCT email) AS unique_emails -- NULLs excluded and deduplicated
FROM users;

-- NULL in ORDER BY: NULLS FIRST / NULLS LAST (PostgreSQL)
SELECT name, last_login
FROM users
ORDER BY last_login DESC NULLS LAST;  -- active users first, never-logged last

-- Propagation: any arithmetic with NULL produces NULL
SELECT 5 + NULL;   -- NULL
SELECT NULL = NULL; -- NULL (not TRUE — use IS NULL)

-- INSERT single row
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- INSERT multiple rows
INSERT INTO products (name, price, stock)
VALUES
    ('Widget A', 9.99,  100),
    ('Widget B', 19.99, 50),
    ('Widget C', 4.99,  200);

-- INSERT ... SELECT (copy rows)
INSERT INTO archived_orders (SELECT * FROM orders WHERE created_at < '2025-01-01');

-- UPSERT (PostgreSQL): insert or update on conflict
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', true)
ON CONFLICT (user_id)
DO UPDATE SET
    theme         = EXCLUDED.theme,
    notifications = EXCLUDED.notifications,
    updated_at    = NOW();

-- UPSERT: insert only if missing (do nothing on conflict)
INSERT INTO tags (name) VALUES ('golang')
ON CONFLICT (name) DO NOTHING;

-- UPDATE with JOIN (PostgreSQL)
UPDATE orders o
SET status = 'premium'
FROM users u
WHERE o.user_id = u.id AND u.tier = 'gold';

-- DELETE with subquery
DELETE FROM sessions
WHERE user_id IN (
    SELECT id FROM users WHERE last_login < NOW() - INTERVAL '1 year'
);

-- Returning modified rows
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')
RETURNING id, created_at;

-- B-tree index: default — fast for =, <, >, BETWEEN, LIKE 'prefix%'
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Composite index: column order matters
-- Best for: WHERE email = ? AND created_at > ?
-- Also good for: WHERE email = ?  (leading column)
-- NOT efficient for: WHERE created_at > ?  (non-leading column alone)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index: only index rows matching a condition (smaller, faster)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Covering index: include extra columns to avoid table lookup
CREATE INDEX idx_orders_covering
ON orders(user_id)
INCLUDE (total, status);  -- PostgreSQL 11+

-- GiST / GIN indexes for full-text search (PostgreSQL)
CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('english', name || ' ' || description));

-- Check which indexes exist
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- EXPLAIN ANALYZE: see if index is being used
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';

-- Drop index
DROP INDEX IF EXISTS idx_users_email;

-- Rebuild index (PostgreSQL): non-blocking
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Current date and time
SELECT NOW();                          -- timestamp with timezone
SELECT CURRENT_DATE;                   -- date only
SELECT CURRENT_TIMESTAMP;             -- standard SQL equivalent of NOW()

-- Date arithmetic (PostgreSQL)
SELECT NOW() - INTERVAL '30 days';    -- 30 days ago
SELECT NOW() + INTERVAL '1 year';     -- 1 year from now
SELECT AGE(NOW(), created_at) FROM users;  -- difference as interval

-- Truncate to period
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1 ORDER BY 1;

-- Extract parts
SELECT
    EXTRACT(YEAR  FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DOW   FROM created_at) AS day_of_week,  -- 0=Sun
    EXTRACT(EPOCH FROM created_at) AS unix_timestamp
FROM orders;

-- Time zone conversion (PostgreSQL)
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' AS ist_time
FROM orders;

-- Format date as string
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM orders;

-- Last 7 days
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

-- This calendar month
SELECT * FROM orders
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- Schema: products(id, name, metadata JSONB)
-- metadata example: {"color":"red","tags":["sale","featured"],"specs":{"weight":1.5}}

-- Access JSON fields
SELECT
    name,
    metadata->>'color' AS color,             -- text value
    metadata->'specs'->>'weight' AS weight,  -- nested text value
    (metadata->>'price')::numeric AS price   -- cast to numeric
FROM products;

-- Filter by JSON field
SELECT name FROM products
WHERE metadata->>'color' = 'red';

SELECT name FROM products
WHERE (metadata->>'price')::numeric > 50;

-- JSON array operations
SELECT name
FROM products
WHERE metadata->'tags' ? 'sale';  -- contains element

SELECT name
FROM products
WHERE metadata->'tags' @> '["sale", "featured"]';  -- contains all

-- Expand JSON array to rows
SELECT name, jsonb_array_elements_text(metadata->'tags') AS tag
FROM products;

-- Update a JSON field (JSONB || merges)
UPDATE products
SET metadata = metadata || '{"featured": true}'::jsonb
WHERE id = 42;

-- Remove a JSON key
UPDATE products
SET metadata = metadata - 'deprecated_field'
WHERE id = 42;

-- Aggregate rows into JSON
SELECT
    department,
    jsonb_agg(jsonb_build_object('name', name, 'salary', salary)) AS employees
FROM employees
GROUP BY department;

-- Case manipulation
SELECT UPPER(name), LOWER(email), INITCAP(name) FROM users;

-- Trimming and padding
SELECT TRIM(name), LTRIM(name), RTRIM(name) FROM users;
SELECT LPAD(CAST(id AS TEXT), 6, '0') AS padded_id FROM orders;  -- "000042"

-- Substring and position
SELECT
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS username,
    SPLIT_PART(email, '@', 2) AS domain  -- PostgreSQL
FROM users;

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;  -- NULL-safe
SELECT CONCAT_WS(', ', city, state, country) AS address FROM locations;  -- with separator

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';       -- ends with
SELECT * FROM users WHERE name LIKE 'A%';                  -- starts with
SELECT * FROM users WHERE phone LIKE '___-____';           -- exact pattern (_ = any char)
SELECT * FROM users WHERE name ILIKE '%alice%';            -- case-insensitive (PostgreSQL)
SELECT * FROM users WHERE name ~ '^[A-Z]';                 -- regex (PostgreSQL)

-- String aggregation
SELECT department, STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department;

-- String length and replace
SELECT
    LENGTH(description) AS char_count,
    REPLACE(description, 'old', 'new') AS updated
FROM products;

-- Transaction: group statements into an atomic unit
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    -- If either fails, ROLLBACK returns both to original state
COMMIT;

-- Explicit rollback on error
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (42, 99.99);
    -- Something fails...
ROLLBACK;  -- undo everything since BEGIN

-- Savepoint: partial rollback within a transaction
BEGIN;
    INSERT INTO users (name) VALUES ('Alice');     -- step 1
    SAVEPOINT after_alice;
    INSERT INTO users (name) VALUES ('Duplicate'); -- step 2
    -- step 2 failed — rollback only to savepoint
    ROLLBACK TO SAVEPOINT after_alice;
    INSERT INTO users (name) VALUES ('Bob');        -- step 3 (replaces step 2)
COMMIT;  -- commits step 1 and step 3

-- Isolation levels (from least to most strict)
-- READ COMMITTED (default in PostgreSQL/MySQL): sees only committed data
-- REPEATABLE READ: same rows for duration of transaction
-- SERIALIZABLE: fully isolated — as if transactions ran sequentially

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT balance FROM accounts WHERE id = 1;
    -- Guaranteed to see consistent state for entire transaction
COMMIT;

CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,           -- auto-increment
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    age        SMALLINT CHECK (age >= 0 AND age < 150),
    status     VARCHAR(20) NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active', 'suspended', 'deleted')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    total      NUMERIC(12, 2) NOT NULL CHECK (total >= 0),
    status     TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ON DELETE options:
-- RESTRICT: prevent deletion of referenced row
-- CASCADE: delete child rows automatically
-- SET NULL: set foreign key to NULL (column must allow NULL)
-- SET DEFAULT: set to default value

-- Composite unique constraint
CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id),
    role    TEXT NOT NULL,
    PRIMARY KEY (user_id, role)  -- composite PK also acts as unique index
);

-- Add constraint to existing table
ALTER TABLE users ADD CONSTRAINT chk_email_format
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Deferred constraint (checked at COMMIT not statement time)
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    DEFERRABLE INITIALLY DEFERRED;