Query suggestion

[chekos]

stops by race/ethnicity by whether or not within civil twilight

select one agency at a time only tho 💯

query

SELECT
  IN_TWILIGHT,
  RACE_ETHNICITY,
  COUNT(*) as N
FROM
  (
    SELECT
      main.UNIQUE_ID,
      main.AGENCY_NAME,
      main.DATE_OF_STOP,
      main.CLOSEST_CITY,
      racen_codes.RACE_ETHNICITY,
      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
      INNER JOIN race_ethnicity as racen ON racen.UNIQUE_ID = main.UNIQUE_ID
      INNER JOIN race_ethnicity_codes as racen_codes ON racen_codes.CODE = racen.RAE_FULL
  )
WHERE
  AGENCY_NAME = :agency
GROUP BY
  AGENCY_NAME,
  RACE_ETHNICITY,
  IN_TWILIGHT