Officer ranking by stop/search disparity

[shapiromatron]

New django-list view. For each agency, rank officers by the total stops, the total searches, and the % of searches stop. Dylan has a picture, will attach.

The page should be a table. Officers IDs are the rows, with a hyperlink to each officer ID page for more details. There are three columns (in addition to officer ID):

• Total stop count
• Total search count
• Stop/search count

There are also possible filters at the top. A user can select a specific year, which will filter the data for that year.

A user can also select a race/ethnicity comparison. If a comparison is selected, then there will be 6 columns (the columns above x2 plus the officer ID).

However, how would one then rank given the race/ethnicity comparison? Need some feedback to figure-out how...

[Dylanotron]

[Dylanotron]

There are two ways we can show the Ranking of Racial Disparity by officer, agency, and year

1. Take the difference between black and white of the number of drivers searched over the number of drivers stopped
   a. for each year, race, agency, officer
   b. For the app, just default to the ratio. The user will then select the comparison
2. Take the difference between black and white as a percentage of the whole of the number of drivers searched
   a. group by year, race, agency, officer

The SQL implementation for the first method is as follows:

-- total search stops, corresponds to denominator in "Departmental Search Rate"; this is the stops query plus officer
select count(person_id), p.race, extract(year from s.date) as year, s.officer_id from stops_person p 
join stops_stop s on p.stop_id = s.stop_id 
where 
p.type='D' and
s.agency_id = 78
group by p.race, year, s.officer_id
order by year asc, s.officer_id asc, p.race desc;

-- search count, corresponds to numerator in "Departmental Search Rate"; this is the searches query, plus officer
select count(se.person_id), p.race, extract(year from s.date) as year, s.officer_id from stops_person p 
join stops_stop s on p.stop_id = s.stop_id 
join stops_search se on s.stop_id = se.stop_id
join stops_agency a on s.agency_id = a.id
where 
p.type='D' and
a.id = 78
group by p.race, year, s.officer_id
order by year asc, s.officer_id asc, p.race desc;

The app would then do the same thing we do in the "Departmental Search Rate" by running these two queries and getting a rate of searches over stops per year/race/officer/agency.

The next step would involve sorting by the officer_id s that have the highest rate. We will want to default to a total highest search rate, so that will need to be included in both of these queries.

More discussion to follow if we want to implement method #2, which is a distinctively different method from 1.