drop table if exists #tmp_test
select *
into #tmp_test
from (
select 'Wenlei' as student, 'math' as course, 4 as score, 1 as semester
union 
select 'Wenlei', 'statistics', 4 , 2
union 
select 'Wenlei', 'data science', 4.5, 3
union 
select 'Lisa', 'bioloy', 5, 1
union 
select 'Lisa', 'anatomy', null, 2  
union
select 'Brian', 'pharmacology', 4, 1
) a
select * from #tmp_test
order by student, semester


--group sum, max, min, count, size  
select *,
sum(score) over (partition by student ) as "sum", 
max(score) over (partition by student ) as "max",
min(score) over (partition by student ) as "min",
count(score) over (partition by student ) as "count",
count(*) over (partition by student ) as "count with null"
from #tmp_test
order by student, semester


--***** sum, ***** count 
select *,
sum(score) over (partition by student order by semester ) as cum_sum,
count(*)over (partition by student order by semester ) as cum_count
from #tmp_test
order by student, semester

--first value
select *,
first_value(score) over (partition by student order by semester ) as "first",
first_value(score) over (partition by student order by semester desc ) as "last"
from #tmp_test
order by student, semester


--lag/lead
select *,
lag(score) over  (partition by student order by semester ) as "lag",
lead(score) over  (partition by student order by semester ) as "lead"
from #tmp_test
order by student, semester

--rank, dense_rank, row_number 
select *,
row_number() over  (partition by student order by score desc ) as "row_nubmer",
rank() over  (partition by student order by score desc ) as "rank",
dense_rank() over  (partition by student order by score desc ) as "dense_rank"
from #tmp_test
order by student, semester


CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)


select * from product


SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST