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
Tag
how-to
590 posts
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
CSV
Tailing and Following a CSV That Is Still Being Written
Follow a growing CSV with tail -f, skip the header, survive rotation with tail -F and read only complete lines, plus a Python follower and the buffering trap.
6 min read
Excel
Rank Customers by Spend in Excel and SQL With Ties Handled
Rank customers by spend with RANK.EQ and sorted lists in Excel and with RANK, DENSE_RANK and ROW_NUMBER in SQL, including ties, top N and ranks per group.
7 min read
XML
Moving From XML to JSON Gracefully Without Losing Meaning
Generic converters guess at lists, attributes and mixed text. Write a small mapping with explicit rules so the JSON stays predictable for every record.
6 min read
JSON
Checking a JSON Column Against a Schema in PostgreSQL
Enforce the shape of a PostgreSQL jsonb column with CHECK constraints, NOT VALID and jsonpath, including the NULL trap that lets a string id pass.
8 min read
CSV
Compressing CSV Files and Reading Them Without Unzipping
Compress a CSV with gzip or zstd and read it directly with zcat, pandas, DuckDB or Python: 130 MB became 34 to 43 MB and no unzipped copy was needed.
6 min read
Excel
Running Total of Monthly Sales Both Ways in Excel and SQL
Build a monthly running total in Excel with expanding ranges, SUMIFS and SCAN, and in SQL with a window function, including ties and resets.
7 min read