Code Library — Capypad · Read code line by line

SQL · ExpertSnippet

Window Functions for Deterministic Ranking

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not group rows into a single output row, preserving the original data detail.

snippet.sql

sql

1

2

3

4

5

6

SELECT 
  product_id, 
  category, 
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

SyntaxFunctions

SQL · ExpertSnippet

Transaction Isolation Levels and Integrity

Isolation levels define how transaction integrity is visible to other users and systems. SERIALIZABLE is the highest level, preventing dirty reads, non-repeatable reads, and phantom reads by simulating sequential execution.

snippet.sql

sql

1

2

3

4

5

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 102;
COMMIT;

TransactionsSecurity

SQL · ExpertSnippet

Recursive Common Table Expressions (CTEs)

Recursive CTEs are used to traverse hierarchical data structures like organizational charts or bill of materials. They consist of an anchor member and a recursive member joined by UNION ALL.

snippet.sql

sql

1

2

3

4

5

6

7

8

9

10

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

SyntaxQueries & Joins

SQL · ExpertSnippet

Conditional Aggregation for Data Pivoting

Conditional aggregation uses CASE statements inside aggregate functions to pivot data from rows into columns. This technique is highly efficient for generating reports that require multiple metrics from a single scan of the table.

snippet.sql

sql

1

2

3

4

5

6

SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) as revenue_shipped,
  SUM(CASE WHEN status = 'returned' THEN amount ELSE 0 END) as loss_returned
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

Queries & JoinsBest Practices

SQL · ExpertSnippet

Correlated Subqueries for Comparative Analysis

A correlated subquery refers to columns in the outer query. This example identifies employees who earn more than the average salary within their specific department, requiring the subquery to execute for every row evaluated by the outer statement.

snippet.sql

sql

1

2

3

4

5

6

7

SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Queries & JoinsPerformance

SQL · ExpertSnippet

Temporal Overlap Detection Logic

Identifying overlapping intervals is a common expert challenge. The logic (StartA < EndB AND StartB < EndA) is the mathematically optimal way to detect any intersection between two time periods, avoiding complex nested CASE statements.

snippet.sql

sql

1

2

3

4

5

6

SELECT a.id, b.id
FROM Bookings a
JOIN Bookings b ON a.room_id = b.room_id
  AND a.id < b.id
WHERE a.start_time < b.end_time
  AND b.start_time < a.end_time;

Queries & JoinsBest Practices

SQL · ExpertSnippet

SARGable Predicates for Index Optimization

SARGable (Search ARGumentable) queries allow the engine to perform index seeks rather than full scans. By comparing the column directly to a range instead of wrapping it in a function like EXTRACT, the database can utilize the B-Tree index structure efficiently.

snippet.sql

sql

1

2

3

4

5

SELECT order_id
FROM Orders
WHERE order_date >= '2023-01-01'
AND order_date < '2023-02-01';
-- AVOID: WHERE EXTRACT(MONTH FROM order_date) = 1

PerformanceBest Practices

SQL · ExpertSnippet

Data Integrity via WITH CHECK OPTION

The WITH CHECK OPTION clause on a view prevents users from inserting or updating rows that would not be visible through the view itself. This enforces business logic at the schema level, ensuring data consistency for updatable views.

snippet.sql

sql

1

2

3

4

5

CREATE VIEW Active_Staff AS
SELECT id, name, status
FROM Employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

SecuritySyntax