gistfile1.sql

WITH
  active_qcds AS (
  SELECT
    a.qcd_quote_sheet_category_definition_pk AS qcd_quote_sheet_category_definition_pk
  FROM
    website.qcd_quote_sheet_category_definition AS a
  JOIN (
    SELECT
      MAX(b.qcd_activation_time) AS qcd_activation_time,
      b.qcd_rca_request_category_pk
    FROM
      website.qcd_quote_sheet_category_definition AS b
    WHERE
      qcd_activation_time IS NOT NULL
    GROUP BY
      b.qcd_rca_request_category_pk) AS y
  ON
    y.qcd_activation_time = a.qcd_activation_time
    AND y.qcd_rca_request_category_pk = a.qcd_rca_request_category_pk ),
  quote_sheets AS (
  SELECT
    category_id,
    category_definition_id,
    service_id
  FROM
    quoting.pg_quote_sheets AS pg
  JOIN
    a.services AS services
  ON
    pg.service_pk = services.service_pk
  WHERE
    pro_intent = 2
  GROUP BY
    category_id,
    service_id,
    category_definition_id)
SELECT
  category_id,
  service_id
FROM
  quote_sheets
JOIN
  active_qcds
ON
  active_qcds.qcd_quote_sheet_category_definition_pk=quote_sheets.category_definition_id