BxD Data Assessment

Question 1 - SQL

Using the data dictionary on the following page, please write a SQL query to get the top five most frequent felony charges for cases opened in the last three months.

“Charge1a” in the cases table is assumed to be the primary key to “charge” in the charge codes table. This is using the POSTGRES flavor of SQL.

WITH charges_freq AS ( 
    SELECT DISTINCT cc.label AS charge, 
           COUNT(*) AS frequency, 
           RANK() OVER(ORDER BY COUNT(*) DESC) AS ranking 
    FROM cases AS c 
    JOIN charge_codes AS cc 
        ON cc.charge = c.charge1a 
    WHERE c.open_date >= date_trunc('month', now()) - interval '3 month' AND 
          c.open_date < date_trunc('month', now()) AND 
          cc.charge_class= ‘felony’ 
    GROUP BY cc.label 
    ) 
SELECT charge, frequency 
FROM charges_freq 
WHERE ranking <=5 
ORDER BY ranking 
””” 

Question 2

• Please share two telling pieces of information in the form of data visualizations. These can be basic descriptive statistics or analysis of trends.

Let’s first look at some basic summary statistics on Commands and allegation types.

Command	FADO.Type	Allegation	freq
INT CIS	Force	physical force	744
WARRSEC	Abuse of Authority	premises entered and or searched	727
WARRSEC	Force	physical force	710
NARCBBX	Force	physical force	587
WARRSEC	Discourtesy	word	525
075 PCT	Force	physical force	514
INT CIS	Discourtesy	word	435
120 PCT	Force	physical force	433
044 PCT	Force	physical force	432
C R C	Force	physical force	390

Intelligence, warrants, narcotics in the Bronx are the top commands in allegations. Then the PCTs; 44 is in the Bronx, 75 is East New York, and 120 is Staten Island - Daniel Pantaleo was an officer in the 120 pct.

Let’s next link individuals within the commands.

Command	full_name	officer_count
MTS PCT	Robert Currie	173
JT T/F	Michael Elliott	159
ND NMI	John Coghlan	127
JT T/F	George Whalen	113
075 PCT	Robert Martinez	110
113 DET	Steven Deluca	109
PROPCLK	Glen Halfhide	109
WARRSEC	Ivan Montero	105
070 PCT	Dennis Ferber	104
JT T/F	Raymond Johnson	104

Culture of Misconduct

Which commands potentially have a culture of misconduct. Are there commands with a high number of officers who have many complaints?

We will try to determine which commands have very high offending officers relative to the mean number of complaints in that command. The proxy association here is that if a commnad has a high average number of complaints and several officers well above that average, then perhaps the command is taking an indifferent or defiant stance on civilan complaints.

One Hundred or More Complaints.

There are around a dozen officers with more than one hundred complaints. Let’s explore those complaint histories.

There seems to be two categories of high offender. The “peak” offender with (typically) one peak event, and a consistent offender with long history of offenses. It seems that all but one of 100+ complaint officers who have very high peaks tend to have shorter complaint periods. As in, they only have complaints stretching over less than 10 years. The consistent offenders tend to have 20+ years of 3-10 complaints per year, which is troubling.

• What are some of the limitations of this data? Are there any data points that you would want added to the dataset to provide more relevant details?

I think the biggest limitation is that we only have the complaints and not all officer interactions. As such, we cannot directly know from this data the rate at which a given officer commits a complaint worthy action. It also would be great to have more data that gives context to the officer-civilian interaction. Did a given complaint come from a traffic stop, a four-year investigation that lead to a warrant, or a warrant for failure to appear regarding a minor summons?