Resource Allocation Report with SQL

Write production SQL on 7M+ rows to answer real public safety business questions.

Introduction

30 Second Summary

Every city faces the same question: where should we send resources to keep people safe? The answer lives in data, but only if someone knows how to ask the right questions and translate millions of records into clear recommendations.

In this project, you will use SQL to analyze 7+ million rows of real Chicago crime data and build a resource allocation report that answers specific leadership questions about where to increase patrols, which crime types need different strategies, and which districts are improving.

What You'll Build

A business analysis notebook on Kaggle that queries BigQuery's Chicago Crime dataset to produce a stakeholder-ready resource allocation report with data-backed recommendations.

By the end of this project, you'll have:

  • Defined and calculated KPIs (incident volume, arrest rate, domestic ratio, year-over-year change) that measure public safety performance across districts.
  • Built a District Scorecard using CTEs, window functions, and self-joins that ranks every district and labels them for resource action.
  • Delivered a Budget Meeting Summary query and written recommendation that a non-technical stakeholder could read and act on.
  • Secret Mission: Build a seasonal staffing model that recommends which months and shifts each district should increase or decrease patrol coverage.

Are there any prerequisites?

You need a free Kaggle account. If you do not have one yet, you will create it in Step 1.

Some programming experience is helpful, but you do not need prior SQL knowledge. Each query is explained as you build it.

Set Up Your Kaggle BigQuery Environment

Before you can analyze Chicago's crime data, you need a workspace that connects directly to the dataset. The data lives in Google BigQuery, a cloud data warehouse that holds over 7 million rows of real crime records.

Kaggle Notebooks give you a browser-based Python environment with BigQuery already integrated. No local installs, no billing setup, no credentials to manage. You will create a notebook, attach the dataset, and confirm your connection in minutes.

In this step, get ready to:
  • Create a new Kaggle notebook for your report.
  • Add the Chicago Crime BigQuery dataset.
  • Verify your connection and inspect the data schema.
Create a new Kaggle notebook

Kaggle Notebooks are browser-based coding environments built on Jupyter. Each notebook consists of cells where you write and run Python code or Markdown text.

  • Open kaggle.com and sign in to your account.
  • Click Code in the left sidebar.
  • Click New Notebook near the top of the page.
  • A new notebook opens in the editor. Click the notebook title at the top of the page to edit it.
  • Replace the default title with Public Safety Resource Allocation Report and press Enter to confirm.

Why name the notebook now?

Business analysts name deliverables clearly from the start. This notebook will become a publishable portfolio piece, so giving it a professional title now saves you from remembering to do it later.

Add the BigQuery dataset

Attaching a BigQuery dataset to your notebook tells Kaggle to authenticate your session automatically. Without this step, your Python code cannot reach the data.

  • In the right-hand sidebar of the notebook editor, find the Input section.
  • Click Add Input.
  • In the search box that appears, type chicago_crime.
  • Look for the BigQuery dataset from bigquery-public-data and click it to add it to your notebook.

What does adding the dataset do?

When you add a BigQuery dataset through the Input pane, Kaggle handles authentication behind the scenes. The google-cloud-bigquery Python library is already pre-installed in Kaggle's environment, so you do not need to run any pip install commands.

You also get a free quota of 5 TB of data scanned per 30-day period. This project uses well under 1 GB total, so quota is not a concern.

Verify the connection and inspect the schema

Now you will write your first code cells to confirm that your notebook can reach the Chicago Crime dataset and explore what columns are available.

  • Delete any placeholder code in the first cell of your notebook.
  • Paste the following code into the first cell to connect to BigQuery and list available tables:
# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)

What does this code do?

  • from google.cloud import bigquery imports the BigQuery Python client library that Kaggle pre-installs.
  • bigquery.Client() creates a client instance. Kaggle injects credentials automatically because you attached the dataset.
  • client.dataset("chicago_crime", project="bigquery-public-data") points to the public Chicago Crime dataset hosted by Google.
  • The for loop prints every table name in that dataset so you can confirm access.
  • Run the cell by pressing Shift+Enter.
  • You should see crime printed as a table name in the output below the cell.

Not seeing any output?

  • Make sure you added the chicago_crime BigQuery dataset through the Input pane in the sidebar. Without it, Kaggle cannot authenticate your session.
  • Check that you selected the dataset from bigquery-public-data, not a community-uploaded CSV version.
  • If you see a permission error, try removing the dataset from the Input pane and re-adding it.

Why can't my notebook connect to BigQuery?

Now that you confirmed access, you need to understand what data is available. The schema tells you which columns exist and what types they hold.

  • Click the + Code button below your first cell to add a new code cell.
  • Paste the following code to inspect the schema of the crime table:
# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")

What does this code do?

  • dataset_ref.table("crime") gets a reference to the specific crime table within the chicago_crime dataset.
  • client.get_table(table_ref) fetches the table metadata including row count and schema.
  • The f-string prints the total row count (formatted with commas for readability) and the number of columns.
  • The loop prints each column's name, data type, and mode (whether it allows NULL values).
  • Run this cell by pressing Shift+Enter.
  • You should see a list of columns including primary_type, date, district, arrest, domestic, and location_description.

Seeing a 'not found' error?

  • Confirm that your first cell ran successfully and printed crime. The second cell depends on the dataset_ref variable created in Cell 1.
  • Make sure you ran the cells in order. In Kaggle Notebooks, cells must execute sequentially so variables from earlier cells are available.

Help me fix a 'not found' error in my schema inspection cell.

Key columns for your report

Here are the columns you will use most in this project and the business questions they answer:

  • primary_type (STRING): What categories of crime occur most? Where should resources focus?
  • date (TIMESTAMP): Are crimes increasing or decreasing over time? When do they peak?
  • district (INTEGER): Which geographic areas need more patrols?
  • arrest (BOOLEAN): How effective is enforcement? What is the arrest rate?
  • domestic (BOOLEAN): What proportion of incidents are domestic? Does this vary by district?
  • location_description (STRING): Where do crimes happen? Are some locations better policed than others?

✔️ Awesome, I've got everything!

Great. Make sure both cells ran without errors and you can see the schema output.

ⓧ I'd like to double check the full code

Here is the complete code across both cells. Compare it with what you have in your notebook:

# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)


# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")

Your environment is connected and you understand the shape of your data. Next up, you will define the KPIs that matter for resource allocation and write your first analytical queries.

Define KPIs and Establish the Baseline

Your environment is connected and you know the shape of your data. Now it's time to answer your first stakeholder question.

Imagine the Operations Director sits down across from you and says: "Give me a high-level picture of our current situation. What are the main crime categories we deal with, how effective are we at making arrests, and what does the overall volume look like?" Every business analysis starts by establishing a baseline. You need numbers to compare against before you can recommend changes.

In this step, get ready to:
  • Query the dataset scope and rank crime categories by volume.
  • Calculate core KPIs (arrest rate, domestic incident ratio) for each crime type.
  • Write an executive summary translating query results into business insights.
Write the current state summary

Every BA report opens with a "current state summary" that gives stakeholders the lay of the land. You need to know how much data you're working with and which crime categories dominate the picture.

  • Create a new code cell in your notebook below the schema inspection cell.
  • Paste the following code to query the dataset scope and top crime categories:
# Cell 3: Current State Summary - Dataset Scope and Top Categories
sql = """
SELECT
    COUNT(*) AS total_records,
    MIN(date) AS earliest_record,
    MAX(date) AS latest_record,
    COUNT(DISTINCT primary_type) AS crime_categories,
    COUNT(DISTINCT district) AS total_districts
FROM `bigquery-public-data.chicago_crime.crime`
"""
df = client.query(sql).to_dataframe()
print("=== DATASET SCOPE ===")
print(df)

