/* cleanup
use role sysadmin; 
drop database test_db;
*/
use role sysadmin; 
create database test_db;

CREATE OR REPLACE VIEW orders_vw
AS
SELECT n_name AS Nation
      ,c_mktsegment AS Segment
      ,year(o_orderdate) AS Year
      ,month(o_orderdate) AS Month
      ,quarter(o_orderdate) AS Quarter
      ,sum(o_totalprice) AS TotalPrice
      ,count(distinct c_custkey) AS CntCustomers
FROM snowflake_sample_data.tpch_sf1.orders 
JOIN snowflake_sample_data.tpch_sf1.customer ON o_custkey = c_custkey
JOIN snowflake_sample_data.tpch_sf1.nation   ON n_nationkey = c_nationkey
GROUP BY Nation, Segment, Year, Month, Quarter;

SELECT * FROM orders_vw;

-- Pivot 
-- Advantages and benefits of the data pivot:
-- • Query result is more readable once presented in a crosstabular format.
-- • A way to transform multiple rows into a fewer rows, typically with more columns.

--(Snowflake - documentation)
/*
SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )
[ ... ]
*/

-- 1)
SELECT *
FROM
(SELECT Segment, Quarter, TotalPrice FROM orders_vw) 
PIVOT (SUM(TotalPrice) 
    FOR Quarter IN (1, 2, 3, 4));

-- Grouping Sets
-- The GROUPING SETS is an extension of GROUP BY. It is used to define multiple groupings.
-- 1)
GROUP BY GROUPING SETS (a, b, c)
GROUP BY GROUPING SETS ((a), (b), (c))
--Is equivalent to:
GROUP BY a
UNION ALL
GROUP BY b
UNION ALL
GROUP BY c
-----
GROUP BY GROUPING SETS (a, (b, c))
--Is equivalent to:
GROUP BY a
UNION ALL
GROUP BY b, c
-----
GROUP BY GROUPING SETS (a, b,())
GROUP BY GROUPING SETS ((a), (b),())
-- Is equivalent to:
GROUP BY a
UNION ALL
GROUP BY b
UNION ALL
GROUP BY () 

-- 2)
SELECT nation, year, sum(CntCustomers)  
FROM orders_vw
GROUP BY GROUPING SETS(nation, year);

-- 3)
SELECT nation, year, sum(CntCustomers)  
FROM orders_vw
GROUP BY GROUPING SETS((nation, year));

-- 4) 
SELECT nation, year, sum(CntCustomers)  
FROM orders_vw
GROUP BY GROUPING SETS((nation, year),());

-- 5)
SELECT Segment, "1", "2", "3", "4", "1"+ "2"+ "3"+ "4" AS Summary
FROM
(SELECT Segment, Quarter, TotalPrice FROM orders_vw) 
PIVOT (SUM(TotalPrice) 
    FOR Quarter IN (1, 2, 3, 4));
    
-- 5.1)
SELECT Segment, SUM("1") AS "1", SUM("2") AS "2", SUM("3") AS "3", SUM("4") AS "4", 
                SUM("1"+ "2"+ "3"+ "4") AS Summary
FROM
(SELECT Segment, Quarter, TotalPrice FROM orders_vw) 
PIVOT (SUM(TotalPrice) 
    FOR Quarter IN (1, 2, 3, 4))
GROUP BY GROUPING SETS (Segment,());

-- Which NULL came from a subtotals and which is a stored NULL value? 
-- GROUPING
--(Snowflake - documentation)
/*
GROUPING( <expr1> [ , <expr2> , ... ] )

GROUPING(expr) returns 0 for a row that is grouped on expr, and 1 for a row that is not grouped on expr
*/

-- 6)
SELECT Segment, SUM("1") AS "1", SUM("2") AS "3", SUM("3") AS "3", SUM("4") AS "4", 
                SUM("1"+ "2"+ "3"+ "4") AS Summary,
                GROUPING(Segment)
FROM
(SELECT Segment, Quarter, TotalPrice FROM orders_vw) 
PIVOT (SUM(TotalPrice) 
    FOR Quarter IN (1, 2, 3, 4))
GROUP BY GROUPING SETS (Segment,());

