Skip to content

Instantly share code, notes, and snippets.

@hexylena
Created July 10, 2021 10:36
Show Gist options
  • Save hexylena/a5ec95d3b4dc43c8fb6ad245677743d1 to your computer and use it in GitHub Desktop.
Save hexylena/a5ec95d3b4dc43c8fb6ad245677743d1 to your computer and use it in GitHub Desktop.
WITH
sent
AS (
SELECT
job.id AS job,
date_trunc('month', job.create_time)::DATE AS month,
job.job_runner_name AS runner,
ds_in.total_size AS size
FROM
job
LEFT JOIN job_to_input_dataset AS jtid ON job.id = jtid.job_id
LEFT JOIN history_dataset_association AS hda_in ON jtid.dataset_id = hda_in.id
LEFT JOIN dataset AS ds_in ON hda_in.dataset_id = ds_in.id
WHERE
job_runner_name LIKE 'pulsar%'
ORDER BY
job.id DESC
),
recv
AS (
SELECT
job.id AS job,
date_trunc('month', job.create_time)::DATE AS month,
job.job_runner_name AS runner,
ds_out.total_size AS size
FROM
job
LEFT JOIN job_to_output_dataset AS jtid ON job.id = jtid.job_id
LEFT JOIN history_dataset_association AS hda_out ON jtid.dataset_id = hda_out.id
LEFT JOIN dataset AS ds_out ON hda_out.dataset_id = ds_out.id
WHERE
job_runner_name LIKE 'pulsar%'
ORDER BY
job.id DESC
)
SELECT
sent.month, pg_size_pretty(sum(sent.size)) AS sent, pg_size_pretty(sum(recv.size)) AS recv
FROM
sent FULL JOIN recv ON sent.job = recv.job
GROUP BY
sent.month;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment