Skip to content

Instantly share code, notes, and snippets.

@kmarenov

kmarenov/gist:b99a2b5a9a5db5d8076b0cdbc18ba185

Last active September 17, 2017 19:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kmarenov/b99a2b5a9a5db5d8076b0cdbc18ba185 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kmarenov/b99a2b5a9a5db5d8076b0cdbc18ba185 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
SELECT
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price IS NULL) AS november_2016_null,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price < 1000000 AND vg.price IS NOT NULL) AS november_2016_to_1M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price >= 1000000 AND vg.price < 3000000 AND vg.price IS NOT NULL) AS november_2016_from_1M_to_3M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price >= 3000000 AND vg.price < 5000000 AND vg.price IS NOT NULL) AS november_2016_from_3M_to_5M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price >= 5000000 AND vg.price < 8000000 AND vg.price IS NOT NULL) AS november_2016_from_5M_to_8M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2016-11-01' AND ce.timestamp <= '2016-12-01' AND vg.price >= 8000000 AND vg.price IS NOT NULL) AS november_2016_from_8M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price IS NULL) AS june_2017_null,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price < 1000000 AND vg.price IS NOT NULL) AS june_2017_to_1M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price >= 1000000 AND vg.price < 3000000 AND vg.price IS NOT NULL) AS june_2017_from_1M_to_3M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price >= 3000000 AND vg.price < 5000000 AND vg.price IS NOT NULL) AS june_2017_from_3M_to_5M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price >= 5000000 AND vg.price < 8000000 AND vg.price IS NOT NULL) AS june_2017_from_5M_to_8M,
COUNT(DISTINCT v.id) FILTER (WHERE ce.timestamp >= '2017-06-01' AND ce.timestamp <= '2017-07-01' AND vg.price >= 8000000 AND vg.price IS NOT NULL) AS june_2017_from_8M
FROM radar2.cam_event ce
LEFT JOIN radar2.vehicle v ON ce."vehicleId" = v.id
LEFT JOIN radar2.vehicle_generation vg ON vg.id = v."generationId"
LEFT JOIN radar2.cam_event2slot ces ON ce.id = ces."camEventId"
LEFT JOIN radar2.slot s ON ces."slotId" = s.id
WHERE s."locationId" = 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment