Last active
November 22, 2025 20:04
-
-
Save jmakeig/22da6fbf6492194c67b71b738ca492a4 to your computer and use it in GitHub Desktop.
Use case: Timeseries data that’s stored densely, but needs to be reported sparsely, i.e. with explicit zeros for missing periods. (RIGHT JOIN for the win!)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- PostgreSQL syntax | |
| DROP TABLE IF EXISTS revenue; | |
| CREATE TABLE revenue ( | |
| month TEXT, | |
| customer INT, | |
| channel TEXT, | |
| list NUMERIC | |
| ); | |
| -------------------------------------------------------- | |
| DELETE FROM revenue WHERE TRUE; | |
| INSERT INTO revenue (month, customer, channel, list) VALUES | |
| ('2025-10', 1, 'online', 101.1), | |
| ('2025-08', 1, NULL, 33.33), -- Y | |
| ('2025-08', 2, 'online', 55.55), | |
| ('2025-08', 2, NULL, 66.66), | |
| ('2025-04', 3, 'online', 66.66), -- Y | |
| ('2025-06', 1, NULL, 22.22); -- Y | |
| -------------------------------------------------------- | |
| WITH | |
| _revenue AS ( | |
| SELECT * FROM revenue | |
| -- This is where you need to do the filtering | |
| -- Everything else below is boilerplate | |
| WHERE customer IN (1, 3) | |
| AND month < '2025-10' | |
| ), | |
| customer_months AS ( | |
| SELECT month, customer | |
| FROM | |
| -- CROSS JOIN | |
| (SELECT DISTINCT customer FROM _revenue), | |
| (SELECT to_char(month, 'YYYY-MM') AS month FROM | |
| generate_series( | |
| to_timestamp((SELECT MIN(month) FROM revenue), 'YYYY-MM'), | |
| to_timestamp((SELECT MAX(month) FROM revenue), 'YYYY-MM'), | |
| '1 month'::interval | |
| ) AS month) | |
| ) | |
| SELECT | |
| * | |
| FROM | |
| _revenue | |
| -- Unlike ON, USING deduplicates the joined columns in the output. | |
| -- This makes * do what you expect. | |
| RIGHT JOIN customer_months USING(customer, month) | |
| ORDER BY month DESC, customer ASC | |
| ; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- PostgreSQL 14 | |
| SELECT | |
| days AS observation_date, | |
| -- Explicitly coalescing missing values to zero. | |
| -- This logic will be use case-dependent. | |
| coalesce(metrics.metric, 0) AS metric | |
| FROM | |
| -- Replace this with the dense metrics table | |
| (values ('2023-01-01'::date, 111111), ('2023-06-15'::date, 222222)) AS metrics (observation_date, metric) | |
| RIGHT JOIN | |
| generate_series('2023-01-01'::timestamp, now()::timestamp, '1 day'::interval) AS days | |
| ON metrics.observation_date = days | |
| ORDER BY | |
| days ASC | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment