Backfill Query

backfill_query.sql

WITH discontinued_derivatives AS (
  SELECT id
  FROM research_site.derivatives
  WHERE date_discontinued IS NOT NULL
  AND date_discontinued < current_date
),

unavailable_models AS (
  SELECT slug
  FROM research_site.models
  WHERE NOT FACTORY_ORDER_AVAILABLE
),

inactive_dealership_offers AS (
    SELECT O.id
    FROM quotes_site.offers O
    JOIN dealers_site.dealerships D ON D.id = O.dealership_id
    JOIN dealers_site.dealership_transitions DT ON D.id = DT.dealership_id AND DT.most_recent
    WHERE DT.to_state IN ('archived', 'suspended')
),

cars_with_inactive_derivative AS (
  SELECT C.id
  FROM quotes_site.cars C
  JOIN quotes_site.car_configurations CC ON CC.id = C.car_configuration_id
  JOIN discontinued_derivatives DD ON DD.id = CC.cap_engine_id
  WHERE C.status NOT IN (5,6)
  AND C.created_at::date < '2019-01-01'::date
),

cars_with_unavailable_model AS (
  SELECT C.id
  FROM quotes_site.cars C
  JOIN quotes_site.car_configurations CC ON C.car_configuration_id = CC.id
  JOIN unavailable_models UM ON UM.slug = CC.model_slug
  AND C.status NOT IN (5,6)
  AND C.created_at::date < '2019-01-01'::date
),

cars_with_inactive_dealership AS (
  SELECT C.id
  FROM quotes_site.cars C
  JOIN inactive_dealership_offers IDF ON IDF.id = C.offer_id
  WHERE C.status NOT IN (5,6)
  AND C.created_at::date < '2019-01-01'::date
),

distinct_cars AS (
  SELECT DISTINCT(id) as id
  FROM (
    (SELECT id FROM cars_with_unavailable_model)
    UNION
    (SELECT id FROM cars_with_inactive_dealership)
    UNION
    (SELECT id FROM cars_with_inactive_derivative)
  )
)

SELECT id
FROM distinct_cars
ORDER BY id
;