Напредни извештаи од базата (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 c2.id_cinema,maks.sumMaks,sum_3.film3 from cinemas c2
join projection_rooms pr2 on pr2.id_cinema=c2.id_cinema
join projection_is_played_in_room pipir2 on pipir2.id_room=pr2.id_room 
join projections p2 on p2.id_projection = pipir2.id_projection
join films f2 on  f2.id_film=p2.id_film 
join(
        select c.id_cinema as id1,max(sum_cena.sum1) as sumMaks
        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 cinema_plays_film cpf on cpf.id_cinema=c.id_cinema 
        join films f on  f.id_film=p.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 c1.id_cinema as id,f1.id_film as film,sum(t1.price) as sum1  from cinemas c1
        join projection_rooms pr1 on pr1.id_cinema=c1.id_cinema 
        join projection_is_played_in_room pipir1 on pipir1.id_room=pr1.id_room 
        join projections p1 on p1.id_projection=pipir1.id_projection 
        join films f1 on f1.id_film=p1.id_film 
        join tickets t1 on t1.id_projection=p1.id_projection 
        where t1.date_reserved between now()-interval'3 months' and now()
        group by c1.id_cinema,f1.id_film  
        ) as sum_cena on sum_cena.id=c.id_cinema and sum_cena.film=f.id_film 
        group by c.id_cinema
) as maks on maks.id1=c2.id_cinema 
join(
        select c3.id_cinema as id3,f3.id_film as film3,sum(t3.price) as sum3  from cinemas c3
        join projection_rooms pr3 on pr3.id_cinema=c3.id_cinema 
        join projection_is_played_in_room pipir3 on pipir3.id_room=pr3.id_room 
        join projections p3 on p3.id_projection=pipir3.id_projection 
        join films f3 on f3.id_film=p3.id_film 
        join tickets t3 on t3.id_projection=p3.id_projection 
        where t3.date_reserved between now()-interval'3 months' and now()
        group by c3.id_cinema,f3.id_film  
) as sum_3 on sum_3.id3=c2.id_cinema and sum_3.film3=f2.id_film and sum_3.sum3=maks.sumMaks
group by c2.id_cinema,maks.sumMaks,sum_3.film3

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

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

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

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  

Извештај за секое кино, колку од настаните биле организирани кај него и извештај за секој настан колку клиенти биле заинтересирани за секој настан во последните 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