Assignment 3: SQL

Resources

An SQL Reference (pdf)

video: Analysis in Carto

video: SQL in Carto

Carto has numerous tutorials on working with data and SQL.

If you want to learn more about SQL in general (outside of Carto and mapping), SQL School is a good place to start.

Please create a post in Piazza for any technical questions.

Part 1: SQL

Write at least five different SQL statements for datasets in your Carto account. Base them on a variety of the queries under Common Queries in the SQL Reference. Some should be mappable and some should be unmappable (eg, selecting the COUNT of a table, since that cannot be mapped).

Submit:

• the SQL that you performed,
• what you wanted to find with the SQL, and
• the output of the SQL, either a screenshot of the resulting dataset or of the map, as appropriate.

Part 2: SQL, Inside Airbnb

Using SQL and data from Inside Airbnb for the city of your choice (find the city, then download the listings.csv) answer the following questions:

1. How many Airbnb listings are there in the city you chose?
2. How many listings are there in each neighborhood?
3. What is the average price for an listing?
4. Make a map where you filter the listings somehow (for example, showing listings with "quaint" in their name versus those with "cozy" in the name).

Part 3: Readings

This week we briefly look at the often-contentious realm of crime mapping:

• Take a look at the NYPD's official crime map and data dashboard, CompStat 2.0. For some context, either read this take predictive policing or listen to a few episodes of the podcast Reply All (part I, part II).
  
• Many crime maps focus on violent or drug-related crimes, but what if you were concerned about white collar crimes happening in the neighborhood? Maybe you'll find the White Collar Crime Risk Zones map of use! Check out the map, and read through the methodology.
  

While we'll be talking about these next week in class, please do respond on Piazza.

Part 4: HTML Preparation

Finally, next week we will begin talking about HTML. Please create a (free) GitHub account before coming to class. Unlike Carto, it doesn't matter if you use your New School email account or not--up to you. You don't have to do anything with it yet, just be prepared to log in when you need to.

Submitting your work

• Part 1: submit SQL used and the resulting screenshots.
• Part 2: submit SQL used and the answers you received.

Please include a short paragraph response or summary of your work.