/*
Vytvořte pohled CV2_1 se sloupci channnel_desc, country_iso_code, sales ,poradi1, poradi2 pro zobrazení objemů 
prodeje v kanálu „Internet“ v prvním čtvrtletí roku 2000 pro všechny země a tyto informace doplňte pořadím.
Pohled vytvořte ve svém schématu v instanci oracle10 na serveru SQL101. K tabulkám přistupujte přes public_link
atdstest_sh Objemy prodeje zaokrouhlete na celá čísla.                  
*/

-- 1]
create or replace view CV2_1 as
with sum_sales as(
select channel_desc channel, country_iso_code code, sum(amount_sold) as total
FROM sales @atdstest_sh
join customers @atdstest_sh on sales.cust_id = customers.cust_id
join countries @atdstest_sh on customers.country_id = countries.country_id
join channels @atdstest_sh on channels.channel_id = sales.channel_id
where extract(year from time_id) = 2000 and extract(month from time_id) < 4
and channel_id in (select channel_id from channels @atdstest_sh where lower(channel_desc) = 'internet') 
group by channel_desc, country_iso_code 
) 
SELECT channel, code,  
total,
RANK() OVER (order by total DESC NULLS LAST) poradi1,
DENSE_RANK() OVER (order by total DESC NULLS LAST) poradi2
from sum_sales

-- 2]
create or replace view CV2_2 as with sum_sales as 
(select channel_desc channel, country_iso_code code, sum(amount_sold) as total
FROM sales @atdstest_sh
join customers @atdstest_sh on sales.cust_id = customers.cust_id
join countries @atdstest_sh on customers.country_id = countries.country_id
join channels @atdstest_sh on channels.channel_id = sales.channel_id
where extract(year from time_id) = 2000 and extract(month from time_id) < 4
and channel_id in (select channel_id from channels @atdstest_sh where lower(channel_desc) = 'internet') 
group by channel_desc, country_iso_code ) 
SELECT channel, code,  
total,
RANK() OVER (order by total DESC NULLS LAST) poradi1,
DENSE_RANK() OVER (order by total DESC NULLS LAST) poradi2,
round(CUME_DIST() OVER (ORDER BY total DESC ), 3) a_cume_dist,
round(PERCENT_RANK() OVER (ORDER BY total DESC ), 3) a_percent_rank,
NTILE(4) OVER (ORDER BY total DESC ) a_ntile
from sum_sales


/* -- 3]
Vytvořte pohled CV2_3 se sloupci channnel_desc, day, sales, best_day, worst_day,  better_calday, worse_caldaykterý ke každému dni a každému prodejnímu kanálu v listopadu 2000 zobrazí datum dne v daném měsíci, který byl z hlediska objemu prodeje v daném prodejním kanálu nejlepší a nejhorší. Dále uveďte sloupce, v nichž bude zobrazen z hlediska objemů prodeje ve stejném kalendářním dnu v týdnu, jako je daný den, první den následující horší a předchozí den lepší z hlediska finančního objemu prodeje. 
	Uvažujte pouze zemi ‘US’.
	Objemy prodeje zaokrouhlete na celá čísla.
*/
  SELECT chan.channel_desc, tim.time_id, ROUND(SUM(amount_sold)) sales,    
    FIRST_VALUE(tim.time_id) OVER (PARTITION BY chan.channel_desc ORDER BY SUM(sal.amount_sold) DESC) best_day,
    FIRST_VALUE(tim.time_id) OVER (PARTITION BY chan.channel_desc ORDER BY SUM(sal.amount_sold) ASC) worst_day,
    LEAD(tim.time_id) OVER (PARTITION BY tim.day_number_in_week, chan.channel_desc ORDER BY SUM(sal.amount_sold) ASC) better_calday,
    LAG(tim.time_id) OVER (PARTITION BY tim.day_number_in_week, chan.channel_desc ORDER BY SUM(sal.amount_sold) ASC) worse_calday
  FROM sales@atdstest_sh sal, customers@atdstest_sh cus, channels@atdstest_sh chan, countries@atdstest_sh ctr, times@atdstest_sh tim
  WHERE sal.cust_id = cus.cust_id AND sal.channel_id = chan.channel_id
  AND sal.time_id = tim.time_id AND cus.country_id = ctr.country_id
  AND tim.calendar_month_desc IN ('2000-11') AND ctr.country_iso_code = 'US'
  GROUP BY chan.channel_desc, tim.time_id, tim.day_number_in_week
  ORDER BY chan.channel_desc, sales DESC;
  
