Your first query
This guide will walk you through the basics creating your first reactive query. Whether you're a seasoned data scientist or have never written a SQL query, this this is a great place to start.
1 Navigate to the query editor
If you have not created a free account, please do so now. After logging in, navigate to the query editor on the left hand sidebar,
or click here.
You will be greeted with a friendly SQL editor to start writing your queries.
2 Get some sample data
When writing a new query, it is helpful to understand the data that you are working with.
For the purpose of this guide, we will be looking at Chicago crime data in the past year.
To get an idea of what the data looks like, we will retrieve a couple of rows from the table.
Specifically, the query below gets the first 25 rows in the table.
If you are looking for a more detailed tutorial on SQL, we recommend the
W3 School's guide
. Copy and paste the following query into your editor and click Run.
select * from vay.`chicago/crimes-one-year-prior-to-present/rows.csv` limit 25
After the query has finished processing, you will see 25 rows appear below the query editor. As you can see, there is information about the type of crime, where it happened, and much more. Now let's turn all of this data into some more digestible information.
3 Extracting information
As you can see from step 2, it is straight forward to get raw data from the dataset. But what if we wanted to extract more useful information from it?
Let's say we want to know what the most common crimes are in Chicago, easy! The following query gets the top 10 crimes and sorts them based on how frequent they occur.
PRIMARY_DESCRIPTION as crime, count(PRIMARY_DESCRIPTION) as feq
by feq desc
"crime": "CRIMINAL DAMAGE",
"crime": "DECEPTIVE PRACTICE",
"crime": "OTHER OFFENSE",
"crime": "MOTOR VEHICLE THEFT",
The query shows us that the most common crime is theft with 61,741 occurrences, followed by battery and criminal damage.
4 Making the query reactive
By making this query reactive, it will update every time new crime data is available. Thus, the insight provided by the query (top crimes committed in Chicago), will always be the most up-to-date. To create the reactive query, simply click the button next to "Run" that says "Create". To learn more about reactive queries,
check out their overview