Напредни извештаи од базата (SQL и складирани процедури)

Извештај за сите кина, на месечно ниво, колку карти се пордадени и вкупниот профит во предходната година

select c.id_cinema,c.name,
coalesce(prv_mesec.karti, 0) as prvi_mesec,
coalesce(vtor_mesec.karti, 0) as vtori_mesec,
coalesce(tret_mesec.karti, 0) as tretti_mesec,
coalesce(cetvrt_mesec.karti,0) as cetvrti_mesec,
coalesce(pet_mesec.karti,0) as petti_mesec,
coalesce(sest_mesec.karti,0) as sesti_mesec,
coalesce(sedum_mesec.karti, 0)  as sedmi_mesec,
coalesce(osum_mesec.karti,0)  as osmi_mesec,
coalesce(devet_mesec.karti,0) as devetti_mesec,
coalesce(deset_mesec.karti, 0) as desetti_mesec,
coalesce(edinaeset_mesec.karti11,0) as edinaesti_mesec,
coalesce(dvanaeset_mesec.karti,0) as dvanaesti_mesec,
sum(t.price) as vkupen_promet
from cinemas c 
join projection_rooms pr on pr.id_cinema=c.id_cinema
join projection_is_played_in_room pipir on pipir.id_room =pr.id_room 
join projections p on p.id_projection = pipir.id_projection 
join tickets t on t.id_projection=p.id_projection 
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=1
        group by c2.id_cinema 
        
) as prv_mesec on prv_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=2
        group by c2.id_cinema 
        
) as vtor_mesec on vtor_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=3
        group by c2.id_cinema 
        
) as tret_mesec on tret_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=4
        group by c2.id_cinema 
        
) as cetvrt_mesec on cetvrt_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=5
        group by c2.id_cinema 
        
) as pet_mesec on pet_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=6
        group by c2.id_cinema 
        
) as sest_mesec on sest_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=7
        group by c2.id_cinema 
        
) as sedum_mesec on sedum_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=8
        group by c2.id_cinema 
        
) as osum_mesec on osum_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=9
        group by c2.id_cinema 
        
) as devet_mesec on devet_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=10
        group by c2.id_cinema 
        
) as deset_mesec on deset_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti11,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=11
        group by c2.id_cinema 
        
) as edinaeset_mesec on edinaeset_mesec.id=c.id_cinema  
left join (
        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
        join projections p1 on t3.id_projection = p1.id_projection 
        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
        where extract(month from t3.date_reserved)=12
        group by c2.id_cinema 
        
) as dvanaeset_mesec on dvanaeset_mesec.id=c.id_cinema 
where extract(year from t.date_reserved)=extract(year from now()-interval'1 year')
group by c.id_cinema,prv_mesec.karti,vtor_mesec.karti,tret_mesec.karti,cetvrt_mesec.karti,
pet_mesec.karti,sest_mesec.karti,sedum_mesec.karti,osum_mesec.karti,devet_mesec.karti,deset_mesec.karti,
edinaeset_mesec.karti11,dvanaeset_mesec.karti
order by c.id_cinema

Извештај за секое кино, кој филм има направено најмногу профит во последните 3 месеци

select c3.id_cinema,cpf3.id_film from cinemas c3 
join cinema_plays_film cpf3 on cpf3.id_cinema=c3.id_cinema 
join (
select c.id_cinema as id1,max(sum_cena.sum1) as sumMaks
from cinemas c 
join cinema_plays_film cpf on cpf.id_cinema=c.id_cinema 
join films f on f.id_film=cpf.id_film 
join projections p on p.id_film=f.id_film
join tickets t on t.id_projection=p.id_projection and t.date_reserved between now()-interval'3 months' and now()
join (
select distinct(c2.id_cinema) as id,f2.id_film as film,sum(t1.price) as sum1 from tickets t1
join projections p2 on p2.id_projection=t1.id_projection
join films f2 on f2.id_film = p2.id_film 
join cinema_plays_film cpf2 on cpf2.id_film=f2.id_film 
join cinemas c2 on c2.id_cinema=cpf2.id_cinema 
where t1.date_reserved between now()-interval'3 months' and now()
group by c2.id_cinema,f2.id_film 
) as sum_cena on sum_cena.id=c.id_cinema 
group by c.id_cinema
) as maks on maks.id1=c3.id_cinema 
join (
select distinct(c2.id_cinema) as id,f2.id_film as film,sum(t1.price) as sum2 from tickets t1
join projections p2 on p2.id_projection=t1.id_projection
join films f2 on f2.id_film = p2.id_film 
join cinema_plays_film cpf2 on cpf2.id_film=f2.id_film 
join cinemas c2 on c2.id_cinema=cpf2.id_cinema 
where t1.date_reserved between now()-interval'3 months' and now()
group by c2.id_cinema,f2.id_film 

) as counts on counts.id=c3.id_cinema and counts.film=cpf3.id_film
where counts.sum2=maks.sumMaks
group by c3.id_cinema,cpf3.id_film
order by c3.id_cinema 

