Skip to content

Instantly share code, notes, and snippets.

@peterc

peterc/query.sql Secret

Created May 12, 2021 11:01
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save peterc/19f2960c183edaddf5c3d6b8c4804f3a to your computer and use it in GitHub Desktop.
Save peterc/19f2960c183edaddf5c3d6b8c4804f3a to your computer and use it in GitHub Desktop.
Download ZIP
query.sql
Raw
query.sql
SELECT
dataset_day.dataset_id, day, COALESCE(count, 0) AS count
FROM (
SELECT
d.id AS dataset_id,
day::date
FROM
generate_series(current_date - interval '30 day', current_date, '1 day') AS day,
dataset d
ORDER BY
d.id ASC,
day ASC) dataset_day
LEFT JOIN (
SELECT
dataset_id,
timestamp::date AS date,
count(*) AS count
FROM
logs_import
GROUP BY
dataset_id,
date) counts ON dataset_day.dataset_id = counts.dataset_id
AND dataset_day.day = counts.date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment