AdvancedReports: AdvancedReports.sql

/*destinacii so najmn raznovidni nastani*/
/*SELECT d.imeLokacija, COUNT(DISTINCT n.kategorija) AS brojRaznovidniNastani
FROM DESTINACII d
JOIN NASTANI n ON d.idDest = n.idDest
GROUP BY d.imeLokacija
ORDER BY brojRaznovidniNastani DESC;*/
SELECT d.imeLokacija AS destinacija, COUNT(DISTINCT n.naziv) AS broj_nastani
FROM DESTINACII d
JOIN NASTANI n ON d.idDest = n.idDest
GROUP BY d.imeLokacija
HAVING COUNT(DISTINCT n.naziv) = (
    SELECT MAX(broj_nastani)
    FROM (
        SELECT COUNT(DISTINCT n.naziv) AS broj_nastani
        FROM DESTINACII d
        JOIN NASTANI n ON d.idDest = n.idDest
        GROUP BY d.imeLokacija
    ) AS temp
);


/*nastani na odredena dest*/
SELECT DISTINCT n.naziv
FROM NASTANI n
JOIN DESTINACII d ON n.idDest = d.idDest
WHERE d.imeLokacija = 'Охрид';


/*top 10 dest spored recenzija*/
/*SELECT d.imeLokacija, AVG(r.kvalitet) AS prosekKvalitet
FROM DESTINACII d
JOIN RECENZII r ON d.idDest = r.idDest
GROUP BY d.imeLokacija
ORDER BY prosekKvalitet DESC
LIMIT 10;*/
SELECT idKorisnik, COUNT(*) AS broj_recenzii
FROM RECENZII
GROUP BY idKorisnik
HAVING COUNT(*) > (SELECT COUNT(*) * 0.9 FROM RECENZII);


/*dest so najgolem br aktivnosti so razlichni cena/opsezi*/
SELECT d.imeLokacija,
       SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) AS aktivnostiPod20,
       SUM(CASE WHEN a.iznos < 50 THEN 1 ELSE 0 END) AS aktivnostiPod50,
       SUM(CASE WHEN a.iznos < 100 THEN 1 ELSE 0 END) AS aktivnostiPod100
FROM DESTINACII d
JOIN AKTIVNOSTI a ON d.idDest = a.idDest
GROUP BY d.imeLokacija
ORDER BY aktivnostiPod20 DESC, aktivnostiPod50 DESC, aktivnostiPod100 DESC;

/*dest so najevtini aktivnosti*/
SELECT d.imeLokacija,
       COUNT(a.idAktivnost) AS vkupnoAktivnosti,
       SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) AS efiniAktivnosti,
       (SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.idAktivnost)) AS procentEfiniAktivnosti
FROM DESTINACII d
JOIN AKTIVNOSTI a ON d.idDest = a.idDest
GROUP BY d.imeLokacija
HAVING COUNT(a.idAktivnost) > 0
ORDER BY procentEfiniAktivnosti DESC;


/*top 10 korisnici so najmn recenzii*/
/*SELECT k.ime, k.prezime, COUNT(r.idRecenzija) AS brojRecenzii
FROM KORISNICI k
JOIN RECENZII r ON k.idKorisnik = r.idKorisnik
GROUP BY k.idKorisnik, k.ime, k.prezime
ORDER BY brojRecenzii DESC
LIMIT 10;*/
SELECT idKorisnik, COUNT(*) AS broj_recenzii
FROM RECENZII
GROUP BY idKorisnik
HAVING COUNT(*) > (SELECT COUNT(*) * 0.9 FROM RECENZII);

/*value for money*/
/*SELECT p.imePaket, p.cena, TIMESTAMPDIFF(DAY, p.pochetok, p.kraj) AS vremeTraenjeDena,
       p.cena / TIMESTAMPDIFF(DAY, p.pochetok, p.kraj) AS cenaPoDen
FROM PAKETI p
WHERE TIMESTAMPDIFF(DAY, p.pochetok, p.kraj) > 0
ORDER BY cenaPoDen ASC
LIMIT 10;*/
SELECT p.imePaket AS paket, 
       p.cena / DATEDIFF(p.kraj, p.pochetok) AS cena_po_den
FROM PAKETI p
WHERE p.cena / DATEDIFF(p.kraj, p.pochetok) = (
    SELECT MIN(p.cena / DATEDIFF(p.kraj, p.pochetok))
    FROM PAKETI p
);