campaigns_max_created_at.sql

SET search_path=query_test;
DROP SCHEMA IF EXISTS query_test CASCADE;
CREATE SCHEMA query_test;

CREATE TABLE campaign_jobs (
  id bigserial PRIMARY KEY,
  campaign_plan_id int
);

INSERT INTO campaign_jobs (campaign_plan_id)
SELECT 1
FROM generate_series(1, 10) g;

INSERT INTO campaign_jobs (campaign_plan_id)
SELECT 2
FROM generate_series(1, 10) g;

CREATE TABLE campaigns (
  id bigserial PRIMARY KEY,
  campaign_plan_id int
);

INSERT INTO campaigns (campaign_plan_id) VALUES
  (1),
  (2);

CREATE TABLE changeset_jobs (
  id bigserial PRIMARY KEY,
  campaign_id int REFERENCES campaigns,
  campaign_job_id int REFERENCES campaign_jobs,
  created_at timestamptz
);

-- Campaign 1: len(changeset_jobs) == len(campaign_jobs)
INSERT INTO changeset_jobs (campaign_id, campaign_job_id, created_at)
SELECT 1, id, now() + (id||' hour')::interval
FROM campaign_jobs WHERE campaign_plan_id = 1;

-- Campaign 2: len(changeset_jobs) == len(campaign_jobs)-2
INSERT INTO changeset_jobs (campaign_id, campaign_job_id, created_at)
SELECT 2, id, now() + (id||' hour')::interval
FROM campaign_jobs WHERE campaign_plan_id = 2 LIMIT 8;


SELECT COUNT(*) FROM changeset_jobs where campaign_id = 1;
SELECT COUNT(*) FROM changeset_jobs where campaign_id = 2;

-- Query for Campaign 1
SELECT
  max(changeset_jobs.created_at)
FROM campaign_jobs
INNER JOIN campaigns ON campaign_jobs.campaign_plan_id = campaigns.campaign_plan_id
LEFT JOIN changeset_jobs ON changeset_jobs.campaign_job_id = campaign_jobs.id
WHERE campaigns.id = 1
HAVING count(*) FILTER (WHERE changeset_jobs.created_at IS NULL) = 0;

-- Query for Campaign 2
SELECT
  max(changeset_jobs.created_at)
FROM campaign_jobs
INNER JOIN campaigns ON campaign_jobs.campaign_plan_id = campaigns.campaign_plan_id
LEFT JOIN changeset_jobs ON changeset_jobs.campaign_job_id = campaign_jobs.id
WHERE campaigns.id = 2
HAVING count(*) FILTER (WHERE changeset_jobs.created_at IS NULL) = 0;