query.sql

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