database.psql

create table observations(id serial primary key not null , observed_datetime timestamp default current_timestamp , market_open_datetime timestamp not null , market_close_datetime timestamp not null , symbol char(5) not null , num_new_highs int not null , num_new_lows int not null, num_highs_abv_avg_vol int not null , num_highs_blw_avg_vol int not null , num_lows_abv_avg_vol int not null , num_lows_blw_avg_vol int not null , num_new_highs_am int not null , num_new_highs_pm int not null , num_new_lows_am int not null , num_new_lows_pm int not null , percent_change real not null , tallest_green_candlestick real not null , tallest_red_candlestick real not null , age_recent_news int not null , next_market_open_margin real, next_open_up int);

-- insert into observations(symbol , num_new_highs , num_new_lows , num_highs_abv_avg_vol , num_highs_blw_avg_vol , num_lows_abv_avg_vol , num_lows_blw_avg_vol  , num_new_highs_am , num_new_highs_pm , num_new_lows_am , num_new_lows_pm , percent_change , tallest_green_candlestick , tallest_red_candlestick , age_recent_news) values ('AAPL', 12, 23, 2, 56, 34, 2, 36, 8, 56 , 25, .0035, 31.14, 23.4, 28);

SELECT * FROM observations WHERE market_open_datetime >= '2017-09-25';
DELETE FROM observations WHERE age_recent_news=8760;

UPDATE observations
SET next_open_up = 
CASE 
	WHEN next_market_open_margin<-0.05 THEN -6
    WHEN next_market_open_margin>=-0.05 AND next_market_open_margin<-0.04 THEN -5
    WHEN next_market_open_margin>=-0.04 AND next_market_open_margin<-0.03 THEN -4
    WHEN next_market_open_margin>=-0.03 AND next_market_open_margin<-0.02 THEN -3
    WHEN next_market_open_margin>=-0.02 AND next_market_open_margin<-0.01 THEN -2
    WHEN next_market_open_margin>=-0.01 AND next_market_open_margin<0 THEN -1
    WHEN next_market_open_margin>=0 AND next_market_open_margin<0.01 THEN 0
    WHEN next_market_open_margin>=0.01 AND next_market_open_margin<0.02 THEN 1
    WHEN next_market_open_margin>=0.02 AND next_market_open_margin<0.03 THEN 2
    WHEN next_market_open_margin>=0.03 AND next_market_open_margin<0.04 THEN 3
    WHEN next_market_open_margin>=0.04 AND next_market_open_margin<0.05 THEN 4
    WHEN next_market_open_margin>=0.05 THEN 5
END;