Code library

Code worth reading.

Hand-picked snippets across TypeScript, Python, Rust, Go, and more — each one comes with a line-by-line breakdown so you can read it like the people who wrote it.

3495 snippets · 10 languages

▶ Start learning

Browse the SQL · Intermediate collection →

SQL · IntermediateSnippet

Correlated Subqueries

A correlated subquery references columns from the outer query. It is evaluated once for each row processed by the outer query to perform row-specific logic.

snippet.sql

sql

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

Queries & JoinsPerformance

SQL · IntermediateSnippet

Common Table Expressions (CTEs)

A CTE is a temporary result set that you can reference within another statement. It improves readability by breaking down complex queries into logical blocks.

snippet.sql

sql

WITH RegionSales AS (
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
)
SELECT *
FROM RegionSales
WHERE total > 1000;

Queries & JoinsSyntax

SQL · IntermediateSnippet

Filtering Aggregated Data with HAVING

The HAVING clause filters groups created by the GROUP BY clause. Unlike WHERE, which filters individual rows, HAVING filters based on aggregate function results.

snippet.sql

sql

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

Queries & JoinsSyntax

SQL · IntermediateSnippet

Window Functions with ROW_NUMBER

Window functions perform calculations across a set of table rows related to the current row. ROW_NUMBER assigns a unique sequential integer to rows within a specific partition.

snippet.sql

sql

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

FunctionsQueries & Joins

SQL · IntermediateSnippet

Atomic Transactions with COMMIT

Transactions ensure multiple operations are treated as a single unit. COMMIT saves all pending changes permanently to the database, ensuring data consistency.

snippet.sql

sql

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

TransactionsBest Practices

SQL · IntermediateSnippet

Handling Nulls with COALESCE

COALESCE takes a list of arguments and returns the first non-null value. It is essential for providing fallback values when dealing with optional columns in a database.

snippet.sql

sql

SELECT employee_name,
  COALESCE(personal_phone, work_phone, 'No Phone') AS contact_number
FROM employees;

FunctionsBest Practices

SQL · IntermediateSnippet

Set Intersection with INTERSECT

The INTERSECT operator returns only the distinct rows that are present in both the first and second query result sets. It is useful for finding common records across different datasets without using complex joins.

snippet.sql

sql

SELECT user_id FROM newsletter_subscribers
INTERSECT
SELECT user_id FROM premium_members;

Queries & JoinsSyntax

SQL · IntermediateSnippet

Self-Joins for Hierarchical Relationships

A self-join is a regular join where a table is joined with itself. This is primarily used to query hierarchical data, such as employees and their managers stored in the same table.

snippet.sql

sql

SELECT 
  e.name AS employee,
  m.name AS manager
FROM staff e
JOIN staff m ON e.manager_id = m.staff_id;

Queries & JoinsSyntax