/* --4] 
Vytvořte pohled CV2_4 se sloupci channnel_desc, week, sales, a_ratio, který ke každému týdnu v listopadu a prosinci 1999 v rámci jednotlivých distribučních kanálů uvede pro každý týden a daný kanál procentuální podíl (RATIO_TO_REPORT) finančních objemů prodeje daného týdne na celkovém objemu obchodu v daném měsíci v daném kanálu. Řešení omezte na zemi CA. Objemy prodeje zaokrouhlete na celá čísla.
*/  
  SELECT chan.channel_desc, tim.week_ending_day week, ROUND(SUM(amount_sold)) sales,
    ROUND(SUM(sal.amount_sold) / SUM (SUM(sal.amount_sold)) OVER (PARTITION BY chan.channel_desc) * 100, 1) a_ratio
  FROM sales@atdstest_sh sal, customers@atdstest_sh cus, channels@atdstest_sh chan, countries@atdstest_sh ctr, times@atdstest_sh tim
  WHERE sal.cust_id = cus.cust_id AND sal.channel_id = chan.channel_id AND sal.time_id = tim.time_id
  AND cus.country_id = ctr.country_id AND tim.calendar_month_desc IN ('1999-11', '1999-12') AND ctr.country_iso_code = 'CA'
  GROUP BY chan.channel_desc, tim.week_ending_day
  ORDER BY chan.channel_desc, tim.week_ending_day;  
  
/* --5]
Vytvořte pohled CV2_5 se sloupci month, sales, max_10pct_minus, between_10_50pct_plus, který pro jednotlivé měsíce roku 2001 vypíše objem obchodu celkem za všechny kanály a pro jednotlivé měsíce uvede, kolik měsíců mělo maximálně o 10% menší objem obchodu než byl objem obchodu v daném měsíci a kolik měsíců o 10 až 50% větší objem obchodu než byl objem obchodu v daném měsíci .Řešení omezte na zemi CA.
*/  
  
  SELECT tim.calendar_month_desc MONTH, ROUND(SUM(sal.amount_sold)) sales,
    COUNT(*) OVER (ORDER BY SUM(sal.amount_sold) RANGE BETWEEN UNBOUNDED PRECEDING AND (SUM(sal.amount_sold)*0.1) PRECEDING ) max_10pct_minusxx,
    COUNT(*) OVER (ORDER BY SUM(sal.amount_sold) RANGE BETWEEN (SUM(sal.amount_sold)*0.1) PRECEDING AND (SUM(sal.amount_sold)*0.000000000001) PRECEDING ) max_10pct_minus,
    COUNT(*) OVER (ORDER BY SUM(sal.amount_sold) RANGE BETWEEN (SUM(sal.amount_sold)*0.1) FOLLOWING AND (SUM(sal.amount_sold)*0.5) FOLLOWING) between_10_50pct_plus
  FROM sales@atdstest_sh sal, customers@atdstest_sh cus, channels@atdstest_sh chan, countries@atdstest_sh ctr, times@atdstest_sh tim
  WHERE sal.cust_id = cus.cust_id AND sal.channel_id = chan.channel_id AND sal.time_id = tim.time_id
  AND cus.country_id = ctr.country_id AND tim.CALENDAR_YEAR = 2001 AND ctr.country_iso_code = 'CA'
  GROUP BY tim.calendar_month_desc
  ORDER BY tim.calendar_month_desc;
  
  
--- dal nevim  
  
  
  
  
  
  
  
  
  
   
1) 15 záznamů (stejný výpis)
2) 15 záznamů (stejný výpis)
3) 89 záznamů
4) 30 záznamů (stejný výpis)
5) 12 záznamů



