Page 7 - SQL

A plain GROUP BY over a date column is rarely enough for time series aggregation, because days without events simply do not show up in the result. This article explains how to build time buckets with DATE_TRUNC, how calendar tables and generate_series reliably fill missing periods, and how to compute comparison periods like previous year or previous week directly in SQL.

When requests suddenly hang while CPU usage stays low, lock contention is often the reason. With performance_schema in MySQL and pg_locks in PostgreSQL, lock contention can be diagnosed systematically, instead of guessing anew with every incident which session is blocking which other one.

A FULL OUTER JOIN returns all rows from both tables involved, regardless of whether a matching counterpart exists on the other side, filling missing values with null. Not every database supports this operation natively though: MySQL had no dedicated FULL JOIN syntax until fairly recently, and older versions of other systems sometimes lack the feature entirely as well. This article covers how to reliably recreate a FULL OUTER JOIN using a combination of LEFT JOIN, RIGHT JOIN, and UNION, which alternative using LEFT JOIN and NOT EXISTS is preferable in certain cases, and where typical correctness and performance pitfalls lurk in the emulation.

Nested aggregation computes a metric from values that are already aggregated, such as average daily revenue from daily sums, instead of directly from individual rows. Since SQL forbids nesting aggregate functions like AVG(SUM(column)) directly at the same SELECT level, this article shows how subqueries and CTEs implement nested aggregation cleanly and correctly.

Eventual consistency is not a compromise for poorly built systems but a deliberate consequence of the CAP theorem in every distributed system, including read replicas in relational databases. Whoever concretely understands consistency models, conflict resolution, and replication lag makes better decisions about where a short delay is acceptable and where it is not.

Few behaviors of auto-increment sequences unsettle developers as reliably as an ID column with missing values. The reflex to treat that as a bug and fix it with extra logic almost always causes more problems than it solves: locked tables, blocked transactions, and worse scalability. This article explains why gaps are a structurally unavoidable consequence of rollbacks, sequence caching, and concurrent transaction execution, why that is completely harmless in the vast majority of cases, and where the rare, legitimate exceptions lie.

Choosing a primary key type looks like a minor detail at first glance, but it directly affects the physical structure of the index, fragmentation, cache behavior, and a system's ability to assign keys without central coordination. Classic auto-increment yields compact, sorted values but fails across distributed write paths. Random UUIDs solve the coordination problem but destroy insert order in the index. Sortable variants like ULID and UUIDv7 try to combine both worlds. This article ranks the three approaches by their actual effect on index structure and operations, not by gut feeling.

Weighted aggregates account for the fact that not every value in a report should count equally, for example because one order contains more items than another, or an exam counts more than a quiz. This article shows why a plain AVG returns misleading averages in such cases, and how weighted average and weighted sum, using SUM(value times weight) divided by SUM(weight), compute the correct result.

Anyone who needs to reconstruct the state of a row at an arbitrary point in the past classically builds a custom audit trail table with triggers or application code. The SQL:2011 standard defines a far more robust alternative: system-versioned tables, where the database itself automatically maintains a complete history with valid time periods on every UPDATE and DELETE, with no extra application code required. SQL Server and MariaDB already implement this concept in a production-ready way, with dedicated syntax for time-travel queries. This article shows how the mechanism actually works, how it differs from hand-built audit trails, and where its limits lie.

Anyone who inserts, updates or deletes large data volumes one row at a time produces lock escalation, bloated transaction logs and runtimes measured in hours. With well designed batching, temporarily disabled indexes and correctly sized chunks, the same bulk operations become a task of minutes, without blocking the database's live operation.