sql = """
SELECT
    primary_type,
    COUNT(*) AS incident_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
ORDER BY incident_count DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("\n=== TOP 10 CRIME CATEGORIES ===")
print(df)

What does this code do?

The first query establishes scope. It counts total records, finds the earliest and latest dates, and counts distinct crime types and districts. This tells the Director exactly how much data backs your analysis.

The second query ranks crime categories by volume. GROUP BY groups all records by primary_type, ORDER BY incident_count DESC sorts from highest to lowest, and SUM(COUNT(*)) OVER () is a window function that calculates each category's percentage of all crime.

  • Run the cell by pressing **Shift+Enter**.
  • You should see a dataset scope table showing 7+ million total records, followed by a table of the top 10 crime categories with their incident counts and percentage of total.

Seeing a query error?

Check that your table path uses backticks around the fully qualified name: `bigquery-public-data.chicago_crime.crime`. Regular quotes will not work for BigQuery table references.

Also confirm that you added the Chicago Crime dataset in Step 1. Without it, the Kaggle notebook won't have authentication to query BigQuery.

Why is my BigQuery query failing in Kaggle?

Calculate core KPIs by crime type

A KPI (Key Performance Indicator) is a measurable value that tells stakeholders whether things are going well or poorly. For public safety, you need three KPIs: incident volume (how much crime), arrest rate (how effective are responses), and domestic incident ratio (what proportion involves domestic situations).

  • Create a new code cell below your current state summary.
  • Paste the following query to calculate KPIs for each crime type:
# Cell 4: Core KPI Calculation by Crime Type
sql = """
SELECT
    primary_type,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
HAVING COUNT(*) > 5000
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== KPI BASELINE: ARREST RATE AND DOMESTIC RATE BY CRIME TYPE ===")
print(df)

What does this code do?

  • AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) converts the boolean arrest column into a percentage. Each TRUE becomes 1.0, each FALSE becomes 0.0, and the average gives you the arrest rate.
  • HAVING COUNT(*) > 5000 filters out crime types with too few records. Without it, categories with 10 incidents could show 100% arrest rates, which is misleading for resource decisions.
  • The same CASE/AVG pattern calculates domestic_rate_pct from the domestic boolean. This tells the Director which crime types are disproportionately domestic situations, which may require different intervention strategies.

Why CASE inside AVG instead of COUNT/SUM?

The CASE-inside-AVG pattern is the standard way to calculate a percentage from a boolean column in SQL. You could also write COUNTIF(arrest = TRUE) * 100.0 / COUNT(*) in BigQuery (which supports COUNTIF), but the CASE/AVG pattern works across all SQL dialects. It is the version you'll see most often in interview questions.

  • Run the cell by pressing **Shift+Enter**.
  • You should see a table listing each crime type alongside its total incidents, arrest rate percentage, and domestic rate percentage. Notice how arrest rates vary dramatically between categories.

Getting empty results?

If your output is empty, check the HAVING threshold. A value of 5000 should return many rows. If you accidentally typed 5000000, you would filter out everything.

Help me debug my BigQuery KPI query

Write the executive summary

A business analyst delivers insights, not just tables. The Director asked a question. Your job is to answer it in plain language, backed by the numbers you just generated.

  • Click the **+ Markdown** button in the notebook toolbar to add a new markdown cell below your KPI query.
  • Write a brief summary that answers the Director's question using numbers from your query results. Include the top 3 crime categories by volume and note which categories have surprisingly high or low arrest rates.

Your summary should read something like this (using the actual numbers from your output):

## Executive Summary: Current State

Our dataset covers [X] million records spanning [date range]. The top 3 crime
categories by volume are [category 1], [category 2], and [category 3],
accounting for [X]% of all incidents combined.

Arrest rates vary significantly: narcotics has the highest arrest rate at
[X]%, while theft has the lowest at [X]%. This suggests different crime types
require fundamentally different resource strategies.

Why write markdown between queries?

In a real BA role, your notebook IS the deliverable. Stakeholders read your markdown cells to understand what the data means. The SQL proves your claims, but the markdown communicates the insight. Practice this habit now because interviewers look for it in take-home assignments.

  • Replace the placeholder brackets with actual numbers from your Cell 3 and Cell 4 output.
  • Run the markdown cell by pressing **Shift+Enter**. You should see formatted text with a heading and two paragraphs of insight.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook (Kaggle auto-saves, but you can also press **Ctrl+S**).

ⓧ I'd like to double check the full code

Here is the full code your notebook should contain at this point. Your markdown cell content will vary based on your actual query results.

# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)


# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")


# Cell 3: Current State Summary - Dataset Scope and Top Categories
sql = """
SELECT
    COUNT(*) AS total_records,
    MIN(date) AS earliest_record,
    MAX(date) AS latest_record,
    COUNT(DISTINCT primary_type) AS crime_categories,
    COUNT(DISTINCT district) AS total_districts
FROM `bigquery-public-data.chicago_crime.crime`
"""
df = client.query(sql).to_dataframe()
print("=== DATASET SCOPE ===")
print(df)

sql = """
SELECT
    primary_type,
    COUNT(*) AS incident_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
ORDER BY incident_count DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("\n=== TOP 10 CRIME CATEGORIES ===")
print(df)


# Cell 4: Core KPI Calculation by Crime Type
sql = """
SELECT
    primary_type,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
HAVING COUNT(*) > 5000
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== KPI BASELINE: ARREST RATE AND DOMESTIC RATE BY CRIME TYPE ===")
print(df)

You've established a solid baseline. The Director now has a clear picture of overall crime volume, the dominant categories, and how arrest effectiveness varies across them. Next up, you'll use this baseline to answer a harder question: which districts are getting worse and which are improving.

Answer the Resource Allocation Question

Your baseline is set. The Director now has a clear picture of crime categories and arrest effectiveness across the city.

Now comes the question that drives budget decisions: which districts are getting worse, and which are improving? To answer this, you need to compare each district's performance across two time periods. This is where self-joins and window functions earn their keep in business analysis.

In this step, get ready to:
  • Compare district performance year-over-year using CTEs and a self-join.
  • Build a District Performance Scorecard that ranks and labels each district.
  • Write a resource allocation recommendation for leadership.
Compare district performance between 2022 and 2023

The Director wants to justify reallocating patrol hours in next quarter's budget. To do that, you need hard numbers showing which districts are trending worse and which are improving.

A self-join lets you compare the same table against itself across different time periods. You will create two CTEs (one for 2022, one for 2023) and then join them on the district column.

  • Create a new code cell in your notebook and paste the following query:
# Cell 5: District Year-over-Year Comparison (Self-Join)
sql = """
WITH district_2022 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
district_2023 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
)
SELECT
    d22.district,
    d22.total_crimes AS crimes_2022,
    d23.total_crimes AS crimes_2023,
    d23.total_crimes - d22.total_crimes AS crime_change,
    ROUND((d23.total_crimes - d22.total_crimes) * 100.0 / d22.total_crimes, 1) AS crime_pct_change,
    d22.arrest_rate_pct AS arrest_rate_2022,
    d23.arrest_rate_pct AS arrest_rate_2023,
    ROUND(d23.arrest_rate_pct - d22.arrest_rate_pct, 1) AS arrest_rate_change
FROM district_2022 d22
JOIN district_2023 d23
    ON d22.district = d23.district
ORDER BY crime_pct_change DESC
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT YEAR-OVER-YEAR COMPARISON ===")
print(df)

What does this query do?

This query uses two CTEs to snapshot each district's stats for a single year, then joins them together on the district column.

  • district_2022 aggregates total crimes, arrest rate, and domestic rate for each district in 2022.
  • district_2023 does the same for 2023.
  • The final SELECT joins both CTEs and calculates the difference. crime_pct_change shows whether crime went up or down as a percentage, while arrest_rate_change shows whether arrests improved.
  • AND district IS NOT NULL filters out records with missing district data, which would otherwise produce NULL rows in your results.

Why a self-join instead of subqueries?

A self-join (joining a table to itself) is a pattern you will see frequently in data analyst interviews. It cleanly answers before/after questions without nesting subqueries inside your SELECT clause.

Each CTE produces a clean one-row-per-district summary. The JOIN then lines them up side by side so you can subtract and compare directly.

  • Run the cell by pressing Shift+Enter.
  • You should see a table with one row per district, showing crimes_2022, crimes_2023, crime_change, crime_pct_change, and arrest rate columns. Districts with the largest crime increases appear at the top.

Getting an error about NULL values?

  • Make sure both CTEs include AND district IS NOT NULL in their WHERE clause. Without this filter, NULL district values create join mismatches.
  • Check that your JOIN condition is ON d22.district = d23.district. If you accidentally use a different alias, BigQuery cannot match rows.

Help me debug my year-over-year comparison query

Build the District Performance Scorecard

Raw numbers show change, but leadership needs a prioritized list. A scorecard assigns each district a composite score and a clear label so the Director can walk into the budget meeting with a ranked recommendation.

  • Create a new code cell and paste this scorecard query:
# Cell 6: District Performance Scorecard
sql = """
WITH district_stats AS (
    SELECT
        district,
        EXTRACT(YEAR FROM date) AS year,
        COUNT(*) AS total_crimes,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate,
        AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) AS domestic_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) IN (2022, 2023)
        AND district IS NOT NULL
    GROUP BY district, year
),
changes AS (
    SELECT
        district,
        year,
        total_crimes,
        arrest_rate,
        LAG(total_crimes) OVER (PARTITION BY district ORDER BY year) AS prev_crimes,
        LAG(arrest_rate) OVER (PARTITION BY district ORDER BY year) AS prev_arrest_rate
    FROM district_stats
),
scored AS (
    SELECT
        district,
        total_crimes AS crimes_2023,
        ROUND(SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) * 100, 1) AS crime_change_pct,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND((arrest_rate - prev_arrest_rate) * 100, 1) AS arrest_rate_change_pts,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) > 0.05
                AND (arrest_rate - prev_arrest_rate) < 0 THEN 'Needs Resources'
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.03
                OR (arrest_rate - prev_arrest_rate) > 0.02 THEN 'Improving'
            ELSE 'Stable'
        END AS status,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.05 THEN 2
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < 0 THEN 1
            ELSE 0
        END +
        CASE
            WHEN arrest_rate - prev_arrest_rate > 0.02 THEN 2
            WHEN arrest_rate - prev_arrest_rate > 0 THEN 1
            ELSE 0
        END AS performance_score
    FROM changes
    WHERE year = 2023 AND prev_crimes IS NOT NULL
)
SELECT
    district,
    crimes_2023,
    crime_change_pct,
    arrest_rate_pct,
    arrest_rate_change_pts,
    status,
    performance_score,
    RANK() OVER (ORDER BY performance_score DESC, crime_change_pct ASC) AS performance_rank
FROM scored
ORDER BY performance_rank
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT PERFORMANCE SCORECARD ===")
print(df)

What does this scorecard query do?

This query uses three nested CTEs to build the scorecard layer by layer:

  • district_stats groups crimes by district and year, keeping both 2022 and 2023 in one table.
  • changes uses LAG() to pull the previous year's values into the same row. This avoids a self-join by using a window function instead.
  • scored calculates the percentage change, assigns a status label with CASE logic, and builds a composite performance_score by awarding points for crime reduction and arrest improvement.
  • The final SELECT uses RANK() to order districts from strongest to weakest performance.

SAFE_DIVIDE is a BigQuery function that returns NULL instead of throwing an error when dividing by zero. This protects against edge cases where a district had zero crimes in 2022.

Why LAG instead of another self-join?

In Cell 5, you used a self-join to compare years. Here, LAG() achieves the same result more concisely by looking at the previous row within each district's partition. Both patterns are valid. Using both in one report shows interviewers you know multiple approaches.

  • Run the cell by pressing Shift+Enter.
  • You should see a ranked table where each district has a status label (Needs Resources, Improving, or Stable), a performance_score, and a performance_rank. The top-performing districts appear first.

Seeing NULL values in your results?

  • The WHERE year = 2023 AND prev_crimes IS NOT NULL clause in the scored CTE filters out 2022 rows (which have no previous year). Make sure this line is present.
  • If crime_change_pct shows NULL for some districts, those districts may have had zero crimes in the prior year. SAFE_DIVIDE returns NULL in that case, which is the expected behavior.

Help me understand my scorecard results

Write your resource allocation recommendation

A business analyst delivers insights, not just query results. Your scorecard produces the data. Now you translate it into a recommendation that a non-technical stakeholder can act on.

  • Click the + Markdown button in the notebook toolbar to add a new markdown cell below your scorecard.
  • Review your scorecard output and identify the top 3 districts labeled "Needs Resources" (these have rising crime and falling arrest rates).
  • Identify the top 3 districts labeled "Improving" (these show crime reduction or arrest rate gains worth studying).
  • Write a markdown summary with two sections: districts that should receive additional patrol hours (with the data that justifies it), and districts whose practices should be studied and replicated.

What makes a good BA recommendation?

A strong recommendation names the specific action, cites the specific data point, and explains the expected outcome. Instead of "District 7 needs help," write something like: "District 7 should receive additional patrol hours. Crime increased 12% year-over-year while arrest rate dropped 3 percentage points, indicating rising demand without matching capacity."

Frame "Improving" districts as learning opportunities. Name what metric improved and suggest studying their approach for replication elsewhere.

  • After writing your recommendation, run the markdown cell to render it.
  • You should see a formatted summary with specific district numbers, percentage changes, and clear action items for leadership.

Not sure which districts to recommend?

  • Look at districts where status is "Needs Resources" in your scorecard output. These are your candidates for increased patrol hours.
  • For "Study Best Practices" picks, look at districts with the highest performance_score and lowest performance_rank values. These are doing something right.

Help me write my resource allocation recommendation

✔️ Awesome, I've got everything!

Great. Double check you have both Cell 5 and Cell 6 saved in your notebook, plus your markdown recommendation.

ⓧ I'd like to double check the full code

Here is the complete notebook code up to this point:

# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)


# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")


# Cell 3: Current State Summary - Dataset Scope and Top Categories
sql = """
SELECT
    COUNT(*) AS total_records,
    MIN(date) AS earliest_record,
    MAX(date) AS latest_record,
    COUNT(DISTINCT primary_type) AS crime_categories,
    COUNT(DISTINCT district) AS total_districts
FROM `bigquery-public-data.chicago_crime.crime`
"""
df = client.query(sql).to_dataframe()
print("=== DATASET SCOPE ===")
print(df)

sql = """
SELECT
    primary_type,
    COUNT(*) AS incident_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
ORDER BY incident_count DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("\n=== TOP 10 CRIME CATEGORIES ===")
print(df)


# Cell 4: Core KPI Calculation by Crime Type
sql = """
SELECT
    primary_type,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
HAVING COUNT(*) > 5000
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== KPI BASELINE: ARREST RATE AND DOMESTIC RATE BY CRIME TYPE ===")
print(df)


