Last active

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

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

View starts_and_ends.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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.