/* 1. dio */
CREATE TABLE Sales ( Country varchar(50), Region varchar(50), Sales int );
INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);
GO

-- Osnovno korištenje
select Country, Region, SUM(sales) AS TotalSales
from Sales
group by Country, Region

-- Izračun grand totala.
select SUM(sales) AS GrandTotal
from Sales

select SUM(sales) AS GrandTotal
from Sales
group by ()

-- GROUP BY ROLLUP
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by rollup (Country, Region)

-- GROUP BY CUBE
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by cube (Country, Region)

-- GROUP BY GROUPING SETS ()
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by grouping sets (Country, Region, ())

-- ZADACI.

-- 1.
select
	Prezime, Ime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Kupac as k on r.KupacID = k.IDKupac
where k.Prezime in ('Adams', 'Simmons')
group by rollup (k.Prezime, k.Ime)

-- 2.
select
	Prezime, Ime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Kupac as k on r.KupacID = k.IDKupac
where k.Ime in ('Rose', 'Lydia')
group by rollup (k.Ime, k.Prezime)

-- 3.
select
	kom.Ime, kom.Prezime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Komercijalist as kom on r.KomercijalistID = kom.IDKomercijalist
where kom.Prezime like 'A%'
group by cube (kom.Ime, kom.Prezime)

-- 4.
select
	p.Naziv, p.Boja, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Proizvod as p on s.ProizvodID = p.IDProizvod
where p.Boja is not null
group by GROUPING sets ((p.Naziv), (p.Boja), ())

-- 5.
select
	p.Boja, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Proizvod as p on s.ProizvodID = p.IDProizvod
where p.Boja is not null
group by GROUPING sets ((p.Boja), ())

-- 6.
select
	d.Naziv as Drzava, g.Naziv as Grad, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Racun as r on r.IDRacun = s.RacunID
inner join Kupac as k on k.IDKupac = r.KupacID
left join Grad as g on g.IDGrad = k.GradID
left join Drzava as d on d.IDDrzava = g.DrzavaID
group by rollup (d.Naziv, g.Naziv)

/* 2. dio */

-- Primjer funkcija rangiranja
select 
	Country, 
	Region,
	Sales,
	ROW_NUMBER() over (order by Sales) as Rn,
	RANK() over (order by Sales) as R,
	DENSE_RANK() over (order by Sales) as Dr,
	NTILE(2) over (order by Sales) as N
from Sales
order by Country, Region

-- Primjer funkcija rangiranja s particioniranjem
select 
	Country, 
	Region,
	Sales,
	ROW_NUMBER() over (partition by Country order by Sales) as Rn
from Sales

-- ZADACI.

-- 7.
select
	*,
	ROW_NUMBER() over (order by Prezime, Ime) as Rbr
from Kupac
order by Prezime, Ime

-- 8.
select
	*,
	ROW_NUMBER() over (partition by GradID order by Prezime, Ime) as Rbr
from Kupac
order by GradID, Prezime, Ime

-- 9.
select 
	*,
	RANK() over (order by Kolicina desc) as MjestoNaRangListi
from Stavka
order by Kolicina desc

-- 10.
select
	*,
	NTILE(13) over (order by DatumIzdavanja, BrojRacuna) as Skupina
from Racun
where KomercijalistID = 284
order by DatumIzdavanja

-- Primjer agregatnih funkcija
select 
	*,
	SUM(s.Kolicina) over ()
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

select 
	*,
	SUM(s.Kolicina) over (order by DatumIzdavanja)
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

select 
	*,
	SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja)
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

-- Primjer running totala.
select 
    Country, 
    Region,
    Sales,
    AVG(Sales) over (order by Region) as a1,
    SUM(Sales) over (order by Region) as a2
from Sales


-- Primjeri dodatnih okvira
select 
	s.Kolicina, r.datumizdavanja,
	SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja),
	SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja range between unbounded preceding and current row),
	SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja range between unbounded preceding and unbounded following),
	SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja rows between 1 preceding and 1 following)
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja



-- ZADACI.

-- 11.
select 
	s.*,
	r.DatumIzdavanja,
	SUM(s.UkupnaCijena) over (partition by year(DatumIzdavanja) order by DatumIzdavanja) as RunningSum
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

-- 12.
select
	*,
	COUNT(*) over (partition by Boja) as ProizvodaBoje
from Proizvod

-- 13.
select
	*,
	(MAX(CijenaBezPDV) over ()) - CijenaBezPDV as Razlika
from Proizvod
where CijenaBezPDV > 0
order by CijenaBezPDV

-- Primjeri analitičkih funkcija
select
	*,
	FIRST_VALUE(Naziv) over (order by Naziv) as a1
from Proizvod
where Boja is not null
order by Naziv

select
	*,
	FIRST_VALUE(Naziv) over (partition by Boja order by Naziv) as a1
from Proizvod
where Boja is not null
order by Boja, Naziv

select 
	*,
	LAG(Naziv, 3) over (order by IDProizvod) as a1
from Proizvod
where Boja is not null
order by IDProizvod
go

select 
	*,
	LAG(Naziv, 3) over (partition by Boja order by IDProizvod) as a1
from Proizvod
where Boja is not null
order by Boja, IDProizvod

-- ZADACI.

-- 14.
select 
	*,
	CijenaBezPDV - LAG(CijenaBezPDV, 1) over (order by CijenaBezPDV) as Delta
from Proizvod
where CijenaBezPDV > 0
order by CijenaBezPDV

-- 15.
create table VozniRed
(
	IDVozniRed int constraint PK_VozniRed primary key identity,
	Stanica nvarchar(50) not null,
	Polazak time not null
)
go
insert into VozniRed (Stanica, Polazak) values ('Zagreb', '11:00')
insert into VozniRed (Stanica, Polazak) values ('Dugo Selo', '11:35')
insert into VozniRed (Stanica, Polazak) values ('Kutina', '13:15')
insert into VozniRed (Stanica, Polazak) values ('Novska', '13:45')
go

select
	*,
	LEAD(Polazak, 1) over (order by Polazak),
	datediff(minute, Polazak, LEAD(Polazak, 1) over (order by Polazak)) as VoznjaDoIduce
from VozniRed
go