# Cell 5: District Year-over-Year Comparison (Self-Join)
sql = """
WITH district_2022 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
district_2023 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
)
SELECT
    d22.district,
    d22.total_crimes AS crimes_2022,
    d23.total_crimes AS crimes_2023,
    d23.total_crimes - d22.total_crimes AS crime_change,
    ROUND((d23.total_crimes - d22.total_crimes) * 100.0 / d22.total_crimes, 1) AS crime_pct_change,
    d22.arrest_rate_pct AS arrest_rate_2022,
    d23.arrest_rate_pct AS arrest_rate_2023,
    ROUND(d23.arrest_rate_pct - d22.arrest_rate_pct, 1) AS arrest_rate_change
FROM district_2022 d22
JOIN district_2023 d23
    ON d22.district = d23.district
ORDER BY crime_pct_change DESC
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT YEAR-OVER-YEAR COMPARISON ===")
print(df)


# Cell 6: District Performance Scorecard
sql = """
WITH district_stats AS (
    SELECT
        district,
        EXTRACT(YEAR FROM date) AS year,
        COUNT(*) AS total_crimes,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate,
        AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) AS domestic_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) IN (2022, 2023)
        AND district IS NOT NULL
    GROUP BY district, year
),
changes AS (
    SELECT
        district,
        year,
        total_crimes,
        arrest_rate,
        LAG(total_crimes) OVER (PARTITION BY district ORDER BY year) AS prev_crimes,
        LAG(arrest_rate) OVER (PARTITION BY district ORDER BY year) AS prev_arrest_rate
    FROM district_stats
),
scored AS (
    SELECT
        district,
        total_crimes AS crimes_2023,
        ROUND(SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) * 100, 1) AS crime_change_pct,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND((arrest_rate - prev_arrest_rate) * 100, 1) AS arrest_rate_change_pts,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) > 0.05
                AND (arrest_rate - prev_arrest_rate) < 0 THEN 'Needs Resources'
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.03
                OR (arrest_rate - prev_arrest_rate) > 0.02 THEN 'Improving'
            ELSE 'Stable'
        END AS status,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.05 THEN 2
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < 0 THEN 1
            ELSE 0
        END +
        CASE
            WHEN arrest_rate - prev_arrest_rate > 0.02 THEN 2
            WHEN arrest_rate - prev_arrest_rate > 0 THEN 1
            ELSE 0
        END AS performance_score
    FROM changes
    WHERE year = 2023 AND prev_crimes IS NOT NULL
)
SELECT
    district,
    crimes_2023,
    crime_change_pct,
    arrest_rate_pct,
    arrest_rate_change_pts,
    status,
    performance_score,
    RANK() OVER (ORDER BY performance_score DESC, crime_change_pct ASC) AS performance_rank
FROM scored
ORDER BY performance_rank
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT PERFORMANCE SCORECARD ===")
print(df)

You now have a ranked scorecard that clearly labels which districts need help and which are succeeding. Next, you will dig into process improvement opportunities to find out why some areas outperform others.

Identify Process Improvement Opportunities

Your District Scorecard tells leadership where to allocate resources. Now the Director has a follow-up question: why are arrest rates so low for certain crime types, and are there conditions where the department performs better that could be replicated elsewhere?

In this step, you will segment the data by time of day and location to find patterns in arrest effectiveness. Then you will quantify the potential impact of bringing underperforming districts up to the city average. This is the "process improvement" layer of a BA report.

In this step, get ready to:
  • Segment crime data by time of day and compare arrest rates across shifts.
  • Identify crime-location combinations where arrest rates diverge significantly from the average.
  • Build an impact analysis showing how many additional arrests are possible if below-average districts improve.
Segment crimes by time of day to find when arrests are most effective

If arrests happen more effectively during certain hours, that tells leadership when patrol presence actually converts to enforcement outcomes. A CASE expression on the hour of day lets you group every incident into a named shift.

  • Create a new code cell in your notebook (Cell 7) and paste the following query:
# Cell 7: Time-of-Day Arrest Effectiveness
sql = """
SELECT
    CASE
        WHEN EXTRACT(HOUR FROM date) BETWEEN 6 AND 11 THEN 'Morning (6am-12pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 12 AND 17 THEN 'Afternoon (12pm-6pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 18 AND 23 THEN 'Evening (6pm-12am)'
        ELSE 'Night (12am-6am)'
    END AS shift,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_all_crime
FROM `bigquery-public-data.chicago_crime.crime`
WHERE EXTRACT(YEAR FROM date) >= 2020
GROUP BY shift
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== ARREST EFFECTIVENESS BY SHIFT ===")
print(df)

What does this query do?

  • The outer CASE expression maps every hour of the day into four named shifts: Morning, Afternoon, Evening, and Night.
  • EXTRACT(HOUR FROM date) pulls just the hour (0-23) from each timestamp so you can bucket it.
  • The arrest_rate_pct column calculates the percentage of incidents in each shift that resulted in an arrest.
  • SUM(COUNT(*)) OVER () is a window function that calculates the grand total across all rows, letting you compute each shift's percentage of all crime.
  • Run the cell by pressing **Shift+Enter**.
  • You should see a table with four rows (one per shift), each showing total incidents, arrest rate percentage, and share of all crime.

Seeing zero rows or an error?

Make sure the WHERE clause says EXTRACT(YEAR FROM date) >= 2020 with the correct column name date. If you see a "not found" error, confirm the backtick-quoted table path is exactly `bigquery-public-data.chicago_crime.crime`.

Help me debug my time-of-day shift query in BigQuery

Analyze arrest rates by crime type and location

Knowing that arrests are more effective at certain times is useful. But the Director also wants to know if specific locations drive higher arrest rates for certain crime types. A CTE-based query lets you first calculate per-combination stats, then compare each combination against the average for its crime type.

  • Create a new code cell (Cell 8) and paste the following query:
# Cell 8: Crime-Location Arrest Rate Analysis
sql = """
WITH location_stats AS (
    SELECT
        primary_type,
        location_description,
        COUNT(*) AS incidents,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) >= 2020
        AND location_description IS NOT NULL
    GROUP BY primary_type, location_description
    HAVING COUNT(*) > 1000
),
with_avg AS (
    SELECT
        primary_type,
        location_description,
        incidents,
        arrest_rate_pct,
        AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS avg_rate_for_type,
        arrest_rate_pct - AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS diff_from_avg
    FROM location_stats
)
SELECT *
FROM with_avg
WHERE ABS(diff_from_avg) > 5
ORDER BY diff_from_avg DESC
LIMIT 15
"""
df = client.query(sql).to_dataframe()
print("=== PROCESS GAPS: LOCATIONS WHERE ARREST RATES DIFFER FROM TYPE AVERAGE ===")
print(df)

What does this query do?

  • The location_stats CTE groups incidents by crime type and location, keeping only combinations with more than 1,000 incidents so that the arrest rate percentages are statistically meaningful.
  • The with_avg CTE uses a window function to calculate the average arrest rate for each crime type across all its locations, then computes how much each combination deviates from that average.
  • The final SELECT filters for deviations greater than 5 percentage points in either direction. Positive values mean that location has an unusually high arrest rate for that crime type.
  • Run the cell by pressing **Shift+Enter**.
  • You should see up to 15 rows showing crime-location combinations where the arrest rate is at least 5 percentage points above or below the type average.

Why does this matter for the Director?

If narcotics arrests at a "STREET" location run 20 points above the type average, that tells leadership something about how street-level policing converts to arrests for that category. Conversely, a location type with a below-average rate suggests a process gap worth investigating.

Query returns no rows?

If you see an empty result, check that the HAVING threshold is COUNT(*) > 1000 (not 10000). Also confirm the ABS(diff_from_avg) > 5 filter in the outer WHERE clause is present.

Help me debug my CTE-based location analysis query

Build the impact analysis for below-average districts

Spotting patterns is step one. The Director also needs a number to take to the budget meeting: if underperforming districts were brought up to the city average, how many additional arrests would that produce? This is the kind of impact quantification that separates a data dump from a business recommendation.

  • Create a new code cell (Cell 9) and paste the following query:
# Cell 9: Process Gap - Impact of Bringing Below-Average Districts to Average
sql = """
WITH district_type_stats AS (
    SELECT
        district,
        primary_type,
        COUNT(*) AS incidents,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district, primary_type
    HAVING COUNT(*) > 200
),
with_city_avg AS (
    SELECT
        district,
        primary_type,
        incidents,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(arrest_rate) OVER (PARTITION BY primary_type) * 100, 1) AS city_avg_rate_pct,
        CASE
            WHEN arrest_rate < AVG(arrest_rate) OVER (PARTITION BY primary_type)
            THEN ROUND(
                (AVG(arrest_rate) OVER (PARTITION BY primary_type) - arrest_rate) * incidents
            , 0)
            ELSE 0
        END AS additional_arrests_if_at_avg
    FROM district_type_stats
)
SELECT
    primary_type,
    ROUND(SUM(additional_arrests_if_at_avg)) AS total_additional_arrests,
    COUNT(CASE WHEN additional_arrests_if_at_avg > 0 THEN 1 END) AS districts_below_avg
