Working with stocks: Basic SQL
This is a continuation of my work on the codeacademy data science independant project #1: Watching the Stock Market. My first post is here.
In this project, the basic tasks are:
- What are the distinct stocks in the table?
- Query all data for a single stock. Do you notice any overall trends?
- Which rows have a price above 100? between 40 to 50, etc?
- Sort the table by price. What are the minimum and maximum prices?
I write this blog using rmarkdown and the entire project is powered by bookdown. SO there’s some setup that needs to take place to get it all to work.
library(DBI) db = dbConnect(RSQLite::SQLite(), dbname = "../../data/stocks/stocks.db")
What are the distinct stocks in the table?
SELECT DISTINCT name FROM stocks;
In my last post about putting together the database, I said that I was collecting data on stocks for Apple, Boeing, Spotify, EA, and Nvidia. Here’s the proof!
Query all data for a single stock. Do you notice any overall trends?
Alright! To analyze trends for the Apple stocks, we’ll plot them thorugh time. And, we’ll do it in R! First, I’ll run some SQL code to extract the APPL stocks. Silently, the output of the SQL query below will be returned as variable we can access in an R code chunk,
SELECT * FROM stocks WHERE name = 'Apple'
library(ggplot2) appl_stocks$date <- as.Date(appl_stocks$date, "%m/%d/%y") ggplot(appl_stocks, aes(x = date, y = price)) + geom_line()
Huh! Something happened in the beginning of June that was bad for Apple’s stock. Looks like it is taking about two months for the price to recover. Wonder what that is… My first guess was the anouncement of Johnny Ive, but I looked and that wasn’t until the end of June. My best guess is decline in iPhone sales coupled with some negative predictions of tech stocks.
Which rows have a price above 100? between 40 to 50, etc?
This just requires some simple filtering!
SELECT COUNT(*) FROM stocks WHERE price > 100;
Wow! That’s a few stocks!
SELECT COUNT(*) FROM stocks WHERE price >= 40 AND price <= 50;
Well, I picked pricy stocks…
Sort the table by price. What are the minimum and maximum prices?
These are the least expensive stocks
SELECT * FROM stocks ORDER BY price;
And these are the most expensive stocks.
SELECT * FROM stocks ORDER BY price DESC;