Query 1: For each Symbol starting with the letter “A” compute the lowest and highest trade price every minute for all trades between 9:30am and 4:00pm.
Query 2: For each Symbol starting with the letter “B” compute the standard deviation of the trade prices for each hour of the day between 9:30am and 4:00pm.
CREATE EXTERNAL TABLE trades_full
(trading_date_time TIMESTAMP,
network CHAR(1),
message_category CHAR(1),
message_type CHAR(1),
message_sequence BIGINT,
market_exchange CHAR(1),
symbol VARCHAR(10),
trade_price DOUBLE,
trade_size BIGINT,
trade_conditions VARCHAR(6),
trade_conditions2 VARCHAR(6) )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LOCATION ‘gs://adam_manhattan/data/trades_full.csv’;
select minute(trading_date_time) from trades_full group by minute(trading_date_time);
LOAD DATA INFILE ‘/Users/vagrant/tasks-2016/sql-1000/trades_sample.csv’
INTO TABLE trades_full
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;
select symbol, DATE_FORMAT(trading_date_time,’%h:%i%p’) as Minute, max(trade_price) as Highest, min(trade_price) as Lowest from trades_full where symbol LIKE ‘A%’ and time(trading_date_time) between time(‘9:30′) and time(’16:00′) group by symbol, hour(trading_date_time),minute(trading_date_time);
select symbol, DATE_FORMAT(trading_date_time,’%h%p’) as Hour, STD(trade_price) as standard_deviation from trades_full where symbol LIKE ‘B%’ and time(trading_date_time) between time(‘9:30′) and time(’16:00’) group by symbol, hour(trading_date_time);