Last active
March 30, 2020 21:57
-
-
Save datchley/1c66e054c9c89261a7d7f14efbad2698 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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' | |
| ; | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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