Page 4 - SQL

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.

A network timeout when calling a payment or order API says nothing about whether the write actually failed on the server. A naive retry mechanism therefore risks duplicate charges, duplicate orders, or duplicate notifications. An idempotency key solves this problem not in application logic but structurally at the database level, with a UNIQUE constraint that reliably prevents duplicate processing even when the same request arrives in parallel. This article shows the practical table design, response snapshots, and a clean cleanup strategy.

Not every database starts out with cleanly defined foreign key constraints. In grown systems they are often missing, were forgotten during a migration, or got disabled over short term performance concerns. The result is orphaned references, rows that point to long deleted or never existing parent records. This article shows how to systematically find such cases, which repair strategies are realistic, and how to safely add the missing constraint afterward without risking the running system.

RANK and DENSE_RANK answer the question of a row's absolute position within a sorted set, but in many analyses the position itself is not what matters, the relative placement compared to the whole set is. Does a value fall in the top ten percent, somewhere in the middle, or closer to the bottom of the distribution. That is exactly what PERCENT_RANK and CUME_DIST were built for, two window functions that return a continuous percentage instead of a fixed rank number. This article covers the underlying formulas, the practical difference between the two functions, how they compare to NTILE, and typical edge cases with small or heavily skewed data sets.

An ETL script that works on the first test run but produces duplicate records on the second run, or leaves the target in an inconsistent state after a partial failure, is not unusual. Claude helps avoid these pitfalls from the start by building idempotency, error handling and data quality checks directly into the generated extraction, transformation and load logic. This article shows the complete development process using a realistic migration example.

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.

A vaguely defined metric leads to contradictory reports, even when the underlying query is technically correct. Claude helps pin down precise KPI definitions, build cohort analyses and consolidated metric views, and automate recurring reports. This article covers the path from a fuzzy business question to a reliable business intelligence report.