Query suggestion

[chekos]

Stops within civil twilight by agency

query

SELECT
  AGENCY_NAME,
  IN_TWILIGHT,
  COUNT(*) as N
FROM
  (
    SELECT
      main.UNIQUE_ID,
      main.AGENCY_NAME,
      main.DATE_OF_STOP,
      main.CLOSEST_CITY,
      TIME_OF_STOP,
      twilight.CTWILIGHT_BEGIN as twilight_begin,
      twilight.CTWILIGHT_END as twilight_end,
      CASE
        WHEN TIME_OF_STOP > twilight.CTWILIGHT_BEGIN
        AND TIME_OF_STOP < twilight.CTWILIGHT_END THEN "Within civil twilight"
        ELSE "in the dark..."
      END as IN_TWILIGHT
    FROM
      aa_main_table as main
      INNER JOIN civil_twilight_hours as twilight ON twilight.DATE_OF_STOP = main.DATE_OF_STOP
      AND twilight.CLOSEST_CITY = main.CLOSEST_CITY
  )
GROUP BY 
  AGENCY_NAME, IN_TWILIGHT

[chekos]

Closed with #31 too