SQL: Production Code Snippets

Advanced SQL queries: window functions, transactions, triggers, CTEs.

Python JavaScript TypeScript Java C# C++ Go PHP Rust SQL

On this page:

46. SQL: Advanced Window Functions 47. SQL: Transaction with Row Locking 48. SQL: Automatic updated_at Trigger 49. SQL: Optimizing Partial Index 50. SQL: Recursive Query (CTE)

46. SQL: Advanced Window Functions

SQL

Trend analysis and rankings (ROW_NUMBER) over time.

SELECT 
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_in_dep
FROM employees;

When to use

Useful for rankings, year-over-year comparisons, or trend analysis without writing subqueries.

Common pitfalls

ROW_NUMBER() assigns a unique number even for ties — if you want a shared rank for equal values, use RANK() or DENSE_RANK() instead.

47. SQL: Transaction with Row Locking

SQL

Safe modification of inventory states or financial balances.

BEGIN TRANSACTION;

SELECT stock_quantity 
FROM inventory 
WHERE product_id = 142 
FOR UPDATE;

UPDATE inventory 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 142;

COMMIT;

When to use

Essential for operations on inventory states or financial balances, where two concurrent transactions must not overwrite each other.

Common pitfalls

FOR UPDATE locks the row until the end of the transaction — an overly long transaction can make other queries wait a long time for access to that same row.

48. SQL: Automatic updated_at Trigger

SQL

A function that refreshes the modification field on every UPDATE.

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP;

When to use

A convenient way to ensure the updated_at field always reflects the record's last actual change.

Common pitfalls

Trigger syntax differs between database engines (MySQL, PostgreSQL, SQL Server) — this example needs adapting to your specific SQL dialect.

49. SQL: Optimizing Partial Index

SQL

Indexing only active records to save memory.

CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE status = 'active';

When to use

Useful when queries regularly filter on one value (e.g. status = 'active') and the rest of the records are rarely queried.

Common pitfalls

A partial index only speeds up queries that exactly match the WHERE condition in its definition — other queries won't use it.

50. SQL: Recursive Query (CTE)

SQL

Traversing hierarchical relationships (e.g. an organization chart).

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

When to use

Use it for hierarchical structures — org charts, nested categories, threaded comments.

Common pitfalls

Missing a termination condition (or a broken reference) can lead to an infinite loop — most databases have a default recursion depth limit as a safeguard.