Oracle Reporting 4 - Time Series Calculations

Query 2: Use lag function to put sales of both year 2000 and 2001 in one row:
WITH v AS
( 
SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year,
sum(s.amount_sold) sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND p.Prod_name in ('Y Box') AND
t.Calendar_Year in (2000,2001)       
GROUP BY p.Prod_Name, t.Calendar_Year
)
select * from (
select prod, calendar_year, sales, lag(sales,1) over(partition by v.prod order by v.calendar_year) last_year_sales
from v
)
where calendar_year=2001;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        146  consistent gets
          0  physical reads
          0  redo size
        761  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Query 2_1: Alternative, user could use decode to generate the same output. This is a well-known method to do row to column transformation.
select p.prod_name, sum(decode(t.calendar_year,2000, amount_sold,0)) "2000_sales",sum(decode(t.calendar_year,2001, amount_sold,0)) "2001_sales"
from sales s, times t, products p where s.time_id=t.time_id and s.prod_Id=p.prod_id
and t.calendar_year in (2000, 2001) and p.prod_name in ('Y Box') group by p.prod_name;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        146  consistent gets
          0  physical reads
          0  redo size
        687  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Autotrace estimates Query_2 and Query_2_1 have same performance.
What if the customer needs all years sales in one row? I could add another column using same method, either decode or lag. However, there's an elegant way to do this, using Pivot operation. 
Query 2_3 :
WITH v AS
( 
SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year,
sum(s.amount_sold) amount_sold
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND p.Prod_name in ('Y Box')
GROUP BY p.Prod_Name, t.Calendar_Year
order by t.calendar_year
)
select * from 
(select prod, calendar_year, amount_sold
from v) s pivot( sum(amount_sold)
for calendar_year in (1998 "1998_sales", 1999 "1999_sales",2000 "2000_sales", 2001 "2001_sales"));

Query 3: A data densified query, compares weekly cumulative sales of 2001 to that of 2000.
WITH v AS
( 
SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year Year,
t.Calendar_Week_Number Week, SUM(Amount_Sold) Sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND p.Prod_name in ('Y Box') AND
t.Calendar_Year in (2000,2001) AND
t.Calendar_Week_Number BETWEEN 30 AND 40
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number 
)
SELECT Prod , Year, Week, Sales,
Weekly_ytd_sales, Weekly_ytd_sales_prior_year
FROM (
SELECT Prod, Year, Week, Sales, Weekly_ytd_sales,
LAG(Weekly_ytd_sales, 1) OVER (PARTITION BY Prod , Week ORDER BY Year) Weekly_ytd_sales_prior_year
FROM
( SELECT v.Prod Prod , t.Year Year, t.Week Week,NVL(v.Sales,0) Sales, 
SUM(NVL(v.Sales,0)) OVER (PARTITION BY v.Prod , t.Year ORDER BY t.week) weekly_ytd_sales
FROM v 
PARTITION BY (v.Prod )
RIGHT OUTER JOIN 
(SELECT DISTINCT Calendar_Week_Number Week, Calendar_Year Year
FROM Times
WHERE Calendar_Year IN (2000, 2001)) t
ON (v.week = t.week AND v.Year = t.Year)
) dense_sales
) year_over_year_sales
WHERE Year = 2001 AND Week BETWEEN 30 AND 40
ORDER BY 1, 2, 3;