Skip to content

@alastaircoote /starts_and_ends.sql
Last active

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Messy SQL query to get start and end figures for turnstile data
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.