DROP TABLE finalresult;

CREATE TABLE maxvalues AS
select s.symbol as symbol ,s.close as close,year(s.dt) as dt from stocks s
join (SELECT  s.symbol, max(s.dt) as max FROM stocks s
where s.symbol = "AA"
group by s.symbol,year(s.dt)) datemax
on (s.dt = datemax.max and s.symbol = datemax.symbol);

-- min Werte in Tabelle schreiben

CREATE TABLE minvalues AS

select s.symbol as symbol ,s.close as close,year(s.dt) as dt from stocks s

join  (SELECT  s.symbol,min(s.dt) as min FROM stocks s
where s.symbol = "AA"
group by s.symbol,year(s.dt)) datemin
on (s.dt = datemin.min
and s.symbol = datemin.symbol);

-- min und max mergen

CREATE TABLE mergedminmax AS
SELECT min.symbol, min.dt, min.close as min, max.close as max, ROUND((max.close/(min.close/100)-100),2) AS change
FROM minvalues min
JOIN maxvalues max
ON min.dt = max.dt;

CREATE TABLE finalresult AS
SELECT s.exc, s.symbol, min(s.close) as min, max(s.close) as max, m.change, year(s.dt)
FROM stocks s
JOIN mergedminmax m
ON s.symbol = m.symbol AND year(s.dt) = m.dt
GROUP BY s.exc, s.symbol, YEAR(s.dt),m.change;

-- hilfstabellen bereinigen

DROP TABLE maxvalues;
DROP TABLE minvalues;
DROP TABLE mergedminmax;

SELECT * FROM finalresult;