select * from user_actions;

--1.
select  extract(year from aggregate_date) "year", sum(ue.clicks) SumOfClicks, sum(ue.shows) SumOfShows from user_actions ue
group by  extract(year from aggregate_date)
order by "year";


--2.
select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
group by  extract(year from aggregate_date)
order by "year";

--3.
select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
where section='Sport'
group by  extract(year from aggregate_date)
order by "year";

--4.
select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
where section='Sport'
group by  extract(year from aggregate_date)
order by "year" desc;