CSV
Calculating Month Over Month Growth From a CSV in SQL
Compute month over month growth with lag(): revenue of 46.50, 202.00 and 382.00 grew 334.4% and then 89.1%. Partial months, gaps and a zero base need care.
6 min read
Library
Everything you need to level up your coding skills.
YAML
Config Driven Pipelines and Their Limits With Numbers
Measure what a YAML pipeline file costs to load, validate and order from 100 to 50,000 steps, then see where the file stops being the right place for logic.
7 min read
Excel
Lookup With Approximate Match Versus SQL Range Join
Compare Excel approximate match lookups with SQL range joins for tiers, rates and bands: how each handles gaps, boundaries and overlaps, and which to use.
7 min read
XML
Keeping Both XML and JSON Output From One Source Model
Build one internal model and two small renderers, negotiate the format with the Accept header, and test that both carry the same data on awkward cases.
6 min read
JSON
Flattening JSON in SQL With Lateral Joins in PostgreSQL
Flatten JSON arrays into rows in PostgreSQL with CROSS JOIN LATERAL, jsonb_to_recordset and WITH ORDINALITY, keep empty arrays, and compare MySQL and SQLite.
8 min read
CSV
Filling Gaps in a CSV Time Series With SQL and DuckDB
Find and fill missing days in a CSV time series: build a calendar with range(), left join the data and choose zero, carry-forward or interpolation.
7 min read
Excel
Count Distinct Customers per Month Both Ways in Excel
Count distinct customers per month in Excel with UNIQUE and FILTER, SUMPRODUCT or a PivotTable, and in SQL with COUNT(DISTINCT), with blanks handled.
6 min read
PostgreSQL
Indexes and Primary Keys on Partitioned Tables in PostgreSQL
Indexes and primary keys on partitioned tables in PostgreSQL: the partition key rule, local indexes, ON ONLY builds, foreign keys and lookup patterns.
7 min read
CSV
Joining Two CSV Files on a Messy Key Without Losing Rows
Join two CSVs on a messy key: a strict join matched 1 of 4 orders, a normalized key matched 3, and an anti-join listed the rest. Check counts and duplicates.
6 min read
XML
Migrating a Legacy XML Interface to JSON in Safe Steps
A step-by-step plan for moving an XML service to JSON without breaking consumers: freeze, map, run both, measure, announce a date and retire.
6 min read
Excel
Remove Duplicates Keeping the Latest Row Both Ways
Remove duplicate rows but keep the latest per key, in Excel with sort and Remove Duplicates, XLOOKUP or SORTBY, and in SQL with DISTINCT ON or ROW_NUMBER.
7 min read
JSON
Versioning a JSON Schema Without Breaking Consumers
Which schema changes break old producers and old consumers: a tested compatibility matrix, open versus closed objects, version numbers and a CI check.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.