Skip to content

Instantly share code, notes, and snippets.

@alastaircoote
Last active December 17, 2015 23:09
Show Gist options
  • Save alastaircoote/5687130 to your computer and use it in GitHub Desktop.
Save alastaircoote/5687130 to your computer and use it in GitHub Desktop.
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