SQL context.sql

use stock_analysis;
create table if not exists user_ (Date DATETIME,Open FLOAT,High FLOAT,Low float,Close float,Adj_Close float, Volume INT);

-- Best return day
with cte1 as (
select *,(LAG(CLOSE) OVER(ORDER BY DATE)-close)/close as daily_Return
from aa
)
select date,round(daily_return,2) as Highest_return
from cte1
where daily_return = (select max(daily_return) from cte1);

-- Lowest return day
with cte2 as (
select *,(LAG(CLOSE) OVER(ORDER BY DATE)-close)/close as daily_Return
from aa
)
select date,round(daily_return,2) as Lowest_return
from cte2
where daily_return = (select min(daily_return) from cte2);

-- First Day which less than Average return day
with cte3 as (
select *,(LAG(CLOSE) OVER(ORDER BY DATE)-close)/close as daily_Return
from aal
)
select date,round(daily_return,5)
from cte3
where daily_return <= (select avg(daily_return) from cte3)
order by daily_return desc
limit 1;


-- Query for Volatility
with cte4 as (
select *, (lag(close) over(order by date) - close)/close as daily_return
from aal),
cte5 as (
select year(date) as year,round((std(daily_Return))*100,2) as volatility
from cte4
group by year(date)),
cte6 as (
select year,volatility,row_number() over(order by volatility) as rnk
from cte5)
select *
from cte6
where (rnk = 1) or (rnk = 2) or (rnk > (select count(*)-2 from cte5))
;

-- Yearly Returns
with cte9 as (
select year(date) as Year_,close
from aal
group by year(date),close),
cte10 as (
select *,row_number() over(partition by Year_) as rnk from cte9),
cte11 as (
select Year_,max(close) as highest_close
from cte10
group by Year_)
select Year_, (lag(highest_close) over(order by Year_) - highest_close)/highest_close as yearly_return
from cte11
limit 655555555555535 offset 1;


with cte as 
(select *,(LAG(CLOSE) OVER(ORDER BY DATE)-close)/close as daily_Return 
from aal),
cte12 as (
select date,round(daily_return,3)
from cte
where daily_return = (select max(daily_return) from cte) or 
(daily_return = (select min(daily_return) from cte))
order by daily_return desc
)
select * from cte12;

select * from aal;