FROM with_city_avg
GROUP BY primary_type
HAVING SUM(additional_arrests_if_at_avg) > 0
ORDER BY total_additional_arrests DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("=== IMPACT ANALYSIS: ADDITIONAL ARRESTS IF BELOW-AVG DISTRICTS REACH CITY AVERAGE ===")
print(df)

What does this query do?

  • The district_type_stats CTE calculates the arrest rate for each district-crime-type pair in 2023, filtering to combinations with at least 200 incidents.
  • The with_city_avg CTE uses AVG(...) OVER (PARTITION BY primary_type) to compute the city-wide average arrest rate for each crime type, then calculates how many additional arrests each below-average district would have made if it performed at the average.
  • The final aggregation groups by crime type and sums the potential additional arrests across all underperforming districts. This gives leadership a concrete number for each category.
  • Run the cell by pressing **Shift+Enter**.
  • You should see up to 10 crime types ranked by total potential additional arrests, with a column showing how many districts are currently below the city average.

Seeing negative numbers or unexpected results?

The CASE expression sets additional_arrests_if_at_avg to 0 for districts already at or above average. If you see negatives, make sure the ELSE 0 clause is present. Also confirm the HAVING in the outer query filters for SUM(additional_arrests_if_at_avg) > 0 so only categories with improvement potential appear.

Help me debug my process gap impact analysis query

✔️ Awesome, I've got everything!

Great. Double check you have Cells 7, 8, and 9 all producing output before moving on.

ⓧ I'd like to double check the full code

Here is the full notebook code from Cell 1 through Cell 9. Compare your cells against this:

# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)


# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")


# Cell 3: Current State Summary - Dataset Scope and Top Categories
sql = """
SELECT
    COUNT(*) AS total_records,
    MIN(date) AS earliest_record,
    MAX(date) AS latest_record,
    COUNT(DISTINCT primary_type) AS crime_categories,
    COUNT(DISTINCT district) AS total_districts
FROM `bigquery-public-data.chicago_crime.crime`
"""
df = client.query(sql).to_dataframe()
print("=== DATASET SCOPE ===")
print(df)

sql = """
SELECT
    primary_type,
    COUNT(*) AS incident_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
ORDER BY incident_count DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("\n=== TOP 10 CRIME CATEGORIES ===")
print(df)


# Cell 4: Core KPI Calculation by Crime Type
sql = """
SELECT
    primary_type,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
HAVING COUNT(*) > 5000
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== KPI BASELINE: ARREST RATE AND DOMESTIC RATE BY CRIME TYPE ===")
print(df)


# Cell 5: District Year-over-Year Comparison (Self-Join)
sql = """
WITH district_2022 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
district_2023 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
)
SELECT
    d22.district,
    d22.total_crimes AS crimes_2022,
    d23.total_crimes AS crimes_2023,
    d23.total_crimes - d22.total_crimes AS crime_change,
    ROUND((d23.total_crimes - d22.total_crimes) * 100.0 / d22.total_crimes, 1) AS crime_pct_change,
    d22.arrest_rate_pct AS arrest_rate_2022,
    d23.arrest_rate_pct AS arrest_rate_2023,
    ROUND(d23.arrest_rate_pct - d22.arrest_rate_pct, 1) AS arrest_rate_change
FROM district_2022 d22
JOIN district_2023 d23
    ON d22.district = d23.district
ORDER BY crime_pct_change DESC
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT YEAR-OVER-YEAR COMPARISON ===")
print(df)


# Cell 6: District Performance Scorecard
sql = """
WITH district_stats AS (
    SELECT
        district,
        EXTRACT(YEAR FROM date) AS year,
        COUNT(*) AS total_crimes,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate,
        AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) AS domestic_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) IN (2022, 2023)
        AND district IS NOT NULL
    GROUP BY district, year
),
changes AS (
    SELECT
        district,
        year,
        total_crimes,
        arrest_rate,
        LAG(total_crimes) OVER (PARTITION BY district ORDER BY year) AS prev_crimes,
        LAG(arrest_rate) OVER (PARTITION BY district ORDER BY year) AS prev_arrest_rate
    FROM district_stats
),
scored AS (
    SELECT
        district,
        total_crimes AS crimes_2023,
        ROUND(SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) * 100, 1) AS crime_change_pct,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND((arrest_rate - prev_arrest_rate) * 100, 1) AS arrest_rate_change_pts,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) > 0.05
                AND (arrest_rate - prev_arrest_rate) < 0 THEN 'Needs Resources'
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.03
                OR (arrest_rate - prev_arrest_rate) > 0.02 THEN 'Improving'
            ELSE 'Stable'
        END AS status,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.05 THEN 2
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < 0 THEN 1
            ELSE 0
        END +
        CASE
            WHEN arrest_rate - prev_arrest_rate > 0.02 THEN 2
            WHEN arrest_rate - prev_arrest_rate > 0 THEN 1
            ELSE 0
        END AS performance_score
    FROM changes
    WHERE year = 2023 AND prev_crimes IS NOT NULL
)
SELECT
    district,
    crimes_2023,
    crime_change_pct,
    arrest_rate_pct,
    arrest_rate_change_pts,
    status,
    performance_score,
    RANK() OVER (ORDER BY performance_score DESC, crime_change_pct ASC) AS performance_rank
FROM scored
ORDER BY performance_rank
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT PERFORMANCE SCORECARD ===")
print(df)


