Page 3 - SQL

Building a cross tabulation only after exporting to Excel gives up both performance and reproducibility. With CASE WHEN, FILTER, and, depending on the database, native PIVOT commands, pivot tables can be produced directly in SQL, consistent on every run and without a manual post-processing step.

Data integrity that only gets manually verified when needed is already violated before anyone looks. Automated checks built from constraints, checksums and statistical anomaly detection surface inconsistencies long before they turn into wrong reports or bad business decisions.

If you have only solved reporting problems with GROUP BY so far, you always lose the individual rows in the process. Window functions solve exactly this problem: they compute sums, rankings and averages over a defined window of rows without collapsing the result to one row per group. This article explains the OVER clause, PARTITION BY and ORDER BY step by step with real examples.

Real reporting dashboards rarely need a single window function, they need a combination of CTEs, aggregation and several nested window functions. Cohort retention tables and period-over-period growth rates can be calculated entirely in SQL, structured maintainably with WITH clauses, without raw data ever having to leave the database for the calculation.

A Polymorphic Association promises a single comments table for posts, photos, and videos at once, but the generic foreign key behind it breaks the database's referential integrity. This post shows how the classic type-plus-id pattern emerges, why it leads to orphaned rows, and which three alternatives solve the same problem without giving up foreign key constraints.

A LATERAL join lets a subquery in FROM or JOIN access columns of the preceding table within the same statement, something an ordinary join or an ordinary subquery cannot do. That makes top-N-per-group queries, dynamic per-row calculations, and multi-column table function calls possible, using LATERAL in PostgreSQL, and CROSS APPLY plus OUTER APPLY in SQL Server.

Running untested database migrations against production risks data loss, long downtime and silent inconsistencies. A clear test strategy built on the expand-contract pattern, rollback rehearsals and automated pipelines turns schema changes into a predictable process instead of a gamble.

Without an adjusted frame clause, LAST_VALUE almost never returns the actual last value of a partition, because the default window frame ends at the current row. Using FIRST_VALUE and LAST_VALUE correctly in practice means explicitly extending the window frame to the entire partition instead of relying on an invisible default.

An audit trail answers the question of who changed which value in which row and when, and application logs alone can rarely answer that question reliably. This post shows three established patterns for change history tables in SQL, from shadow tables with triggers through a central audit table to native system versioned tables, including performance cost and retention strategies.

Scale-up and scale-out solve different problems, yet they are often treated as interchangeable options. Vertical scaling means more power for a single instance, horizontal scaling means more instances for the same load. Get this decision wrong and you either build an unnecessarily complex distributed system or hit a hard capacity limit sooner than necessary.