Skip to content

Instantly share code, notes, and snippets.

@datchley

datchley/get-all-features-for-single-vin.sql

Last active March 30, 2020 21:57
Show Gist options
  • Save datchley/1c66e054c9c89261a7d7f14efbad2698 to your computer and use it in GitHub Desktop.
Save datchley/1c66e054c9c89261a7d7f14efbad2698 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
get-all-features-for-single-vin.sql
WITH all_builds AS (
/*
* Set this up as a Materialized View after each "sync" process
* which allows querying by VIN # or build_id
*/
SELECT
'Base' as type,
s.header as header,
s.description as description,
null as value,
s.build_id as build_id,
s.vin as vin
FROM vehicle_builds t
JOIN LATERAL(VALUES
('Country', t.country, t.id, t.vin),
('Make', t.make_name, t.id, t.vin),
('Body Type', t.body_type, t.id, t.vin),
('StyleID', style_id::TEXT, t.id, t.vin),
('Division Name', division_name, t.id, t.vin),
('Subdivision Name', subdivision_name, t.id, t.vin),
('Model', model_name, t.id, t.vin),
('Market Class', market_class_name, t.id, t.vin),
('Year', model_year::TEXT, t.id, t.vin),
('Style Name', style_name, t.id, t.vin),
('Style Name (wo Trim)', style_name_wo_trim, t.id, t.vin),
('Trim Name', trim_name, t.id, t.vin),
('MFR Model Code', mfr_model_code, t.id, t.vin),
('Fleet Only', is_fleet_only::TEXT, t.id, t.vin),
('Model Fleet', is_model_fleet::TEXT, t.id, t.vin),
('Num Passenger Doors', passenger_doors::TEXT, t.id, t.vin),
('Drivetrain', drivetrain, t.id, t.vin)
) s(header, description, build_id, vin) ON TRUE
UNION
SELECT
'StandardEquipment' as type,
header_name as header,
description,
null as value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_standard_equipment
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_standard_equipment.vehicle_build_id
-- NOTE: Maybe take this out as Chris has seen some vehicles with known equipment where this is null
-- WHERE vehicle_build_standard_equipment.install_cause IS NOT NULL
UNION
SELECT
'GenericEquipment' as type,
header_name as header,
category_name as description,
null as value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_generic_equipment
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_generic_equipment.vehicle_build_id
WHERE
vehicle_build_generic_equipment.install_cause IS NOT NULL
UNION
SELECT
'FactoryOption' as type,
header_name as header,
description,
null as value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_factory_options
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_factory_options.vehicle_build_id
WHERE vehicle_build_factory_options.install_cause IS NOT NULL
UNION
SELECT
'TechSpec' as type,
header_name as header,
title_name as description,
value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_technical_specs
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_technical_specs.vehicle_build_id
UNION
SELECT
'Color' as type,
'Exterior' as header,
color_name as description,
null as value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_exterior_colors
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_exterior_colors.vehicle_build_id
WHERE vehicle_build_exterior_colors.install_cause IS NOT NULL
UNION
SELECT
'Color' as type,
'Interior' as header,
color_name as description,
null as value,
vehicle_builds.id as build_id,
vehicle_builds.vin as vin
FROM vehicle_build_interior_colors
LEFT JOIN vehicle_builds on vehicle_builds.id = vehicle_build_interior_colors.vehicle_build_id
WHERE vehicle_build_interior_colors.install_cause IS NOT NULL
)
--
-- Query to get ALL features for a specific VIN # or build id
--
SELECT
type,
header,
description,
value
FROM
all_builds
WHERE
-- Or: build_id = 10
vin = '5UXCR4C06LLW64052'
;
Raw
listings-queries.sql
--
-- EXAMPLE 1:
-- Multiple options on one attribute
--
SELECT
listings.vehicle_build_id build_id,
vehicle_builds.vin,
listings.stock_no,
listings.state,
vehicle_builds.style_id,
vehicle_builds.model_year,
vehicle_builds.make_name,
vehicle_builds.model_name,
vehicle_builds.trim_name,
vehicle_builds.body_type,
vehicle_builds.passenger_doors,
vehicle_builds.drivetrain,
vehicle_builds.price_msrp,
vehicle_builds.price_destination,
vehicle_builds.price_invoice,
vehicle_builds.built_at,
vehicle_builds.market_class_id,
vehicle_builds.market_class_name,
vehicle_build_exterior_colors.color_code,
vehicle_build_exterior_colors.color_name,
vehicle_build_interior_colors.color_code,
vehicle_build_interior_colors.color_name,
listings.price,
listings.odometer,
listings.days_on_market,
listings.days_on_market_active,
listings.mc_id,
dealers.name dealer_name,
dealers.street dealer_address,
dealers.city dealer_city,
dealers.state dealer_state,
dealers.zip dealer_zip,
dealers.country dealer_country,
dealers.website dealer_website,
dealers.phone dealer_phone
FROM listings
LEFT JOIN dealers ON listings.dealer_id = dealers.id
LEFT JOIN vehicle_builds ON listings.vehicle_build_id = vehicle_builds.id
LEFT JOIN vehicle_build_interior_colors ON vehicle_build_interior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_interior_colors.install_cause IS NOT NULL
LEFT JOIN vehicle_build_exterior_colors ON vehicle_build_exterior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_exterior_colors.install_cause IS NOT NULL
WHERE
-- Only query current listings (nothing unlisted)
listings.unlisted_at IS NULL
-- Query on a specific style id (you could use ANDs on make, model, and trim fields as well)
-- Ex.: AND (model_year = 2020 AND make_name = 'BMW' AND model_name = 'X3 M' AND trim_name = 'Competition')
AND vehicle_builds.style_id = 406818
-- Use subquery to get the list of build ids matching the colors we're searching on
-- in the exterior color table.
AND listings.vehicle_build_id IN (
SELECT vehicle_build_id FROM vehicle_build_exterior_colors
WHERE
-- Looking for any vehicle matching one of these colors
color_name IN ('Toronto Red Metallic', 'Donington Grey Metallic')
AND install_cause IS NOT NULL
)
/* RESULTS
"build_id","vin","stock_no","price","state","dealer_name"
"109","5YMTS0C01L9B57694","W21547","84395","UT","Bmw Of Pleasant Grove"
"84","5YMTS0C0XL9B03519","W21371","82695","UT","Bmw Of Pleasant Grove"
"86","5YMTS0C07L9B66836","W21545","82695","UT","Bmw Of Pleasant Grove"
*/
--
-- EXAMPLE 2:
-- Single optin on multiple attributes
--
SELECT
listings.vehicle_build_id build_id,
vehicle_builds.vin,
listings.stock_no,
listings.state,
vehicle_builds.style_id,
vehicle_builds.model_year,
vehicle_builds.make_name,
vehicle_builds.model_name,
vehicle_builds.trim_name,
vehicle_builds.body_type,
vehicle_builds.passenger_doors,
vehicle_builds.drivetrain,
vehicle_builds.price_msrp,
vehicle_builds.price_destination,
vehicle_builds.price_invoice,
vehicle_builds.built_at,
vehicle_builds.market_class_id,
vehicle_builds.market_class_name,
vehicle_build_exterior_colors.color_code,
vehicle_build_exterior_colors.color_name,
vehicle_build_interior_colors.color_code,
vehicle_build_interior_colors.color_name,
listings.price,
listings.odometer,
listings.days_on_market,
listings.days_on_market_active,
listings.mc_id,
dealers.name dealer_name,
dealers.street dealer_address,
dealers.city dealer_city,
dealers.state dealer_state,
dealers.zip dealer_zip,
dealers.country dealer_country,
dealers.website dealer_website,
dealers.phone dealer_phone
FROM listings
LEFT JOIN dealers ON listings.dealer_id = dealers.id
LEFT JOIN vehicle_builds ON listings.vehicle_build_id = vehicle_builds.id
LEFT JOIN vehicle_build_interior_colors ON vehicle_build_interior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_interior_colors.install_cause IS NOT NULL
LEFT JOIN vehicle_build_exterior_colors ON vehicle_build_exterior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_exterior_colors.install_cause IS NOT NULL
WHERE
-- Only query current listings (nothing unlisted)
listings.unlisted_at IS NULL
-- Query on a specific style id (you could use ANDs on make, model, and trim fields as well)
-- Ex.: AND (model_year = 2020 AND make_name = 'BMW' AND model_name = 'X3 M' AND trim_name = 'Competition')
AND vehicle_builds.style_id = 406818
-- Looking for builds with a specific exterior color by name
AND listings.vehicle_build_id IN (
SELECT vehicle_build_id FROM vehicle_build_exterior_colors
WHERE
color_name = 'Black Sapphire Metallic'
AND install_cause IS NOT NULL
)
-- Looking for builds with a specific seat trim package
AND listings.vehicle_build_id IN (
SELECT vehicle_build_id FROM vehicle_build_factory_options
WHERE
header_name = 'SEAT TRIM'
AND oem_code = 'HDJA'
AND install_cause IS NOT NULL
)
/* RESULTS:
"build_id","vin","stock_no","price","state","dealer_name"
"85","5YMTS0C05L9B47668","W21580","82345","UT","Bmw Of Pleasant Grove"
*/
--
-- EXAMPLE 3:
-- Excluded option on one attribute
--
SELECT
listings.vehicle_build_id build_id,
vehicle_builds.vin,
listings.stock_no,
listings.state,
vehicle_builds.style_id,
vehicle_builds.model_year,
vehicle_builds.make_name,
vehicle_builds.model_name,
vehicle_builds.trim_name,
vehicle_builds.body_type,
vehicle_builds.passenger_doors,
vehicle_builds.drivetrain,
vehicle_builds.price_msrp,
vehicle_builds.price_destination,
vehicle_builds.price_invoice,
vehicle_builds.built_at,
vehicle_builds.market_class_id,
vehicle_builds.market_class_name,
vehicle_build_exterior_colors.color_code,
vehicle_build_exterior_colors.color_name,
vehicle_build_interior_colors.color_code,
vehicle_build_interior_colors.color_name,
listings.price,
listings.odometer,
listings.days_on_market,
listings.days_on_market_active,
listings.mc_id,
dealers.name dealer_name,
dealers.street dealer_address,
dealers.city dealer_city,
dealers.state dealer_state,
dealers.zip dealer_zip,
dealers.country dealer_country,
dealers.website dealer_website,
dealers.phone dealer_phone
FROM listings
LEFT JOIN dealers ON listings.dealer_id = dealers.id
LEFT JOIN vehicle_builds ON listings.vehicle_build_id = vehicle_builds.id
LEFT JOIN vehicle_build_interior_colors ON vehicle_build_interior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_interior_colors.install_cause IS NOT NULL
LEFT JOIN vehicle_build_exterior_colors ON vehicle_build_exterior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_exterior_colors.install_cause IS NOT NULL
WHERE
-- Only query current listings (nothing unlisted)
listings.unlisted_at IS NULL
-- Query on a specific style id (you could use ANDs on make, model, and trim fields as well)
-- Ex.: AND (model_year = 2020 AND make_name = 'BMW' AND model_name = 'X3 M' AND trim_name = 'Competition')
AND vehicle_builds.style_id = 406818
-- Looking for builds that DO NOT have a particular package by oem_code
AND listings.vehicle_build_id NOT IN (
SELECT vehicle_build_id FROM vehicle_build_factory_options
WHERE oem_code = '4FH' AND install_cause IS NOT NULL
)
/* RESULTS:
"build_id","vin","stock_no","price","state","dealer_name"
"85","5YMTS0C05L9B47668","W21580","82345","UT","Bmw Of Pleasant Grove"
*/
--
-- EXAMPLE 4:
-- Excluded option embedded in multile packages
--
SELECT
listings.vehicle_build_id build_id,
vehicle_builds.vin,
listings.stock_no,
listings.state,
vehicle_builds.style_id,
vehicle_builds.model_year,
vehicle_builds.make_name,
vehicle_builds.model_name,
vehicle_builds.trim_name,
vehicle_builds.body_type,
vehicle_builds.passenger_doors,
vehicle_builds.drivetrain,
vehicle_builds.price_msrp,
vehicle_builds.price_destination,
vehicle_builds.price_invoice,
vehicle_builds.built_at,
vehicle_builds.market_class_id,
vehicle_builds.market_class_name,
vehicle_build_exterior_colors.color_code,
vehicle_build_exterior_colors.color_name,
vehicle_build_interior_colors.color_code,
vehicle_build_interior_colors.color_name,
listings.price,
listings.odometer,
listings.days_on_market,
listings.days_on_market_active,
listings.mc_id,
dealers.name dealer_name,
dealers.street dealer_address,
dealers.city dealer_city,
dealers.state dealer_state,
dealers.zip dealer_zip,
dealers.country dealer_country,
dealers.website dealer_website,
dealers.phone dealer_phone
FROM listings
LEFT JOIN dealers ON listings.dealer_id = dealers.id
LEFT JOIN vehicle_builds ON listings.vehicle_build_id = vehicle_builds.id
LEFT JOIN vehicle_build_interior_colors ON vehicle_build_interior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_interior_colors.install_cause IS NOT NULL
LEFT JOIN vehicle_build_exterior_colors ON vehicle_build_exterior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_exterior_colors.install_cause IS NOT NULL
WHERE
-- Only query current listings (nothing unlisted)
listings.unlisted_at IS NULL
-- Query on a specific style id (you could use ANDs on make, model, and trim fields as well)
-- Ex.: AND (model_year = 2020 AND make_name = 'BMW' AND model_name = 'X3 M' AND trim_name = 'Competition')
AND vehicle_builds.style_id = 408729
-- Looking for buils that DO NOT have the selected packages by oem_code
AND listings.vehicle_build_id NOT IN (
SELECT DISTINCT vehicle_build_id FROM vehicle_build_factory_options
WHERE
oem_code IN ('ZCV','ZPP','ZPX')
AND install_cause IS NOT NULL
)
/* RESULTS:
"build_id","vin","stock_no","price","state","dealer_name"
"49","3MW5R7J00L8B22092","W21587","46300","UT","Bmw Of Murray"
*/
--
-- EXAMPLE 5:
-- Single option available a la carte or embedded in multiple packages
--
SELECT
listings.vehicle_build_id build_id,
vehicle_builds.vin,
listings.stock_no,
listings.state,
vehicle_builds.style_id,
vehicle_builds.model_year,
vehicle_builds.make_name,
vehicle_builds.model_name,
vehicle_builds.trim_name,
vehicle_builds.body_type,
vehicle_builds.passenger_doors,
vehicle_builds.drivetrain,
vehicle_builds.price_msrp,
vehicle_builds.price_destination,
vehicle_builds.price_invoice,
vehicle_builds.built_at,
vehicle_builds.market_class_id,
vehicle_builds.market_class_name,
vehicle_build_exterior_colors.color_code,
vehicle_build_exterior_colors.color_name,
vehicle_build_interior_colors.color_code,
vehicle_build_interior_colors.color_name,
listings.price,
listings.odometer,
listings.days_on_market,
listings.days_on_market_active,
listings.mc_id,
dealers.name dealer_name,
dealers.street dealer_address,
dealers.city dealer_city,
dealers.state dealer_state,
dealers.zip dealer_zip,
dealers.country dealer_country,
dealers.website dealer_website,
dealers.phone dealer_phone
FROM listings
LEFT JOIN dealers ON listings.dealer_id = dealers.id
LEFT JOIN vehicle_builds ON listings.vehicle_build_id = vehicle_builds.id
LEFT JOIN vehicle_build_interior_colors ON vehicle_build_interior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_interior_colors.install_cause IS NOT NULL
LEFT JOIN vehicle_build_exterior_colors ON vehicle_build_exterior_colors.vehicle_build_id = vehicle_builds.id AND vehicle_build_exterior_colors.install_cause IS NOT NULL
WHERE
-- Only query current listings (nothing unlisted)
listings.unlisted_at IS NULL
-- Query on a specific style id (you could use ANDs on make, model, and trim fields as well)
-- Ex.: AND (model_year = 2020 AND make_name = 'BMW' AND model_name = 'X3 M' AND trim_name = 'Competition')
AND vehicle_builds.style_id = 408729
-- Looking for builds that have active park control, which could be in
-- one of 3 packages (ZCV, ZPP or ZDA) or a-la-carte as oem_code 508
-- we verify the packages have the feature by fuzzy matching on the description for the fragment 'park distance'
AND listings.vehicle_build_id IN (
SELECT DISTINCT ON(vehicle_build_id)
vehicle_build_id
FROM
vehicle_build_factory_options
WHERE
oem_code IN ('508','ZCV','ZPP','ZDA')
AND description ILIKE '%park distance%'
AND install_cause IS NOT NULL
)
/* RESULTS:
"build_id","vin","stock_no","price","state","dealer_name"
"33","3MW5R7J03L8B22281","W21617","49995","UT","Bmw Of Murray"
"34","3MW5R7J05L8B22086","W21589","47995","UT","Bmw Of Murray"
"35","3MW5R7J09L8B19000","W21583","50460","UT","Bmw Of Murray"
"36","3MW5R7J09L8B22351","W21628","48545","UT","Bmw Of Murray"
"37","3MW5R7J00L8B31780","W21785","50385","UT","Bmw Of Murray"
"38","3MW5R7J08L8B31543","W21786","50935","UT","Bmw Of Murray"
"39","3MW5R7J0XL8B30426","W21798","49885","UT","Bmw Of Murray"
"40","3MW5R7J06L8B31802","W21799","56860","UT","Bmw Of Murray"
"49","3MW5R7J00L8B22092","W21587","46300","UT","Bmw Of Murray"
"50","3MW5R7J03L8B22233","W21616","54420","UT","Bmw Of Murray"
"51","3MW5R7J06L8B21917","W21591","51335","UT","Bmw Of Murray"
"80","3MW5R7J00L8B31181","W21827","51185","UT","Bmw Of Pleasant Grove"
"94","3MW5R7J00L8B31763","W21811","50635","UT","Bmw Of Murray"
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment