Notes

Short SQL notes by Malek Damiri. Each one is a pattern I use in revenue reporting, with a small dataset you can paste into DuckDB and the exact output it returns.

• Build a date spine so empty days countGROUP BY drops the days with no rows. A date spine puts them back, and changes the average.
• Keep the latest row per key with QUALIFYTaking max() of each column mixes values from different rows. QUALIFY keeps one whole row per key.
• Check row counts before you trust a joinA join to a table with duplicate keys multiplies revenue. Two quick checks catch it.
• Find streaks with gaps and islandsSubtract a row number from a month number and consecutive months share a value. That's the whole trick.
• LAG compares to the previous row, not the previous monthWhen a month is missing, LAG silently compares to an older one. Fill the months first.