Извештај за секој клиент, за секое кино, колку карти има купено во секое кино, колку пари има потрошено во секое кино, и колку кодови за попуст имаат искористено во секое кино

select c.id_customer, c2.id_cinema ,
coalesce((
select count(distinct t1.id_ticket) from tickets t1
join projections p on p.id_projection=t1.id_projection 
join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
join projection_rooms pr on pr.id_room=pipir.id_room 
join cinemas cin on cin.id_cinema=pr.id_cinema
where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
,0),
coalesce((
select sum(t1.price) from tickets t1
join projections p on p.id_projection=t1.id_projection 
join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
join projection_rooms pr on pr.id_room=pipir.id_room 
join cinemas cin on cin.id_cinema=pr.id_cinema
where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
,0),
coalesce((
select count(distinct d.id_discount) from discounts d 
join tickets t1  on t1.id_discount = d.id_discount 
join projections p on p.id_projection=t1.id_projection 
join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
join projection_rooms pr on pr.id_room=pipir.id_room 
join cinemas cin on cin.id_cinema=pr.id_cinema
where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer
),0)
from customers c 
left join tickets t on t.id_customer=c.id_customer 
left join projections p on p.id_projection=t.id_projection 
left join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
left join projection_rooms pr on pr.id_room=pipir.id_room 
left join cinemas c2 on c2.id_cinema=pr.id_cinema 
where t.date_reserved between now()-interval'1 year' and now()
group by c.id_customer,c2.id_cinema 
order by c.id_customer

Извештај за секое кино, колку од настаните биле организирани кај него и извештај за секој настан колку клиенти биле заинтересирани за секој настан во последните 6 месеци

select c.id_cinema,count(distinct coe.id_event) from cinemas c
left join cinema_organizes_event coe on coe.id_cinema=c.id_cinema 
left join events e on e.id_event=coe.id_event 
where e.start_date between now()-interval'6 months' and now()
group by c.id_cinema 

select e.id_event,count(distinct c.id_customer) from events e 
left join customer_is_interested_in_event ciiie on ciiie.id_event=e.id_event 
left join customers c on ciiie.id_customer=c.id_customer 
where e.start_date between now()-interval'6 months' and now()
group by e.id_event 

Извештај за секој филм, колку карти се продадени и колкав профит има секој филм направено, колку различни клиенти купиле карта за филмот и колку рејтинзи има секој филм

select f.id_film,
coalesce((
select count(distinct t.id_ticket) from tickets t
left join projections p on p.id_projection=t.id_projection 
left join films f1 on f1.id_film=p.id_film 
where f.id_film=f1.id_film 
),0),
coalesce((
select sum(t.price) from tickets t
left join projections p on p.id_projection=t.id_projection 
left join films f1 on f1.id_film=p.id_film 
where f.id_film=f1.id_film 
),0),
coalesce((
select count(distinct c.id_customer) from customers c
left join tickets t2 on t2.id_customer=c.id_customer 
left join projections p on p.id_projection=t2.id_projection 
left join films f1 on f1.id_film=p.id_film 
where f.id_film=f1.id_film 
),0),
coalesce((
select count(crf.rating) from customer_rates_film crf 
left join films f2 on f2.id_film=crf.id_film 
where f2.id_film=f.id_film
),0)
from films f 

Извештај за секое кино, колку вработени има и колку пари треба да исплати секое кино следниов месец на неговите вработени

select c.id_cinema, 
coalesce((
select count(distinct w.id_worker) from workers w
left join cinemas c1 on c1.id_cinema=w.id_cinema 
where c1.id_cinema=c.id_cinema
),0),
coalesce((
select sum(s.sum) from salaries s 
left join workers w2 on s.id_worker=w2.id_worker
left join cinemas c1 on c1.id_cinema=w2.id_cinema 
where c1.id_cinema=c.id_cinema and s.date_to is null
),0)
from cinemas c