search

chekos / RIPA-2018-datasette

Publishing datasette of CA's RIPA 2018 data
http://ripa-2018.datasettes.cimarron.io/
GNU General Public License v3.0
0 stars 0 forks source link

Query suggestion (consent searches) #25

Closed chekos closed 4 years ago

chekos commented 4 years ago

YIKES

query

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,
-- claimed basis for search is that consent was given
  SUM(bfs.BFS_CONSENT_GIVEN) as bfs_consent_given,
-- claimed consentual encounter resulted in consentual search
  SUM(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