Beginner SQL Project: Build a Report With BigQuery and Looker

Want to practice your SQL skills on a real dataset? In this beginner-friendly project, you’ll use BigQuery and Looker to explore Chicago’s crime data and build your first interactive report – no advanced experience needed!

If you want to get better at SQL, nothing beats working on real projects. Practice is where your skills truly grow – and today, you’ll take a big step forward.

In this beginner-friendly project, you’ll create a simple crime report based on the Chicago Crime public dataset, using BigQuery to write SQL queries and Looker Studio to build an interactive dashboard. No advanced setup, no complicated tools – just a clear path from raw data to real insights.

We'll be using two powerful (and free) tools:

• BigQuery to query large public datasets with SQL.
• Looker Studio to turn your results into easy-to-understand visualizations.

If you want to build even stronger skills after this project, check out the SQL From A to Z track at LearnSQL.com – it’s a full learning path designed to take you from absolute beginner to confident SQL user with hands-on practice.

Let’s get started!

Step 1: Set Up Your Environment

Before we start writing SQL queries, let’s get everything ready. Don’t worry – it’s quick, free, and beginner-friendly.

Access BigQuery

First, you’ll need a Google account. Go to Google Cloud Console, create a new project, and open BigQuery. Google offers a free tier that’s more than enough for this project – no credit card needed if you just use BigQuery’s public datasets.

Inside BigQuery, you’ll be able to run SQL queries directly in your browser without installing anything.

Find the Chicago Crime Public Dataset

Once you're in BigQuery, click "Add Data" → "Explore Public Datasets". Search for "chicago crime" – the dataset you need is called: bigquery-public-data.chicago_crime.crime

Click on it to view the tables and fields available. You’re ready to start querying!

Set Up Looker Studio

Looker Studio (formerly known as Google Data Studio) lets you easily visualize your BigQuery results – no coding needed.

Visit Looker Studio, sign in with the same Google account, and click "Create" → "Report".
When asked to select a data source, choose BigQuery, find your project, and connect to the dataset you’ll be working with.

That’s it – your environment is ready! In the next step, we’ll start exploring the data with SQL.

Step 2: Explore the Dataset

Let’s take a quick look at the data you’ll be working with. The Chicago Crime dataset contains detailed records of reported crimes in Chicago, going back to 2001.

Each row represents a single crime report and includes useful information like:

• date – when the crime occurred.
• primary_type – the general category (e.g., THEFT, ASSAULT).
• description – a more specific label.
• location_description – where it happened (e.g., STREET, RESIDENCE).
• arrest – whether an arrest was made (TRUE/FALSE).
• district – the police district number.
• latitude / longitude – the geographic location.

To preview the dataset, run this simple query in BigQuery:

SELECT *  
FROM `bigquery-public-data.chicago_crime.crime`  
LIMIT 10;

This shows the first 10 rows and gives you a feel for the structure of the data. Spend a moment scrolling through the results – knowing what’s available will help you write smarter queries.

Step 3: Write Your Queries

Now that you’ve seen what’s in the dataset, it’s time to write your first SQL query. Our goal here is to find out how many crimes were committed in Chicago in 2024, grouped by type.

In a real-world report, you’d likely want to go deeper – exploring arrest rates, comparing trends over time, or breaking things down by neighborhood. But to keep this tutorial beginner-friendly, we’ll stick to just one clear question:  Which types of crimes were most common in 2024?

To answer that, we’ll:

• Filter the data to just the year 2024.
• Group by primary_type (crime category).
• Count the number of crimes in each category.

Here’s the query:

SELECT 
  primary_type, 
  COUNT(*) AS num_crimes
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
GROUP BY primary_type
ORDER BY num_crimes DESC;

This gives you a ranked list of crime types, from most to least frequent. In the next step, we’ll use this result to build a simple bar chart in Looker Studio.

To learn how to write more complex queries for reports, take a look at the SQL Reporting track on LearnSQL.com. It’s a hands-on guide for turning raw data into useful insights using real reporting techniques.

You can also keep the SQL Basics Cheat Sheet open as a quick reference while you write – it’s a great companion for beginners.

Step 4: Connect BigQuery to Looker Studio

Now that your query is ready, let’s visualize the results using Looker Studio (formerly Google Data Studio). Since we're using the free tier of BigQuery, we’ll run the query using the Custom Query option, without needing to create or save any tables or views.

1. Go to Looker Studio and sign in with the same Google account you used for BigQuery.
2. Click “Blank Report” or choose “Create → Report”.
3. When prompted to select a data source, choose “BigQuery”.
4. In the connector window:
• Select your own project (the one you created earlier in Google Cloud Console).
• Then click “Custom Query” in the bottom left.
5. In the Custom Query box, paste the SQL query you created earlier:

   SELECT 
     primary_type, 
     COUNT(*) AS num_crimes
   FROM `bigquery-public-data.chicago_crime.crime`
   WHERE year = 2024
   GROUP BY primary_type
   ORDER BY num_crimes DESC;
   

6. Click “Add”, then confirm by clicking “Add to Report”.
   

Looker Studio will now use the results of your query directly, without requiring you to store any data. You're ready to build your first chart.

Step 5: Create a Simple Report

With your data source connected, it’s time to create your first report. In Looker Studio, start by building a bar chart that shows the number of crimes by type.

Create the Chart

1. From the top menu, click “Add a chart” and select the bar chart.
2. Drag to place it on the report canvas.

Set Up the Data

• Dimension (X-axis): primary_type
• Metric (Y-axis): num_crimes

This will display a bar chart where each bar represents a type of crime, and the height shows how many times it occurred in 2024.

Looker Studio offers many other chart types you can experiment with – including line charts, pie charts, geo maps, and more. As your queries become more advanced, you can use these to show trends over time, compare categories, or visualize data by location.

For now, we’ll keep things simple with one chart, but feel free to explore and customize the layout as you go.

Step 6: Final Touches

Now that your chart is ready, take a minute to polish the report and make it your own.

• Add a title using the text box tool – for example: “Chicago Crimes by Type – 2024”.
• Format numbers for readability – add commas, remove decimals, or shorten large values (e.g., 12K instead of 12,000).
• Adjust colors to improve contrast or highlight key data points – choose a color palette that suits your style or brand.
• Customize the styling – select fonts, font sizes, and layout options that make the report easy to read and visually appealing.

Looker Studio gives you full control over the look and feel of your report, so don’t hesitate to experiment. These small tweaks help make your final report more engaging and professional.

What's Next? Build on What You've Learned

You’ve just completed a real SQL project – from writing a query to building a live report. That’s a solid accomplishment.

As you get more comfortable with SQL and reporting tools, try taking your projects a step further. Here are a few ideas to explore next:

• Analyze crimes by district or neighborhood to identify hotspots.
• Check arrest rates by crime type or location.
• Compare trends over time to see how crime patterns shift month to month or year to year.

These kinds of insights are exactly what professionals deliver in real reporting roles. If you're ready to learn how to structure more advanced queries and reports, the SQL Reporting track on LearnSQL.com is a great next step. It’s designed to help you turn raw data into actionable insights using real-world reporting techniques.

You can also explore other hands-on courses at LearnSQL.com to sharpen your skills – whether you're just starting out or aiming to take your SQL to the next level.

Start simple, keep exploring – every project grows your SQL skills.