-- 7)
SELECT CASE GROUPING(Segment)
       WHEN 1 THEN 'Grand Total'
       ELSE Segment
       END AS Segment,
       SUM("1") AS "1", SUM("2") AS "2", SUM("3") AS "3", SUM("4") AS "4", 
                SUM("1"+ "2"+ "3"+ "4") AS Summary
FROM
(SELECT Segment, Quarter, TotalPrice FROM orders_vw) 
PIVOT (SUM(TotalPrice) 
    FOR Quarter IN (1, 2, 3, 4))
GROUP BY GROUPING SETS (Segment,());


-- Pivoting data without a PIVOT clause
-- Cross-tabular format could be achieved by using the following template
/*
SELECT <row-data-columns>,
       <aggregate-function>(CASE WHEN <pivot-column> = <value 1> THEN <column> END),
       <aggregate-function>(CASE WHEN <pivot-column> = <value 2> THEN <column> END),
       …
       <aggregate-function>(CASE WHEN <pivot-column> = <value n> THEN <column> END),
       <aggregate-function>(<column>)
FROM <table-expr>
GROUP BY GROUPING SETS((<row-data-columns>), ());
*/

-- 8)
SELECT Segment
      ,SUM(CASE WHEN Quarter = 1 THEN TotalPrice END) AS Q1
      ,SUM(CASE WHEN Quarter = 2 THEN TotalPrice END) AS Q2
      ,SUM(CASE WHEN Quarter = 3 THEN TotalPrice END) AS Q3
      ,SUM(CASE WHEN Quarter = 4 THEN TotalPrice END) AS Q4
      ,SUM(TotalPrice) AS summary
FROM (SELECT Segment, Quarter, TotalPrice FROM orders_vw)
GROUP BY GROUPING SETS((Segment), ());

--
CREATE VIEW orders_calendar_quarter_vw
AS
SELECT Nation
      ,Year
      ,Month
      ,sum(Totalprice) as sum_totalprice
FROM orders_vw
GROUP BY Nation, Year, Month;

select * From orders_calendar_quarter_vw;

-- Window Functions
/* Snowflake documentation
<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] ) --
The OVER clause specifies that the function is being used as a window function.

The PARTITION BY sub-clause allows rows to be grouped into sub-groups, for example by city, by year, etc. 
The PARTITION BY clause is optional. You can analyze an entire group of rows without breaking it into sub-groups.

The ORDER BY clause orders rows within the window. 

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

*/
-- 9) 
SELECT Nation, Year, Month, sum_totalprice,
      SUM(sum_totalprice) OVER () AS total
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;

-- 10)
SELECT Nation, Year, Month, sum_totalprice,
      SUM(sum_totalprice) OVER (PARTITION BY nation) AS total
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995
ORDER BY Nation, Month;

-- 11)
SELECT Nation, Year, Month, sum_totalprice,
      SUM(sum_totalprice) OVER (ORDER BY month
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                 AND CURRENT ROW) AS run_total
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;

-- 12) 
SELECT Nation, Year, Month, sum_totalprice,
      AVG(sum_totalprice)  OVER (ORDER BY month
                                ROWS BETWEEN 1 PRECEDING
                                         AND 1 FOLLOWING) as moving_average
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;

-- Access to more than one row of a table
-- 13) 
SELECT Nation, Year, Month, sum_totalprice,
      LAG(sum_totalprice,1)  OVER (ORDER BY month) AS lag_sum
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;

-- 14) 
SELECT Nation, Year, Month, sum_totalprice,
      LAG(sum_totalprice,1)  OVER (ORDER BY month) AS lag_sum,
      LEAD(sum_totalprice,1)  OVER (ORDER BY month) AS lead_sum
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;

-- 15) 
SELECT Nation, Year, Month, sum_totalprice,
      LAG(sum_totalprice,1)  OVER (ORDER BY month) AS lag_sum,
      LEAD(sum_totalprice,1)  OVER (ORDER BY month) AS lead_sum,
      FIRST_VALUE(sum_totalprice)  OVER (ORDER BY month) AS first_sum,
      LAST_VALUE(sum_totalprice)  OVER (ORDER BY month) AS last_sum
FROM orders_calendar_quarter_vw
WHERE YEAR = 1995 AND nation = 'GERMANY'
ORDER BY month;