SQL window functions are one of the most powerful tools for analytical queries because they calculate values across related rows without collapsing the original result set. Unlike GROUP BY, which reduces many rows into a smaller number of groups, a window function returns one value for every input row while adding an analytical column. The OVER clause defines the window through three optional components: PARTITION BY divides rows into independent groups, ORDER BY establishes their sequence, and a frame determines exactly which rows are included in the calculation. This makes window functions suitable for customer-level totals, percentage-of-total calculations, rankings, and running measures while preserving row-level detail.
Ranking functions provide several ways to assign positions while handling ties differently. ROW_NUMBER() assigns a unique sequential number and is useful for deduplication or selecting one record per group. RANK() gives tied rows the same position and leaves gaps after ties, making it appropriate for competition-style standings. DENSE_RANK() also assigns equal ranks to ties but does not leave gaps, which makes it useful for top-N tiers. NTILE() distributes rows into approximately equal buckets for quartiles, deciles, or cohorts. A critical detail is determinism: ROW_NUMBER() over a non-unique ordering can produce different assignments between executions, so a unique tiebreaker such as a primary key should be added. For Top-N-per-group queries, ranking is calculated inside a CTE or subquery and filtered in the outer query because window functions are evaluated after WHERE.
Offset functions allow SQL to compare the current row with neighbouring rows without constructing self-joins. LAG() retrieves a value from an earlier row, while LEAD() looks ahead, making them ideal for month-over-month changes, period comparisons, and time-to-next-event calculations. FIRST_VALUE() and LAST_VALUE() retrieve values from the edges of a window, but LAST_VALUE() requires particular care because the default frame can end at the current row, causing it to return the current value rather than the final value of the partition. Aggregate functions such as SUM, AVG, COUNT, MIN, and MAX can also operate as windows. With ORDER BY, they can produce cumulative calculations such as running totals; without ordering, they can repeat a partition-level summary on every row. An explicit ROWS frame can create fixed-width calculations such as seven-row moving averages.
Window frames are particularly important when duplicate ordering values or time gaps exist. ROWS defines a physical number of rows, while RANGE groups rows according to the ordering value, meaning duplicate order values can be treated as peers. Choosing the wrong frame can therefore produce unexpected running totals or moving-window results. The presentation also applies window functions to practical analytical patterns including Top-N per group, deduplication, gaps and islands, sessionisation, and cohort retention. In the cohort example, MIN(...) OVER (PARTITION BY cust_id) identifies each customer’s first month without collapsing the order rows, month arithmetic determines the customer’s offset from that cohort, and a windowed MAX supplies the month-zero population used as the retention denominator.
Performance depends largely on the sorting required by each distinct window specification. Each different PARTITION BY and ORDER BY combination may require its own sort, after which the window calculations can generally be completed in a pass over the sorted data. Reusing a named window specification can allow multiple functions to share the same sort, while an index matching the partition and ordering columns may eliminate or reduce sorting work. Execution plans should therefore be inspected for WindowAgg and Sort operations when tuning analytical queries. The presentation also highlights several common mistakes: filtering a window result directly in WHERE, using LAST_VALUE() without extending its frame, relying on non-deterministic ROW_NUMBER(), confusing RANGE with ROWS, and assuming window functions are always preferable to a simple GROUP BY. The right approach is to define the window precisely, choose ranking and frame semantics intentionally, and verify performance with the execution plan.
SQL Views & Materialized Views: Designing, Securing and Optimizing Database Reporting
SQL views provide a reusable interface over database queries without physically storing their result, while materialized views store the computed result for faster reads. A regular view is expanded into the calling query and is therefore always current, but the underlying computation is performed whenever the view is queried. A materialized view, in contrast, behaves more like a stored reporting table: its result can be indexed and read quickly, but it is only as fresh as its most recent refresh. This distinction makes ordinary views useful for consistent business definitions and abstraction, while materialized views are particularly valuable for expensive analytical queries and dashboards where some data staleness is acceptable.
Good view design requires deliberate layering rather than building increasingly deep chains of views. The presentation recommends separating a model into a base layer for cleaning and standardising source data, a business or entity layer for joining meaningful entities, and a reporting layer for aggregates consumed by dashboards and exports. This keeps dependencies understandable and makes each stage independently testable. CREATE OR REPLACE VIEW can update a definition while preserving a stable column structure, whereas dropping a view with CASCADE can remove dependent objects and therefore requires caution. Simple predicates from an outer query can often be pushed into a view’s plan, but aggregates, DISTINCT, and window functions can prevent such pushdown and make deeply nested reporting views expensive to execute.
Views can also act as controlled write and security boundaries. A simple view over one table without aggregation, DISTINCT, GROUP BY, or set operations can be automatically updatable, while WITH CHECK OPTION prevents inserts or updates that would create rows outside the view’s defining predicate. More complex views can route writes through INSTEAD OF triggers. From a security perspective, views can expose only approved columns, restrict rows by role or tenant, and provide a controlled interface while base-table permissions are revoked. PostgreSQL’s security_barrier option can prevent certain predicate-pushdown techniques from leaking filtered information through user-defined functions, while row-level security is generally the stronger mechanism for enforcing tenant isolation directly on the underlying table.
Materialized views are particularly effective when a query is expensive but its result does not need to be generated for every request. A materialized reporting view can aggregate millions of source rows once and then serve indexed results to dashboards. REFRESH MATERIALIZED VIEW recomputes the result but can block readers, whereas REFRESH MATERIALIZED VIEW CONCURRENTLY allows readers to continue accessing the existing result during the refresh and requires a suitable unique index. When full recomputation becomes too expensive, an incremental rollup table can instead recompute only a recent time window and use an upsert to replace the affected summaries. This makes the cost of each refresh depend primarily on the changed window rather than the entire historical dataset.
Production view systems also require operational discipline. View dependencies should be inspected before schema changes, definitions should remain in source control rather than existing only inside the database, and reporting systems should expose the freshness of materialized results through fields such as refreshed_at and refresh logs. Common mistakes include using SELECT * inside views, creating excessive layers of nested views, forgetting to refresh materialized views, refreshing without CONCURRENTLY when reader availability matters, hiding important business logic exclusively inside database objects, and using CASCADE without checking dependencies. The practical decision is straightforward: use a view when you need a reusable, always-current query definition; a materialized view when an expensive result can tolerate scheduled refreshes; an incremental rollup table when only a bounded recent window changes; and row-level security when tenant isolation must be enforced at the table level.
SQL Transactions & Concurrency: ACID, Isolation Levels, Locking, Deadlocks and Safe Patterns
SQL transactions provide the foundation for reliable database operations by grouping multiple statements into a single logical unit of work. The ACID properties describe the guarantees that make transactions dependable: atomicity ensures that all statements succeed or none do, consistency preserves database constraints, isolation controls what concurrent transactions can observe, and durability ensures committed changes survive failures. Transaction boundaries are controlled with BEGIN, COMMIT, and ROLLBACK, while SAVEPOINT enables partial rollback inside a larger transaction. Keeping transactions short is equally important because long-running transactions can retain locks, increase contention, and prevent old row versions from being cleaned up efficiently.
Concurrency becomes difficult when multiple transactions access the same data simultaneously. Common anomalies include dirty reads, non-repeatable reads, phantom reads, lost updates, and write skew. Database isolation levels determine which of these effects are prevented. READ COMMITTED is commonly appropriate for ordinary OLTP workloads, while REPEATABLE READ provides a consistent snapshot across multiple statements and is useful for multi-query reports. SERIALIZABLE provides the strongest isolation by making concurrent execution behave as though transactions were executed one after another, but conflicting transactions may be aborted and therefore require application-level retry logic. The exact behaviour differs across database engines, so applications should never assume that the default isolation level is universal.
Explicit locking is essential when correctness depends on reading a value and then making a decision based on it. SELECT ... FOR UPDATE locks selected rows until the transaction ends, preventing another transaction from modifying them concurrently. In simpler cases, an atomic statement such as UPDATE items SET stock = stock - 1 WHERE id = 5 AND stock > 0 is even better because the database performs the decision and modification as one operation. NOWAIT can fail immediately instead of waiting for a lock, while SKIP LOCKED allows workers to ignore already-claimed rows and is particularly useful for database-backed job queues. Deadlocks occur when transactions wait on each other in a cycle; the standard defence is to acquire locks in a deterministic order, keep transactions short, and retry the transaction that the database chooses as the deadlock victim.
Safe concurrent writes also require avoiding application-level race conditions. A classic check-then-insert pattern can allow two clients to observe that a record does not exist and then both attempt to insert it. Database-enforced uniqueness combined with INSERT ... ON CONFLICT or MERGE allows the database to resolve that race atomically. Idempotency keys provide another important pattern: a unique key associated with a request allows retries to become harmless no-ops rather than duplicate payments or operations. For background processing, FOR UPDATE SKIP LOCKED can be combined with a status column to let multiple workers claim different jobs without blocking one another. Optimistic concurrency using a version column is preferable for long-lived user interactions, while pessimistic locking with FOR UPDATE is better suited to short, high-contention operations such as inventory or stock decrements.
The central principle of SQL concurrency is to make correctness explicit rather than relying on timing or assumptions. Transactions should be opened as late as possible, perform the required database work, and commit immediately; applications should never wait for user input or external network calls while holding database locks. A robust money-transfer transaction, for example, can lock both accounts in deterministic order, verify that the source balance is sufficient, perform the debit and credit, and write the audit record within the same transaction so the audit cannot exist independently of the transfer. Production systems should combine atomic updates, appropriate isolation, deterministic lock ordering, unique constraints, idempotency keys, retry handling, and monitoring for blocked or idle-in-transaction sessions. Together, these patterns turn concurrency from a source of intermittent bugs into a deliberately controlled part of database design.
SQL Subqueries & CTEs: Correlated Queries, EXISTS, Derived Tables and Common Table Expressions
Subqueries and Common Table Expressions (CTEs) are the foundation of writing complex SQL in a structured and maintainable way. Instead of solving a difficult query in one deeply nested statement, they allow developers to decompose the problem into logical steps. A subquery can appear in SELECT to return a single value, in WHERE to filter rows, in FROM as a derived table, or in a WITH clause as a named CTE. The position of the subquery determines what it must return—whether a scalar value, a list, or an entire table. CTEs extend this idea by giving intermediate results meaningful names, making long analytical queries easier to read, debug, and review.
Scalar and correlated subqueries solve different kinds of problems. An uncorrelated subquery is independent of the outer query and is typically evaluated once, making it efficient for tasks such as comparing every order against the global average. A correlated subquery references columns from the outer query, meaning it is logically evaluated for each row, such as comparing an order against its own customer’s average purchase. While correlated queries are expressive, they can become expensive on large datasets and are often better rewritten as joins or window functions. When testing for existence rather than retrieving values, EXISTS is generally superior to IN because it is NULL-safe and stops searching as soon as the first matching row is found.
Derived tables and CTEs provide elegant ways to structure intermediate calculations. A derived table is an inline temporary table that is particularly useful when filtering on aggregates or window-function results, while a CTE transforms nested logic into a top-down pipeline where each step feeds the next. Modern SQL engines usually optimize CTEs similarly to subqueries, but PostgreSQL also offers MATERIALIZED and NOT MATERIALIZED to control whether a CTE is computed once or inlined for predicate pushdown. Writable CTEs take this even further by allowing INSERT, UPDATE, or DELETE operations with RETURNING, enabling atomic multi-step operations such as archiving old records in a single statement.
Recursive CTEs introduce controlled iteration into standard SQL, making hierarchical queries possible without procedural code. They combine an anchor query with a recursive term connected by UNION ALL, allowing databases to traverse organizational charts, category trees, bills of material, and graph paths while carrying additional state such as depth and ancestry. Every recursive query should include a depth limit or cycle guard to prevent infinite recursion. Although recursive CTEs are powerful, many day-to-day analytical problems are better expressed through chained non-recursive CTEs that progressively filter, aggregate, enrich, rank, and present data in clearly separated stages.
Choosing the right query structure is ultimately a balance between readability and performance. Use scalar subqueries for single computed values, EXISTS or NOT EXISTS for membership tests, derived tables or CTEs when filtering aggregates, and window functions when a grouped value must appear alongside detailed rows. The presentation highlights one of the most valuable optimization patterns in SQL: replacing correlated aggregate subqueries with a pre-aggregated join, which allows the database to compute expensive summaries once instead of repeating them for every output row. Well-structured SQL is therefore not only easier to maintain—it also gives the query optimizer a better opportunity to generate efficient execution plans.
SQL String Functions: Text Cleaning, Slicing, Searching, Splitting and Concatenation
SQL string functions are essential for cleaning, transforming, parsing, and searching textual data inside relational databases. Functions such as LENGTH, CHAR_LENGTH, UPPER, LOWER, and INITCAP handle measurement and case transformation, while OCTET_LENGTH measures bytes rather than characters and becomes important with multibyte data. The distinction matters because character length and byte length are not always the same. SQL also provides functions such as REVERSE, REPEAT, ASCII, and CHR for specialised text manipulation. For reliable comparisons, the presentation recommends storing the original value while using a folded or normalised representation for comparison rather than permanently destroying the source text.
Parsing text becomes straightforward when SUBSTRING, LEFT, RIGHT, POSITION, and SPLIT_PART are used according to the structure of the data. SQL string positions are generally 1-based, so forgetting this can introduce silent off-by-one errors. SUBSTRING can extract a fixed region or work together with POSITION to locate delimiters dynamically, while SPLIT_PART is often clearer when processing consistently delimited values such as order codes or email addresses. Cleaning functions then prepare imported data for reliable comparison: TRIM, REPLACE, and TRANSLATE remove unwanted characters, LPAD and RPAD create fixed-width values, and REGEXP_REPLACE handles more complex transformations such as retaining only digits in phone numbers or collapsing repeated whitespace.
Concatenation requires particular care around NULL values. The || operator propagates NULL, meaning that a single missing component can make an entire concatenated result NULL. CONCAT treats NULL values as empty strings, while CONCAT_WS is especially useful for addresses and other multi-part values because it inserts the separator only between non-NULL components. COALESCE provides another explicit way to supply fallback values. For searching, the presentation recommends using the weakest mechanism that satisfies the requirement: equality can use a normal B-tree index, prefix searches such as LIKE 'Adi%' can use an index, while a leading wildcard such as LIKE '%adi%' generally prevents a normal B-tree range scan. For PostgreSQL workloads, trigram indexes can make contains searches index-assisted, while full-text search with to_tsvector and to_tsquery is more appropriate for natural-language document search.
Regular expressions provide more expressive validation, extraction, and replacement than LIKE, but they are generally CPU-bound and should not be used when a simpler indexed predicate is sufficient. The deck also demonstrates how delimited text can be expanded into rows using STRING_TO_ARRAY and UNNEST, after which the resulting values can be trimmed, analysed, joined, or aggregated with STRING_AGG. Although this technique is useful for cleaning denormalised imports, repeatedly splitting a comma-separated column is a sign that the data may be better represented in a normalised child table. Similarly, STRING_AGG can rebuild ordered lists across rows, with ORDER BY placed inside the aggregate and DISTINCT used when repeated values need to be removed.
Performance and correctness depend heavily on keeping text predicates indexable and understanding database-specific behaviour. Wrapping an indexed column in functions such as UPPER() or LOWER() can prevent a plain index from being used unless a corresponding expression index exists; PostgreSQL expression indexes and trigram indexes provide practical solutions. Collation also affects case comparison, sorting, and other text semantics, so assumptions about whether values such as Aditi and aditi are equal should never be made without considering the database and column collation. Common pitfalls include NULL propagation in concatenation, treating character positions as zero-based, leaving wildcard characters unescaped in user input, unexpected CHAR padding, and storing comma-separated lists instead of normalised relationships. A robust text-processing workflow is therefore to normalise first, validate without immediately deleting bad records, deduplicate using a normalised key, and preserve the original data for review.
SQL Stored Procedures, Functions & Triggers: Server-Side Logic, Control Flow and Database Automation
Stored procedures, functions, and triggers allow application logic to execute directly inside the database, but each object has a distinct role. A function returns a value or table and can be called from SELECT, WHERE, or joins, making it suitable for reusable computations and parameterised reporting. A procedure is invoked with CALL and is designed for multi-step maintenance or batch operations where transaction control such as COMMIT and ROLLBACK is required. Triggers are different again: they execute automatically in response to database events and are particularly useful for integrity enforcement, audit trails, and controlled row-level transformations. Choosing the correct object prevents server-side code from becoming unnecessarily complex or difficult to maintain.
SQL functions can be implemented as simple SQL expressions or with procedural languages such as PL/pgSQL when variables, branching, loops, or exception handling are required. A scalar function accepts parameters and returns one value, while a table-returning function can behave much like a parameterised view that can be joined and composed with other queries. Function volatility is also important because it communicates assumptions about how results behave: IMMUTABLE indicates that the same inputs always produce the same result without table access, STABLE allows results to remain consistent within a statement while reading database state, and VOLATILE permits results to change between calls. Declaring volatility accurately gives the query planner more information and can allow immutable functions to participate in expression indexes.
PL/pgSQL adds procedural control flow through variables, IF statements, loops, records, and exception blocks. However, the presentation strongly emphasises a set-based-first approach: a loop that performs an operation row by row is usually far slower than a single SQL statement that performs the same transformation across the entire dataset. Exception handling should likewise be deliberate. RAISE EXCEPTION can abort an operation with a clear message, while named conditions such as unique_violation can be caught when recovery is genuinely required. Broadly swallowing errors with a blanket WHEN OTHERS THEN NULL is dangerous because it can hide failures and leave data in an unexpected state.
Procedures become particularly useful for long-running maintenance tasks because they can commit work between batches. The deck demonstrates chunked archival using batches of 10,000 rows, FOR UPDATE SKIP LOCKED, and GET DIAGNOSTICS to monitor affected rows. Committing between chunks prevents a maintenance job from holding one enormous transaction and helps keep locks and write-ahead logging growth manageable. Triggers provide another form of server-side automation: BEFORE row triggers can modify or validate NEW before a row is stored, while AFTER row triggers can record what actually happened, making them well suited to audit trails. The presentation’s audit example captures inserts, updates, and deletes using TG_OP, CURRENT_USER, and JSONB snapshots of the old and new rows.
The most important lesson is not simply how to write server-side SQL, but knowing when it belongs in the database. Logic that must never be bypassed, such as integrity rules and audit trails, is a strong database-side candidate, as are set-based transformations and bulk maintenance close to the data. Frequently changing business rules, workflows involving external services, retry queues, and complex application behaviour are generally better kept in the application layer. Performance and observability also matter: row-level triggers execute once per affected row, while set-based statements can process large datasets far more efficiently. Production database code should therefore be version-controlled, tested with assertion queries, deployed through repeatable migrations, instrumented with tools such as EXPLAIN ANALYZE, and kept as set-based as possible.
SQL Set Operations & Conditional Logic: UNION, CASE, NULL Handling and Advanced SQL Patterns
SQL set operations provide a way to combine the results of multiple queries vertically, while conditional expressions allow SQL to express branching logic directly inside a query. UNION combines result sets and removes duplicates, whereas UNION ALL simply appends the rows and is generally the better default when duplicate elimination is unnecessary. INTERSECT returns rows present in both result sets, while EXCEPT returns rows present in the first result but absent from the second; Oracle uses MINUS for the latter operation. Set operations require the same number of columns with compatible types, and columns are matched by position rather than name. The first SELECT determines the output column names, while a final ORDER BY applies to the combined result.
A common source of SQL errors is confusing set operations with joins. Set operations stack rows with the same structure, whereas joins combine related tables horizontally by adding attributes and can change row counts through fan-out. CASE addresses a different problem: it returns a value based on conditions and can therefore be used in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and aggregate expressions. SQL supports both searched CASE, which evaluates arbitrary Boolean conditions from top to bottom, and simple CASE, which compares one expression against multiple values. The first matching branch wins, so condition ordering matters. If ELSE is omitted, unmatched rows produce NULL, and all branches must return compatible types.
SQL’s handling of NULL is based on three-valued logic: a condition can evaluate to TRUE, FALSE, or UNKNOWN. Comparisons involving NULL normally produce UNKNOWN, which explains why NULL = NULL is not true and why = NULL should be replaced with IS NULL. This behaviour becomes especially important with NOT IN, because a NULL in the comparison list can make the predicate evaluate to UNKNOWN and prevent rows from qualifying. SQL provides several tools for controlling this behaviour. COALESCE returns the first non-NULL argument, NULLIF converts a specified value into NULL, and IS DISTINCT FROM provides NULL-safe equality semantics. Together, these functions support fallback values, data cleaning, safe division, outer-join reporting, and comparisons involving nullable columns.
These features also enable several practical SQL patterns without requiring procedural code. Conditional aggregation with SUM(CASE...) can create portable static pivots, transforming categories such as quarters into separate columns. CASE can implement custom business-priority sorting, create age or revenue buckets, perform conditional updates, and construct optional filters. For data reconciliation, running EXCEPT in both directions reveals rows missing from either system; the two difference sets can then be combined and paired with a FULL OUTER JOIN to produce a labelled report showing missing, extra, or differing records. The presentation also highlights an important performance consideration: UNION and EXCEPT require duplicate elimination, typically through sorting or hashing, while UNION ALL performs a straightforward append. For large datasets, an indexed NOT EXISTS anti-join can sometimes be a more efficient alternative to EXCEPT.
The key to reliable SQL set and conditional logic is understanding exactly how rows, values, and NULL states behave. Use UNION ALL when duplicate removal is not required, explicitly parenthesise mixed set-operation chains because INTERSECT has higher precedence than UNION and EXCEPT, order CASE conditions from narrow to broad, and provide an ELSE when an unmatched result should not become NULL. For safe ratios, the presentation recommends the idiom COALESCE(a / NULLIF(b, 0), 0), which prevents division by zero while supplying a fallback value. Finally, remember that COUNT(column) ignores NULL values whereas COUNT(*) counts every row. These principles make set operations predictable, conditional logic expressive, and NULL-heavy SQL substantially easier to reason about.
DECISION STATS 