4]
    CHANNEL_DESC                                                 WEEK                   SALES                  A_RATIO               
    ------------------------------------------------------------ ---------------------- ---------------------- ----------------------
    Direct Sales                                                 44                     4109                   14,72                 
    Direct Sales                                                 45                     2942                   10,54                 
    Direct Sales                                                 46                     16319                  58,46                 
    Direct Sales                                                 47                     3761                   13,47                 
    Direct Sales                                                 48                     539                    2,26                   
    Direct Sales                                                 48                     782                    2,8                   
    Direct Sales                                                 49                     3740                   15,67                 
    Direct Sales                                                 50                     4968                   20,82                 
    Direct Sales                                                 51                     10226                  42,85                 
    Direct Sales                                                 52                     4394                   18,41                 
    Internet                                                     44                     516                    24,63                 
    Internet                                                     45                     686                    32,74                 
    Internet                                                     46                     430                    20,53                 
    Internet                                                     47                     343                    16,37                 
    Internet                                                     48                     120                    5,73                   
    Internet                                                     48                     209                    4,56                   
    Internet                                                     49                     803                    17,54                 
    Internet                                                     50                     1102                   24,07                 
    Internet                                                     51                     1871                   40,86                 
    Internet                                                     52                     594                    12,97                 
    Partners                                                     44                     1212                   7,16                   
    Partners                                                     45                     970                    5,73                   
    Partners                                                     46                     9621                   56,85                 
    Partners                                                     47                     4164                   24,61                 
    Partners                                                     48                     955                    5,64                   
    Partners                                                     48                     273                    1,56                   
    Partners                                                     49                     4959                   28,34                 
    Partners                                                     50                     3960                   22,63                 
    Partners                                                     51                     6321                   36,13                 
    Partners                                                     52                     1983                   11,33                 

    30 rows selected    


5]
    MONTH    SALES                  MAX_10PCT_MINUS        BETWEEN_10_50PCT_PLUS 
    -------- ---------------------- ---------------------- ----------------------
    2001-01  56653                  0                      8                     
    2001-02  50060                  0                      4                     
    2001-03  62856                  2                      8                     
    2001-04  75303                  1                      2                     
    2001-05  58990                  1                      8                     
    2001-06  73752                  0                      3                     
    2001-07  81234                  5                      0                     
    2001-08  83789                  4                      0                     
    2001-09  75473                  2                      2                     
    2001-10  76349                  3                      1                     
    2001-11  76940                  4                      1                     
    2001-12  87820                  2                      0                     

    12 rows selected

--max(amount_sold) maximum
--(select sum() from salary) maximum
--COUNT(*) OVER (PARTITION BY channel_id, time_id) pocet   
    
natural join channels @atdstest_sh
natural join customers @atdstest_sh
natural join products @atdstest_sh
natural join promotions @atdstest_sh
natural join times @atdstest_sh    

--(select amount_sold
--from sales @atdstest_sh s2
--where s2.CHANNEL_ID = sa.channel_id
--and s2.time_id = time_id
--and s2.prod_id = prod_id
--and s2.cust_id = cust_id
--and s2.promo_id = promo_id
--) am

--and prod_id = 117 and channel_id = 2 and cust_id = 8452


-- jde
select s1.time_id, s1.channel_id, s1.cust_id, s1.prod_id, s1.amount_sold
from sales @atdstest_sh s1, channels @atdstest_sh, customers @atdstest_sh, 
products @atdstest_sh, promotions @atdstest_sh, times @atdstest_sh, countries @atdstest_sh
where s1.prod_id = products.prod_id
and s1.channel_id = channels.channel_id
and s1.cust_id = customers.cust_id
and countries.country_id = customers.country_id
and s1.time_id = times.time_id
and s1.promo_id = promotions.promo_id
and s1.channel_id = 2
and DAY_NUMBER_IN_MONTH = 1
and CALENDAR_YEAR = 2000 
and CALENDAR_MONTH_NUMBER = 11 
and country_iso_code = 'US'
--

-- taky
select time_id, channel_id, cust_id, prod_id, amount_sold
from sales @atdstest_sh s1
join products @atdstest_sh using (prod_id)
join channels @atdstest_sh using (channel_id)
join times @atdstest_sh using (time_id)
join customers @atdstest_sh using (cust_id)
join promotions @atdstest_sh using (promo_id)
join countries @atdstest_sh on customers.country_id = countries.country_id
where CHANNEL_ID = 2
and DAY_NUMBER_IN_MONTH = 1
and CALENDAR_YEAR = 2000 
and CALENDAR_MONTH_NUMBER = 11 
and country_iso_code = 'US'
--

