| Team Fly | |
round((sum(s.quantity_sold) -
lag(sum(s.quantity_sold),1)
over (partition by p.prod_category, c.channel_desc
order by t.calendar_year)) /
lag(sum(s.quantity_sold),1)
over (partition by p.prod_category, c.channel_desc
order by t.calendar_year) *
100 ,2) as percent_chng
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
and s.channel_id = c.channel_id
and s.time_id = t.time_id
group by p.prod_category, c.channel_desc, t.calendar_year;
2. Select from the sales_trends view using the following listing. Notice that quantity_sold and percent_chng reset after each channel_desc. This is a result of the lag function's partition by clauses in the view definition.
select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics'
order by prod_category, channel_desc, year;
3. Select from the sales_trends view using the following listing that contains a model clause. In this query, we are projecting quantity_sold and percent_chng according to the following rules:
a. Filter the prod_category to only select 'Electronics'.
b. Project for years 2002 to 2006 inclusive.
c. The projected quantity_sold is calculated as the previous year's value plus the average percent_chng over the previous three years.
d. The projected percent_chng is the average percent_chng over the previous three years.
select prod_category, channel_desc, year, quantity_sold, percent_chng
from sales_trends
where prod_category = 'Electronics'
model
partition by (prod_category, channel_desc)
dimension by (year)
measures (quantity_sold, percent_chng)
rules (
percent_chng[for year from 2002 to 2006 increment 1] =
round(avg(percent_chng)[year between currentv()-3 and
currentv()-1], 2),
| Team Fly | |