Skip to content

Instantly share code, notes, and snippets.

@CGA1123

CGA1123/backfill_query.sql

Last active May 31, 2019 22:14
Show Gist options
  • Save CGA1123/a198365323e2783ac70e347e4d5c8185 to your computer and use it in GitHub Desktop.
Save CGA1123/a198365323e2783ac70e347e4d5c8185 to your computer and use it in GitHub Desktop.
Download ZIP
Backfill Query
Raw
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment