SELECT
race_ethnicity,
PRINTF('%.2f', person_consented_to_search*1.0 / searched_person) as share_consented,
PRINTF('%.2f', bfs_consent_given*1.0 / searched_person) as bfs_share_consented,
PRINTF('%.2f', bfs_consent_given*1.0 / searched_person - person_consented_to_search*1.0 / searched_person) as difference_in_consents,
searched_person
FROM
(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)
Difference between Action taken by officer during stop: specify if consent was given for search of person and Basis for search: consent given by race/ethnicity
🤔🤔🤔🤔🤔🤔
query