--where prod_id = s1.prod_id
--and cust_id = s1.cust_id
--and time_id = s1.time_id
--and channel_id = s1.channel_id
--and promo_id = s1.promo_id


select (select channel_desc from channels @atdstest_sh ch where sa.channel_id = ch.channel_id) channel_desc,
DAY_NUMBER_IN_MONTH, 
(select sum(amount_sold) from sales @atdstest_sh s2 
where s2.CHANNEL_ID = sa.channel_id and s2.time_id = time_id and s2.prod_id = prod_id
and s2.cust_id = cust_id and s2.promo_id = promo_id) suma
from sales @atdstest_sh sa
JOIN customers @atdstest_sh using(cust_id)
JOIN countries @atdstest_sh on countries.country_id = customers.country_id
JOIN times @atdstest_sh t2 using (time_id)

where CALENDAR_YEAR = 2000 and CALENDAR_MONTH_NUMBER = 11 and country_iso_code = 'US'
group by channel_id, DAY_NUMBER_IN_MONTH
order by channel_id, DAY_NUMBER_IN_MONTH


--extract(year from time_id) = 2000 and extract(month from time_id) = 11
--and country_id = (select country_id from countries @atdstest_sh where country_iso_code = 'US')


--group by channel_id, DAY_NUMBER_IN_MONTH
--(select distinct channel_desc from channels @atdstest_sh ch where sa.channel_id = ch.channel_id) channel_desc,


--natural JOIN customers @atdstest_sh --using(cust_id) 
--natural join channels @atdstest_sh --using(channel_id)
--JOIN countries @atdstest_sh on countries.country_id = customers.country_id
--natural JOIN times @atdstest_sh --t2 using (time_id)

select chan.channel_desc, tim.DAY_NUMBER_IN_MONTH,
--round(sum(amount_sold) over (partition by sal.channel_id, tim.DAY_NUMBER_IN_MONTH) ) sales,
FIRST_VALUE(tim.time_id) OVER (PARTITION BY chan.channel_desc ORDER BY sum(sal.amount_sold) DESC) best_day

from sales@atdstest_sh sal, customers@atdstest_sh cus, channels@atdstest_sh chan, countries@atdstest_sh ctr, times@atdstest_sh tim
where sal.cust_id = cus.cust_id and sal.channel_id = chan.channel_id and sal.time_id = tim.time_id and cus.country_id = ctr.country_id
and tim.calendar_month_desc in ('2000-11') --tim.CALENDAR_YEAR = 2000 and tim.CALENDAR_MONTH_NUMBER = 11 
and ctr.country_iso_code = 'US' 

group by chan.channel_desc, tim.time_id, tim.DAY_NUMBER_IN_MONTH,tim.day_number_in_week

order by chan.channel_desc, tim.DAY_NUMBER_IN_MONTH



--create or replace view CV2_5 as
select tim.calendar_month_desc month, round(sum(sal.amount_sold)) sales,

COUNT(*) OVER (ORDER BY sum(sal.amount_sold) RANGE BETWEEN UNBOUNDED PRECEDING AND (sum(sal.amount_sold)*0.1) PRECEDING ) max_10pct_minusxx,
COUNT(*) OVER (ORDER BY sum(sal.amount_sold) RANGE BETWEEN (sum(sal.amount_sold)*0.1) PRECEDING AND (sum(sal.amount_sold)*0.000000000001) PRECEDING ) max_10pct_minus,
COUNT(*) OVER (ORDER BY sum(sal.amount_sold) RANGE BETWEEN (sum(sal.amount_sold)*0.1) FOLLOWING AND (sum(sal.amount_sold)*0.5) FOLLOWING) between_10_50pct_plus 
from sales@atdstest_sh sal, customers@atdstest_sh cus, channels@atdstest_sh chan, countries@atdstest_sh ctr, times@atdstest_sh tim
where sal.cust_id = cus.cust_id and sal.channel_id = chan.channel_id and sal.time_id = tim.time_id and cus.country_id = ctr.country_id
and tim.CALENDAR_YEAR = 2001 and ctr.country_iso_code = 'CA' 
group by tim.calendar_month_desc
order by tim.calendar_month_desc