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.

select
  PRIMARY_DESCRIPTION as crime, count(PRIMARY_DESCRIPTION) as feq
from
  vay.`chicago/crimes-one-year-prior-to-present/rows.csv`
group
  by crime
order
  by feq desc
limit
  10
            

[
  {
    "crime": "THEFT",
    "feq": "61741"
  },
  {
    "crime": "BATTERY",
    "feq": "49289"
  },
  {
    "crime": "CRIMINAL DAMAGE",
    "feq": "26467"
  },
  {
    "crime": "ASSAULT",
    "feq": "20563"
  },
  {
    "crime": "DECEPTIVE PRACTICE",
    "feq": "17361"
  },
  {
    "crime": "OTHER OFFENSE",
    "feq": "16447"
  },
  {
    "crime": "NARCOTICS",
    "feq": "13794"
  },
  {
    "crime": "BURGLARY",
    "feq": "9485"
  },
  {
    "crime": "MOTOR VEHICLE THEFT",
    "feq": "8952"
  },
  {
    "crime": "ROBBERY",
    "feq": "7982"
  }
]
            
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!
vay logo

© Copyright 2019 Vay Technologies LLC.
All rights reserved.