IDAS2_CV7_SPOLECNE

SELECT * FROM sh.sales;
SELECT * FROM sh.times;
SELECT * FROM sh.customers;
SELECT * FROM sh.products;
SELECT * FROM sh.countries;
SELECT * FROM sh.channels;
SELECT * FROM sh.promotions;
SELECT * FROM sh.costs;

CREATE OR REPLACE VIEW  cv7_cum_sum AS
SELECT
channel_desc,
calendar_month_name, calendar_year, amount_sold,
SUM(amount_sold) over
(PARTITION BY calendar_year, channel_desc ORDER BY calendar_year, calendar_month_number rows unbounded preceding) cum_sum
FROM
(
SELECT
channel_desc, calendar_month_name, calendar_year, calendar_month_number,SUM(amount_sold) amount_sold
FROM SH.sales
join sh.times using(time_id)
join sh.channels using(channel_id)
WHERE
calendar_year IN (2000,2001)
GROUP BY
channel_desc,
calendar_month_name,
calendar_year,
calendar_month_number);

SELECT * FROM cv7_cum_sum;

CREATE OR REPLACE VIEW cv7_sales_avg_4_days AS
SELECT
channel_desc,
time_id,
SUM(amount_sold) total_sold,
ROUND (AVG(SUM(amount_sold))over (PARTITION BY channel_desc ORDER BY
time_id rows 3 preceding),2) AS prev_4_day_avg,
MIN(SUM(amount_sold)) over (PARTITION BY channel_desc) min_per_channel,
MAX(SUM(amount_sold)) over (PARTITION BY channel_desc) max_per_channel
FROM
sh.sales
join sh.customers using(cust_id)
join sh.countries using(country_id)
join sh.times using (time_id)
join sh.channels using(channel_id)
join sh.promotions using(promo_id)
join sh.products using (prod_id)
WHERE
prod_name = 'Mouse Pad'
AND calendar_month_desc = '1999-01'
AND country_name = 'United States of America'
GROUP BY
channel_desc,
time_id;

SELECT * FROM cv7_sales_avg_4_days;

CREATE OR REPLACE VIEW cv7_sales_3_day_window AS
SELECT
time_id,
SUM(quantity_sold) total_quantity,
SUM(amount_sold) total_amount,
ROUND(AVG(SUM(quantity_sold))
over
(
ORDER BY time_id RANGE BETWEEN INTERVAL '1' DAY preceding AND INTERVAL '1' DAY following)
,2) qty_3day_avg,
ROUND(AVG(SUM(amount_sold))
over
(
ORDER BY time_id RANGE BETWEEN INTERVAL '1' DAY preceding AND INTERVAL '1' DAY following),2) amount_3day_avg
FROM
sh.sales
join sh.customers using(cust_id)
join sh.countries using(country_id)
join sh.times using(time_id)
WHERE
country_name = 'France'
AND (calendar_month_desc='1992-12'
OR calendar_month_desc = '2000-01')
GROUP BY time_id;

SELECT * FROM cv7_sales_3_day_window;