# Cell 7: Time-of-Day Arrest Effectiveness
sql = """
SELECT
    CASE
        WHEN EXTRACT(HOUR FROM date) BETWEEN 6 AND 11 THEN 'Morning (6am-12pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 12 AND 17 THEN 'Afternoon (12pm-6pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 18 AND 23 THEN 'Evening (6pm-12am)'
        ELSE 'Night (12am-6am)'
    END AS shift,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_all_crime
FROM `bigquery-public-data.chicago_crime.crime`
WHERE EXTRACT(YEAR FROM date) >= 2020
GROUP BY shift
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== ARREST EFFECTIVENESS BY SHIFT ===")
print(df)


# Cell 8: Crime-Location Arrest Rate Analysis
sql = """
WITH location_stats AS (
    SELECT
        primary_type,
        location_description,
        COUNT(*) AS incidents,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) >= 2020
        AND location_description IS NOT NULL
    GROUP BY primary_type, location_description
    HAVING COUNT(*) > 1000
),
with_avg AS (
    SELECT
        primary_type,
        location_description,
        incidents,
        arrest_rate_pct,
        AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS avg_rate_for_type,
        arrest_rate_pct - AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS diff_from_avg
    FROM location_stats
)
SELECT *
FROM with_avg
WHERE ABS(diff_from_avg) > 5
ORDER BY diff_from_avg DESC
LIMIT 15
"""
df = client.query(sql).to_dataframe()
print("=== PROCESS GAPS: LOCATIONS WHERE ARREST RATES DIFFER FROM TYPE AVERAGE ===")
print(df)


# Cell 9: Process Gap - Impact of Bringing Below-Average Districts to Average
sql = """
WITH district_type_stats AS (
    SELECT
        district,
        primary_type,
        COUNT(*) AS incidents,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district, primary_type
    HAVING COUNT(*) > 200
),
with_city_avg AS (
    SELECT
        district,
        primary_type,
        incidents,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(arrest_rate) OVER (PARTITION BY primary_type) * 100, 1) AS city_avg_rate_pct,
        CASE
            WHEN arrest_rate < AVG(arrest_rate) OVER (PARTITION BY primary_type)
            THEN ROUND(
                (AVG(arrest_rate) OVER (PARTITION BY primary_type) - arrest_rate) * incidents
            , 0)
            ELSE 0
        END AS additional_arrests_if_at_avg
    FROM district_type_stats
)
SELECT
    primary_type,
    ROUND(SUM(additional_arrests_if_at_avg)) AS total_additional_arrests,
    COUNT(CASE WHEN additional_arrests_if_at_avg > 0 THEN 1 END) AS districts_below_avg
FROM with_city_avg
GROUP BY primary_type
HAVING SUM(additional_arrests_if_at_avg) > 0
ORDER BY total_additional_arrests DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("=== IMPACT ANALYSIS: ADDITIONAL ARRESTS IF BELOW-AVG DISTRICTS REACH CITY AVERAGE ===")
print(df)

You now have a complete process improvement analysis. You know when, where, and for which crime types the department is most effective. Next up, you will pull all of your findings together into a single budget-meeting-ready query and write the final recommendation the Director can take to leadership.

Deliver the Final Recommendation Report

You have all the raw analysis done. You know which districts are struggling, which shifts produce better arrest rates, and which crime-location combinations reveal process gaps.

Now the Director needs one page to bring to the budget meeting. Your job is to pull everything together into a single query that produces an actionable dashboard, plus a written recommendation that a non-technical stakeholder can read and act on.

In this step, get ready to:
  • Build a comprehensive Budget Meeting Summary query combining CTEs, CASE logic, and window functions.
  • Write a final recommendation markdown cell framing your SQL results as actionable business decisions.
Write the Budget Meeting Summary query

The Director said: "Give me one page I can take to the budget meeting that shows where we should add resources, where we should study best practices, and what the expected impact would be."

This final query needs to combine everything into a single output. For each district, it shows 2023 crime volume, year-over-year change, arrest rate, comparison to the city average, and a recommended action.

  • Create a new code cell in your notebook and paste the following query:
# Cell 10: Final Budget Meeting Summary Query
sql = """
WITH district_metrics AS (
    SELECT
        district,
        COUNT(*) AS crimes_2023,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
),
prev_year AS (
    SELECT
        district,
        COUNT(*) AS crimes_2022
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
combined AS (
    SELECT
        dm.district,
        dm.crimes_2023,
        py.crimes_2022,
        ROUND((dm.crimes_2023 - py.crimes_2022) * 100.0 / py.crimes_2022, 1) AS yoy_change_pct,
        dm.arrest_rate_pct,
        AVG(dm.arrest_rate_pct) OVER () AS city_avg_arrest_rate,
        dm.arrest_rate_pct - AVG(dm.arrest_rate_pct) OVER () AS vs_city_avg
    FROM district_metrics dm
    JOIN prev_year py ON dm.district = py.district
)
SELECT
    district,
    crimes_2023,
    yoy_change_pct,
    arrest_rate_pct,
    ROUND(city_avg_arrest_rate, 1) AS city_avg_arrest_rate,
    ROUND(vs_city_avg, 1) AS vs_city_avg_pts,
    CASE
        WHEN yoy_change_pct > 10 AND vs_city_avg < -3 THEN 'Increase Patrol'
        WHEN yoy_change_pct < -5 AND vs_city_avg > 3 THEN 'Study Best Practices'
        WHEN yoy_change_pct > 10 AND vs_city_avg >= -3 THEN 'Reassess Strategy'
        ELSE 'Maintain Current'
    END AS recommended_action
FROM combined
ORDER BY
    CASE
        WHEN yoy_change_pct > 10 AND vs_city_avg < -3 THEN 1
        WHEN yoy_change_pct < -5 AND vs_city_avg > 3 THEN 2
        WHEN yoy_change_pct > 10 AND vs_city_avg >= -3 THEN 3
        ELSE 4
    END,
    yoy_change_pct DESC
"""
df = client.query(sql).to_dataframe()
print("=== BUDGET MEETING SUMMARY: RESOURCE ALLOCATION RECOMMENDATIONS ===")
print(df)

What does this query do?

This query uses three CTEs to build up the final output layer by layer:

  • district_metrics calculates each district's 2023 crime count, arrest rate, and domestic rate.
  • prev_year pulls 2022 crime counts so you can calculate year-over-year change.
  • combined joins the two periods and uses a window function (AVG() OVER ()) to compute the city-wide average arrest rate for comparison.
  • The final SELECT uses a CASE expression to assign each district one of four actions: Increase Patrol, Study Best Practices, Reassess Strategy, or Maintain Current.
  • The ORDER BY CASE pattern puts the most urgent recommendations at the top of the output.
  • Run the cell.
  • You should see a table with every district listed alongside its year-over-year change, arrest rate, comparison to city average, and a recommended action label.

Seeing an error about NULL division?

If you see an error related to division by zero, check that the prev_year CTE is filtering for 2022 data. Districts with zero 2022 crimes would cause a division error in the percentage calculation.

The AND district IS NOT NULL filter in both CTEs handles records without a district assignment.

Help me debug my Budget Meeting Summary query.

Why this query structure matters for interviews

This single query demonstrates five SQL patterns that data analyst interviews test heavily: CTEs for readability, JOINs for combining time periods, window functions for city-wide comparisons, CASE for business logic, and a custom ORDER BY for presentation.

Interviewers want to see that you can combine multiple techniques into one coherent, readable query. This is that query.

Write the final recommendation markdown summary

A business analyst does not hand over a table of numbers and call it done. The Director needs a written recommendation they can read aloud at the budget meeting. This markdown cell is your deliverable.

  • Create a new markdown cell below your last code cell.
  • In Kaggle, click the + Markdown button in the toolbar to add a text cell.

Your written recommendation should include these four sections:

  • Top 3 districts for resource increase, with justification from the query output (high year-over-year crime increase combined with below-average arrest rate).
  • Top 3 districts to study, identifying what makes them effective (crime declining and arrest rates above city average).
  • 2 process changes based on your time-of-day and location findings from Step 4 (for example, which shift has highest arrest effectiveness, which locations show above-average performance).
  • Expected impact of recommendations, referencing the "additional arrests if at average" numbers from your Process Gap analysis.

How to structure the markdown cell

Use your actual query results to fill in specific district numbers and percentages. A real BA deliverable names specific values. "District 8 saw a 14% increase in crime with an arrest rate 5 points below city average" is far stronger than "some districts need help."

Headings, bullet points, and bold text in markdown make the cell scannable for a busy executive.

  • Write your recommendation using the results from both this query and your earlier process analysis.
  • Run the markdown cell to render it.

