----find quarter sale in each state
select *,
sum(sale)over(partition by yyyy, region order  by yyyy,qqqq rows between unbounded preceding and current row) as cummulativesum,
sum(sale)over(partition by yyyy, region) as totalyearlysale,
lag(sale, 1, 0)over(partition by region order by region, yyyy, qqqq asc) as lastyearsale,
lead(sale, 1, 0)over(partition by region order by region, yyyy, qqqq asc) as nextyearsale,
percent_rank() over(partition by region, yyyy order by qqqq asc)
from
(
select region, extract(year from order_date) as yyyy
, extract(quarter from order_date) as qqqq
, round(sum(sales)) as sale
from orders_table
group by 1, 2, 3
)t1---first
where region in ('South','West') and
yyyy in ('2016','2017')