Setup

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, appl_stocks.

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;