public
Last active

Messy SQL query to get start and end figures for turnstile data

  • Download Gist
starts_and_ends.sql
SQL
1 2 3 4 5 6 7 8 9 10 11
SELECT d.unit, d.scp, d.dt,
(SELECT s.dt FROM stats s WHERE s."desc" = 'REGULAR' AND s.unit = d.unit AND date(s.dt) = d.dt AND s.scp = d.scp AND EXTRACT(hour from s.dt) >= 3 ORDER BY s.dt LIMIT 1) AS start_time,
(SELECT s.exits FROM stats s WHERE s."desc" = 'REGULAR' AND s.unit = d.unit AND date(s.dt) = d.dt AND s.scp = d.scp AND EXTRACT(hour from s.dt) >= 3 ORDER BY s.dt LIMIT 1) AS start_exits,
(SELECT s.dt FROM stats s WHERE s."desc" = 'REGULAR' AND s.unit = d.unit AND date(s.dt) = d.dt AND s.scp = d.scp AND EXTRACT(hour from s.dt) >= 11 ORDER BY s.dt LIMIT 1) AS midday_time,
(SELECT s.exits FROM stats s WHERE s."desc" = 'REGULAR' AND s.unit = d.unit AND date(s.dt) = d.dt AND s.scp = d.scp AND EXTRACT(hour from s.dt) >= 11 ORDER BY s.dt LIMIT 1) AS midday_exits
FROM
 
(SELECT DISTINCT stats.unit, stats.scp, date(stats.dt) AS dt FROM stats)
 
as d
WHERE NOT EXTRACT(dow from d.dt) IN (0,6)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.