Frame findings as decisions

Instead of "District X has high crime," write "Recommend increasing patrol hours in District X because crime rose 14% while arrest rates dropped 3 points." Every sentence should connect a data point to a specific action.

✔️ Awesome, I've got everything!

Great. Double check you have saved all your cells.

ⓧ I'd like to double check the full code

Here is the complete notebook code through Cell 10:

# Cell 1: Setup and Connection
from google.cloud import bigquery

client = bigquery.Client()

dataset_ref = client.dataset("chicago_crime", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

tables = client.list_tables(dataset)
for table in tables:
    print(table.table_id)


# Cell 2: Inspect Schema
table_ref = dataset_ref.table("crime")
table = client.get_table(table_ref)

print(f"Table has {table.num_rows:,} rows and {len(table.schema)} columns\n")
for field in table.schema:
    print(f"{field.name}: {field.field_type} ({field.mode})")


# Cell 3: Current State Summary - Dataset Scope and Top Categories
sql = """
SELECT
    COUNT(*) AS total_records,
    MIN(date) AS earliest_record,
    MAX(date) AS latest_record,
    COUNT(DISTINCT primary_type) AS crime_categories,
    COUNT(DISTINCT district) AS total_districts
FROM `bigquery-public-data.chicago_crime.crime`
"""
df = client.query(sql).to_dataframe()
print("=== DATASET SCOPE ===")
print(df)

sql = """
SELECT
    primary_type,
    COUNT(*) AS incident_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
ORDER BY incident_count DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("\n=== TOP 10 CRIME CATEGORIES ===")
print(df)


# Cell 4: Core KPI Calculation by Crime Type
sql = """
SELECT
    primary_type,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
FROM `bigquery-public-data.chicago_crime.crime`
GROUP BY primary_type
HAVING COUNT(*) > 5000
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== KPI BASELINE: ARREST RATE AND DOMESTIC RATE BY CRIME TYPE ===")
print(df)


# Cell 5: District Year-over-Year Comparison (Self-Join)
sql = """
WITH district_2022 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
district_2023 AS (
    SELECT
        district,
        COUNT(*) AS total_crimes,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
)
SELECT
    d22.district,
    d22.total_crimes AS crimes_2022,
    d23.total_crimes AS crimes_2023,
    d23.total_crimes - d22.total_crimes AS crime_change,
    ROUND((d23.total_crimes - d22.total_crimes) * 100.0 / d22.total_crimes, 1) AS crime_pct_change,
    d22.arrest_rate_pct AS arrest_rate_2022,
    d23.arrest_rate_pct AS arrest_rate_2023,
    ROUND(d23.arrest_rate_pct - d22.arrest_rate_pct, 1) AS arrest_rate_change
FROM district_2022 d22
JOIN district_2023 d23
    ON d22.district = d23.district
ORDER BY crime_pct_change DESC
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT YEAR-OVER-YEAR COMPARISON ===")
print(df)


# Cell 6: District Performance Scorecard
sql = """
WITH district_stats AS (
    SELECT
        district,
        EXTRACT(YEAR FROM date) AS year,
        COUNT(*) AS total_crimes,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate,
        AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) AS domestic_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) IN (2022, 2023)
        AND district IS NOT NULL
    GROUP BY district, year
),
changes AS (
    SELECT
        district,
        year,
        total_crimes,
        arrest_rate,
        LAG(total_crimes) OVER (PARTITION BY district ORDER BY year) AS prev_crimes,
        LAG(arrest_rate) OVER (PARTITION BY district ORDER BY year) AS prev_arrest_rate
    FROM district_stats
),
scored AS (
    SELECT
        district,
        total_crimes AS crimes_2023,
        ROUND(SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) * 100, 1) AS crime_change_pct,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND((arrest_rate - prev_arrest_rate) * 100, 1) AS arrest_rate_change_pts,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) > 0.05
                AND (arrest_rate - prev_arrest_rate) < 0 THEN 'Needs Resources'
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.03
                OR (arrest_rate - prev_arrest_rate) > 0.02 THEN 'Improving'
            ELSE 'Stable'
        END AS status,
        CASE
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < -0.05 THEN 2
            WHEN SAFE_DIVIDE(total_crimes - prev_crimes, prev_crimes) < 0 THEN 1
            ELSE 0
        END +
        CASE
            WHEN arrest_rate - prev_arrest_rate > 0.02 THEN 2
            WHEN arrest_rate - prev_arrest_rate > 0 THEN 1
            ELSE 0
        END AS performance_score
    FROM changes
    WHERE year = 2023 AND prev_crimes IS NOT NULL
)
SELECT
    district,
    crimes_2023,
    crime_change_pct,
    arrest_rate_pct,
    arrest_rate_change_pts,
    status,
    performance_score,
    RANK() OVER (ORDER BY performance_score DESC, crime_change_pct ASC) AS performance_rank
FROM scored
ORDER BY performance_rank
"""
df = client.query(sql).to_dataframe()
print("=== DISTRICT PERFORMANCE SCORECARD ===")
print(df)


# Cell 7: Time-of-Day Arrest Effectiveness
sql = """
SELECT
    CASE
        WHEN EXTRACT(HOUR FROM date) BETWEEN 6 AND 11 THEN 'Morning (6am-12pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 12 AND 17 THEN 'Afternoon (12pm-6pm)'
        WHEN EXTRACT(HOUR FROM date) BETWEEN 18 AND 23 THEN 'Evening (6pm-12am)'
        ELSE 'Night (12am-6am)'
    END AS shift,
    COUNT(*) AS total_incidents,
    ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_all_crime
FROM `bigquery-public-data.chicago_crime.crime`
WHERE EXTRACT(YEAR FROM date) >= 2020
GROUP BY shift
ORDER BY total_incidents DESC
"""
df = client.query(sql).to_dataframe()
print("=== ARREST EFFECTIVENESS BY SHIFT ===")
print(df)


# Cell 8: Crime-Location Arrest Rate Analysis
sql = """
WITH location_stats AS (
    SELECT
        primary_type,
        location_description,
        COUNT(*) AS incidents,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) >= 2020
        AND location_description IS NOT NULL
    GROUP BY primary_type, location_description
    HAVING COUNT(*) > 1000
),
with_avg AS (
    SELECT
        primary_type,
        location_description,
        incidents,
        arrest_rate_pct,
        AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS avg_rate_for_type,
        arrest_rate_pct - AVG(arrest_rate_pct) OVER (PARTITION BY primary_type) AS diff_from_avg
    FROM location_stats
)
SELECT *
FROM with_avg
WHERE ABS(diff_from_avg) > 5
ORDER BY diff_from_avg DESC
LIMIT 15
"""
df = client.query(sql).to_dataframe()
print("=== PROCESS GAPS: LOCATIONS WHERE ARREST RATES DIFFER FROM TYPE AVERAGE ===")
print(df)


# Cell 9: Process Gap - Impact of Bringing Below-Average Districts to Average
sql = """
WITH district_type_stats AS (
    SELECT
        district,
        primary_type,
        COUNT(*) AS incidents,
        AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) AS arrest_rate
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district, primary_type
    HAVING COUNT(*) > 200
),
with_city_avg AS (
    SELECT
        district,
        primary_type,
        incidents,
        ROUND(arrest_rate * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(arrest_rate) OVER (PARTITION BY primary_type) * 100, 1) AS city_avg_rate_pct,
        CASE
            WHEN arrest_rate < AVG(arrest_rate) OVER (PARTITION BY primary_type)
            THEN ROUND(
                (AVG(arrest_rate) OVER (PARTITION BY primary_type) - arrest_rate) * incidents
            , 0)
            ELSE 0
        END AS additional_arrests_if_at_avg
    FROM district_type_stats
)
SELECT
    primary_type,
    ROUND(SUM(additional_arrests_if_at_avg)) AS total_additional_arrests,
    COUNT(CASE WHEN additional_arrests_if_at_avg > 0 THEN 1 END) AS districts_below_avg
