1. Use aggregate functions to look at key statistics about the data (e.g., min, max, average).

SELECT ROUND(AVG(price), 2) AS average_price, 
       ROUND(MIN(price), 2) AS min_price, 
       ROUND(MAX(price), 2) AS max_price 
FROM stocks;

At the end of my last post, I mentioned that it may be possible that I picked… well expensive stocks. at an average cost of nearly $190 per share, I’d definitely say these aren’t cheap!

2. Group the data by stock and repeat. How do the stocks compare to each other?

So, what’s the variability in stock price within each stock?

SELECT name, 
       ROUND(AVG(price), 2) AS average_price, 
       ROUND(MIN(price), 2) AS min_price, 
       ROUND(MAX(price), 2) AS max_price 
FROM stocks
GROUP BY name
ORDER BY average_price;

So, EA’s stock is lower than all the others and never even overlaps the stock price of any of the other stocks. On the other end of the spectrum is Boeing - more expensive than all the others by leaps over $100 dollars a share! Spotify and Nvidia are closer in price to each other than Nvidia and Apple but Spotify isn’t as expensive as Nvidia.

3. Group the data by day or hour of day. Does day of week or time of day impact prices?

SELECT date, 
       ROUND(AVG(price), 2) AS average_price
FROM stocks
GROUP BY date
ORDER BY average_price DESC;

Well, I don’t know about day of the week but it does look like that things were looking particularly good at the beginning of may!

4. Which of the rows have a price greater than the average of all prices in the dataset?

SELECT *
FROM stocks
WHERE price > 189.5;

A lot of rows. A lot of rows have a price greater than the average price (110 to be precise). If you look at the table from challenge 2, you’ll see that the only stocks above the average are from Apple and Boeing.

1. Calculate other key statistics, like median or variance.

SELECT AVG((stocks.price - sub.a) * (stocks.price - sub.a)) AS variance
FROM stocks, 
    (SELECT AVG(price) AS a 
     FROM stocks) AS sub;

SELECT price AS median
FROM stocks
ORDER BY price
LIMIT 1
OFFSET (SELECT COUNT(price)
        FROM stocks) / 2;

2. Refactor the data into 2 tables

We’ll refactor the stocks table into stock_info to store general info about the stock itself (ie. symbol, name) and stock_prices to store the collected data on price (ie. symbol, date, price).

CREATE TABLE stock_info
AS SELECT DISTINCT symbol, name 
   FROM stocks;

CREATE TABLE stock_prices
AS SELECT symbol, date, price
   FROM stocks;

SELECT *
FROM stock_info;

SELECT *
FROM stock_prices;

3. Now join the 2 tables in order to view more information on the stock with each row of price.

SELECT stock_prices.symbol, date, price, name
FROM stock_prices
LEFT JOIN stock_info ON stock_info.symbol = stock_prices.symbol;

Awesome! Now on to the next set of SQL challenges in another post!

DROP TABLE stock_prices;

DROP TABLE stock_info;