Pino bio photo

Pino

I (infrequently) blog about data science, business intelligence, big data, web technologies and free software.

Twitter LinkedIn

It’s 2017, and people still underestimate relational databases and SQL. Postgres is in my opinion the open source database that has the richest feature set. One of those little gems is generate_series. For example, I use it to quickly check for missing data in a dataset:

SELECT dates.generate_series::date, count(*)
FROM aw_click_performance awcp
RIGHT OUTER JOIN (
SELECT * FROM generate_series(
(SELECT min(date_of_ad_click) FROM aw_click_performance),
current_date,
interval '1 day')
) AS dates ON awcp.date_of_ad_click = dates.generate_series
GROUP BY 1 ORDER BY 1;