FROM with_city_avg
GROUP BY primary_type
HAVING SUM(additional_arrests_if_at_avg) > 0
ORDER BY total_additional_arrests DESC
LIMIT 10
"""
df = client.query(sql).to_dataframe()
print("=== IMPACT ANALYSIS: ADDITIONAL ARRESTS IF BELOW-AVG DISTRICTS REACH CITY AVERAGE ===")
print(df)


# Cell 10: Final Budget Meeting Summary Query
sql = """
WITH district_metrics AS (
    SELECT
        district,
        COUNT(*) AS crimes_2023,
        ROUND(AVG(CASE WHEN arrest = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS arrest_rate_pct,
        ROUND(AVG(CASE WHEN domestic = TRUE THEN 1.0 ELSE 0.0 END) * 100, 1) AS domestic_rate_pct
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2023
        AND district IS NOT NULL
    GROUP BY district
),
prev_year AS (
    SELECT
        district,
        COUNT(*) AS crimes_2022
    FROM `bigquery-public-data.chicago_crime.crime`
    WHERE EXTRACT(YEAR FROM date) = 2022
        AND district IS NOT NULL
    GROUP BY district
),
combined AS (
    SELECT
        dm.district,
        dm.crimes_2023,
        py.crimes_2022,
        ROUND((dm.crimes_2023 - py.crimes_2022) * 100.0 / py.crimes_2022, 1) AS yoy_change_pct,
        dm.arrest_rate_pct,
        AVG(dm.arrest_rate_pct) OVER () AS city_avg_arrest_rate,
        dm.arrest_rate_pct - AVG(dm.arrest_rate_pct) OVER () AS vs_city_avg
    FROM district_metrics dm
    JOIN prev_year py ON dm.district = py.district
)
SELECT
    district,
    crimes_2023,
    yoy_change_pct,
    arrest_rate_pct,
    ROUND(city_avg_arrest_rate, 1) AS city_avg_arrest_rate,
    ROUND(vs_city_avg, 1) AS vs_city_avg_pts,
    CASE
        WHEN yoy_change_pct > 10 AND vs_city_avg < -3 THEN 'Increase Patrol'
        WHEN yoy_change_pct < -5 AND vs_city_avg > 3 THEN 'Study Best Practices'
        WHEN yoy_change_pct > 10 AND vs_city_avg >= -3 THEN 'Reassess Strategy'
        ELSE 'Maintain Current'
    END AS recommended_action
FROM combined
ORDER BY
    CASE
        WHEN yoy_change_pct > 10 AND vs_city_avg < -3 THEN 1
        WHEN yoy_change_pct < -5 AND vs_city_avg > 3 THEN 2
        WHEN yoy_change_pct > 10 AND vs_city_avg >= -3 THEN 3
        ELSE 4
    END,
    yoy_change_pct DESC
"""
df = client.query(sql).to_dataframe()
print("=== BUDGET MEETING SUMMARY: RESOURCE ALLOCATION RECOMMENDATIONS ===")
print(df)
Write the final recommendation markdown cell

The query gives you the data. Now you need to translate it into a recommendation a non-technical stakeholder can act on.

  • Click + Markdown to create a new text cell below your Cell 10 output.
  • Title the cell "Resource Allocation Recommendation" using a markdown heading.
  • Review your Cell 10 output and identify which districts received the Increase Patrol label.
  • Write a section naming the top 3 districts for resource increase, including each district's year-over-year change percentage and how far below city average their arrest rate falls.
  • Write a section naming the top 3 districts labeled Study Best Practices, explaining what makes their performance stand out (declining crime, above-average arrests).
  • Write a section recommending 2 process changes. Reference your time-of-day findings (which shift has the highest arrest effectiveness) and your location findings (which crime-location combinations outperform the average).
  • Write an "Expected Impact" section referencing the additional arrests numbers from your Cell 9 output.
  • Run the markdown cell to render it.

Why write recommendations, not just queries?

In business analyst roles, the SQL is the means. The recommendation is the deliverable. Hiring managers want to see that you can translate numbers into decisions.

A notebook full of queries without interpretation shows technical skill. A notebook that ends with "Increase evening patrols in Districts 7, 11, and 15 based on these metrics" shows business judgment.

  • Review your completed notebook from top to bottom. It should read like a report: context, KPIs, district scorecard, process gaps, and a final recommendation.

Not sure what to write in the recommendation?

Look at your Cell 10 output for districts labeled Increase Patrol. Those are your "add resources" picks. Look at Cell 7 output for the shift with the highest arrest rate. Look at Cell 8 for location types where arrest rates beat the average.

Connect data points to actions. If Cell 9 shows that bringing below-average districts to the city average for theft would add N additional arrests, say that.

Help me write my recommendation summary for the budget meeting.

Your notebook is now a complete business analyst deliverable. It starts with a stakeholder question, works through the data methodically, and ends with specific actions the Director can take to the budget meeting.

Secret mission

Build a Seasonal Staffing Model

Your resource allocation report tells leadership where to send resources. But when should they send them? Build a seasonal staffing model that recommends which months and times of day each district should increase or decrease patrol coverage.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Kaggle's browser-based environment using BigQuery's free tier. There are no ongoing costs or running processes to worry about.

Resources you used:

  • Kaggle notebook: Public Safety Resource Allocation Report (includes all SQL queries and markdown summaries).
  • BigQuery public dataset queries (no persistent resources created, covered by the free 1 TiB/month quota).
  • Kaggle account (free, used to host and run your notebook).

Keep everything running

No action needed. Choose this if you want to keep building on the notebook or reference your queries later.

  • Your notebook stays in your Kaggle profile under the Code tab.
  • BigQuery queries only consume quota when you run them. There is no background cost while the notebook sits idle.
  • You can publish the notebook to make it visible on your public Kaggle profile as a portfolio piece.

Pause - I'll come back to this later

Make your notebook private so it is hidden from your public profile but still accessible to you.

  • Open the notebook in Kaggle.
  • Click Settings in the notebook sidebar.
  • Under Sharing, set the visibility to Private.
  • Your notebook remains in your account and you can return to it at any time.

Delete - I don't want to use this again

Remove the notebook from your Kaggle account entirely.

  • Go to your Kaggle profile and click the Code tab.
  • Find the Public Safety Resource Allocation Report notebook.
  • Click the three-dot menu on the notebook card and select Delete.
  • If you created a separate notebook for the Secret Mission seasonal staffing model, repeat the same deletion steps for that notebook.
  • No BigQuery resources need to be deleted. You only queried a public dataset and did not create any tables or datasets in your own project.

Nice Work!

Nice Work!

Nice work! You just built a complete business analysis report that translates real stakeholder questions into data-backed resource allocation recommendations using SQL on a 7+ million row production dataset.

Here's what you accomplished:

  • Translated business requirements into production SQL queries using BigQuery, defining KPIs like arrest rate, incident volume, and year-over-year change to answer a fictional Operations Director's questions.
  • Built a District Performance Scorecard combining CTEs, window functions, CASE logic, and self-joins to rank districts and recommend specific resource allocation actions.
  • Delivered executive summaries and recommendations that frame SQL results as actionable decisions a non-technical stakeholder could read and act on at a budget meeting.
  • Secret Mission: Built a seasonal staffing model using moving averages and multi-dimensional grouping to recommend specific patrol schedule changes by district and month.

Ready to quiz yourself?