queries.yaml

report-findings-bullet-3:
  sql: SELECT
        CALL_FOR_SERVICE as call_for_service,
        COUNT(*) as N,
        PRINTF('%.4f', COUNT(*) * 1.0 / (SELECT COUNT(*) FROM aa_main_table)) as share
      FROM
        aa_main_table
      GROUP BY
        CALL_FOR_SERVICE
  title: "(2020 annual report) Findings regarding RIPA Stop Data Bullet Point 3"
  description_html: "<b>95.3 percent of stops were officer-initiated</b>, while 4.7 percent of stops were in response to a call for service, radio call, or dispatch."

race-ethnicity-by-agency:
  sql: SELECT
        b.AGENCY_NAME as agency_name,
        rae_codes.RACE_ETHNICITY as race_ethnicity,
        COUNT(rae.RAE_FULL) as N
      FROM
        race_ethnicity as rae
      INNER JOIN aa_main_table as b ON b.UNIQUE_ID = rae.UNIQUE_ID
      INNER JOIN race_ethnicity_codes as rae_codes ON rae.RAE_FULL = rae_codes.CODE
      GROUP BY
        agency_name,
        rae.RAE_FULL
  title: "Race / Ethnicity by Agency Name"
  description_html: "Stops by race/ethnicity by agency."

race-ethnicity-by-reason-for-stop:
  sql: SELECT
        rae_codes.RACE_ETHNICITY as race_ethnicity,
        rfs_codes.REASON_FOR_STOP as reason_for_stop,
        COUNT(main.REASON_FOR_STOP) as total_stops
      FROM
        aa_main_table as main
        INNER JOIN race_ethnicity as rae ON rae.UNIQUE_ID = main.UNIQUE_ID
        INNER JOIN race_ethnicity_codes as rae_codes ON rae.RAE_FULL = rae_codes.CODE
        INNER JOIN reason_for_stop_codes as rfs_codes ON main.REASON_FOR_STOP = rfs_codes.CODE
      GROUP BY
        main.REASON_FOR_STOP,
        rae.RAE_FULL
      ORDER BY
        rae_codes.RACE_ETHNICITY
  title: "Reason for Stop by Race / Ethnicity "
  description_html: "A higher percentage of Black individuals were stopped for reasonable suspicion than any other racial identity group."

consent-by-race-ethnicity:
  sql: SELECT
        rae_codes.RACE_ETHNICITY as race_ethnicity,
        SUM(ADS_ASKED_SEARCH_PER) as asked_person_for_consent,
        SUM(ADS_SEARCH_PERS_CONSEN) as person_consented_to_search,
        SUM(ADS_SEARCH_PERSON) as searched_person,
        SUM(bfs.BFS_CONSENT_GIVEN) as bfs_consent_given,
        COUNT(main.REASON_FOR_STOP) as rfs_consentual_encounter_consentual_search
      FROM
        action_taken AS at
      INNER JOIN 
        race_ethnicity AS rae ON rae.UNIQUE_ID = at.UNIQUE_ID
      INNER JOIN
        basis_for_search AS bfs ON bfs.UNIQUE_ID = rae.UNIQUE_ID
      INNER JOIN
        race_ethnicity_codes AS rae_codes ON rae.RAE_FULL = rae_codes.CODE
      LEFT JOIN
        (SELECT UNIQUE_ID, REASON_FOR_STOP FROM aa_main_table WHERE REASON_FOR_STOP = 6) AS main ON main.UNIQUE_ID = rae.UNIQUE_ID
      GROUP BY
        rae.RAE_FULL
  title: "Variables related to consent in searches"
  description_html: "According to RIPA stop data regulations: <br> <b>Consensual search</b> is a search that occurs when a person gives a peace officer consent or permission to search the person or the person’s property. Consent can be given in writing or verbally, or may be implied by conduct. <br> <b>Consensual encounter resulting in a search</b>: A consensual encounter is an interaction in which the officer does not exert any authority over, or use any force on, a person, and the person is free to leave. The officer shall only select this data value if a consensual encounter results in a search, regardless of whether the resulting search is consensual."

reason-for-stop-by-race-by-agency:
  sql: SELECT
        rae_codes.RACE_ETHNICITY,
        rfs_codes.REASON_FOR_STOP,
        COUNT(*) as N
      FROM
        aa_main_table as main
      JOIN race_ethnicity as rae
        ON rae.UNIQUE_ID = main.UNIQUE_ID
      JOIN race_ethnicity_codes as rae_codes
        ON rae.RAE_FULL = rae_codes.CODE
      JOIN reason_for_stop_codes as rfs_codes
        on main.REASON_FOR_STOP = rfs_codes.CODE
      WHERE
        "AGENCY_NAME" = :agency
      GROUP BY
        main.REASON_FOR_STOP, rae.RAE_FULL
      ORDER BY
        RACE_ETHNICITY, N DESC
  title: "Reason for stop by race/ethinicty by agency"
  description_html: '<p>AGENCY_NAME OPTIONS:</p><ul><li><a href="?agency=CHP-HQ+A%2FC%2C+STAFF">CHP-HQ A/C, STAFF</a></li><li><a href="?agency=LAPD">LAPD</a></li><li><a href="?agency=LOS+ANGELES+CO+SD">LOS ANGELES CO SD</a></li><li><a href="?agency=SAN+DIEGO+PD">SAN DIEGO PD</a></li><li><a href="?agency=SAN+BERNARDINO+CO+SO">SAN BERNARDINO CO SO</a></li><li><a href="?agency=SAN+FRANCISCO+PD+-+DOC">SAN FRANCISCO PD - DOC</a></li><li><a href="?agency=RIVERSIDE+CO+SO">RIVERSIDE CO SO</a></li><li><a href="?agency=SAN+DIEGO+CO+SO">SAN DIEGO CO SO</a></li></ul>'

veil-of-darkness-analysis-by-agency:
  sql: 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 main.REASON_FOR_STOP = 1
          )
        WHERE
          AGENCY_NAME = :agency
        GROUP BY
          AGENCY_NAME,
          RACE_ETHNICITY,
          IN_TWILIGHT
  title: "Veil of darkness (VOD) analysis by agency"
  description_html: '<p>This includes only traffic violation stops for consistency with RIPA 2020 annual report</p><p>AGENCY_NAME OPTIONS:</p><ul><li><a href="?agency=CHP-HQ+A%2FC%2C+STAFF">CHP-HQ A/C, STAFF</a></li><li><a href="?agency=LAPD">LAPD</a></li><li><a href="?agency=LOS+ANGELES+CO+SD">LOS ANGELES CO SD</a></li><li><a href="?agency=SAN+DIEGO+PD">SAN DIEGO PD</a></li><li><a href="?agency=SAN+BERNARDINO+CO+SO">SAN BERNARDINO CO SO</a></li><li><a href="?agency=SAN+FRANCISCO+PD+-+DOC">SAN FRANCISCO PD - DOC</a></li><li><a href="?agency=RIVERSIDE+CO+SO">RIVERSIDE CO SO</a></li><li><a href="?agency=SAN+DIEGO+CO+SO">SAN DIEGO CO SO</a></li></ul>'