Напредни извештаи од базата (SQL)

Дестинации со најголем број на разновидни настани

Идентификување на дестинации кои имаат најголем број на различни видови настани. Во долунаведениот код се пребаруваат сите дестинации и ги брои различните видови на настани (како концерти, фестивали, саеми и сл.) поврзани со секоја дестинација. Потоа, резултатите се сортираат по бројот на различни настани во опаѓачки редослед и се добива еден вид на рангирање. Од особено значење за корисници што сакаат да вклучат повеќе разновидни настани при нивното патување.

SELECT d.location_name AS destination, COUNT(DISTINCT e.event_type) AS event_number
FROM travel_sage.destination d
JOIN travel_sage.event e ON d.id_destination = e.id_destination
GROUP BY d.location_name
HAVING COUNT(DISTINCT e.event_type) = (
    SELECT MAX(event_number)
    FROM (
        SELECT COUNT(DISTINCT e.event_type) AS event_number
        FROM travel_sage.destination d
        JOIN travel_sage.event e ON d.id_destination = e.id_destination
        GROUP BY d.location_name
    ) AS temp
);

Настани на одредена дестинација

Дополнително, за да се прикажат различни категории на настани за одредена дестинација. За пример, доколку сакате да ги видите сите видови настани што се одржуваат во Охрид:

SELECT DISTINCT e.event_type
FROM travel_sage.event e
JOIN travel_sage.destination d ON e.id_destination = d.id_destination
WHERE d.location_name = 'Охрид';

Топ дестинации според просечен квалитет на рецензии

Овде се зема просечниот квалитет на рецензиите за секоја дестинација и ги прикажува првите 10 дестинации со највисок квалитет на рецензии. Корисно за корисниците што сакаат да ја изберат најдобро оценетата дестинација.

SELECT d.location_name, AVG(r.quality) AS average_quality
FROM travel_sage.destination d
JOIN travel_sage.review r ON d.id_destination = r.id_destination
GROUP BY d.location_name
ORDER BY average_quality DESC
LIMIT 10;

Дестинации со најголем број на активности под различни ценовни опсези

Приказ на дестинациите и брои колку активности постојат во различни ценовни групи (пример под 20, под 50, под 100 евра). Тоа му помага на корисникот да избере дестинација според неговиот буџет и преференции за активности.

SELECT d.location_name,
       SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS activities_under20,
       SUM(CASE WHEN a.amount < 50 THEN 1 ELSE 0 END) AS activities_under50,
       SUM(CASE WHEN a.amount < 100 THEN 1 ELSE 0 END) AS activities_under100
FROM travel_sage.destination d
JOIN travel_sage.activity a ON d.id_destination = a.id_destination
GROUP BY d.location_name
ORDER BY activities_under20 DESC, activities_under50 DESC, activities_under100 DESC;

Дестинации со најголем процент на достапни(евтини) активности

Се прикажуваат дестинации со евтини активности.

SELECT d.location_name,
       COUNT(a.id_activity) AS vkupno_aktivnosti,
       SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS efini_aktivnosti,
       (SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.id_activity)) AS procent_efini_aktivnosti
FROM travel_sage.destination d
JOIN travel_sage.activity a ON d.id_destination = a.id_destination
GROUP BY d.location_name
HAVING COUNT(a.id_activity) > 0
ORDER BY procent_efini_aktivnosti DESC;

Топ корисници со најголем број напишани рецензии

Најактивни корисници т.е. оние кои имаат напишано најголем број на рецензии.

SELECT u.id_user, COUNT(r.id_review) AS broj_recenzii
FROM travel_sage.users u
JOIN travel_sage.review r ON u.id_user = r.id_user
GROUP BY u.id_user
ORDER BY broj_recenzii DESC
LIMIT 10;

Најисплатливи пакети (Best value for money)

Пресметување на цената по ден за пакети, што помага да се дознае кој пакет има најдобра вредност според сумата. Во случај да има пакет од 20 евра за 1 ден или пакет од 50 евра за 1 недела, би можело да се прикаже која опција е најисплатлива.

SELECT p.package_name, (p.price / GREATEST(DATE_PART('day', p.end_date - p.start_date), 1)) AS cena_po_den
FROM travel_sage.package p
ORDER BY cena_po_den ASC
LIMIT 1;

Приказ на бројот на резервации по месец

Помага да се следи трендот на резервации преку времето.

SELECT DATE_TRUNC('month', r.time_point) AS mesec, COUNT(r.id_reservation) AS broj_rezervacii
FROM travel_sage.reservation r
GROUP BY mesec
ORDER BY mesec;

Приказ на бројот на нови корисници по месец

Покажување на новорегистрираните корисници по месец. Овозможува да се види растот на бројот на корисници во различни месеци.

SELECT DATE_TRUNC('month', u.birth_date) AS mesec, COUNT(u.id_user) AS broj_novi_korisnici
FROM travel_sage.users u
GROUP BY mesec
ORDER BY mesec;

Приказ на бројот на пакети во резервации по месец

Ги брои пакетите што се резервирани по месец. Можност за следење на популарноста на различни пакети во текот на годината.

SELECT DATE_TRUNC('month', p.start_date) AS mesec, COUNT(pr.id_package) AS broj_paketi
FROM travel_sage.package p
JOIN travel_sage.package_reservation pr ON p.id_package = pr.id_package
GROUP BY mesec
ORDER BY mesec;

Приказ на бројот на настани по месец

Корисно е за да се согледа колку настани се организирани секој месец.

SELECT DATE_TRUNC('month', e.start_date) AS mesec, COUNT(e.id_event) AS broj_nastani
FROM travel_sage.event e
GROUP BY mesec
ORDER BY mesec;

Приказ на најпопуларните активности на месечно ниво

Овде е прикажана бројката на активности по категорија, со цел да се утврди која активност е најпопуларна во текот на месецот.

SELECT DATE_TRUNC('month', r.time_point) AS mesec, a.category, COUNT(a.id_activity) AS broj_aktivnosti
FROM travel_sage.reservation r
JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
JOIN travel_sage.activity a ON ar.id_activity = a.id_activity
GROUP BY mesec, a.category
ORDER BY mesec, broj_aktivnosti DESC;

Приказ на број на резервации и активности по месец и корисник

Статистика за секој корисник по месец, вклучувајќи број на резервации и активности. Така, се гледа активноста на корисниците и популарноста на нивните активности.

SELECT DATE_TRUNC('month', r.time_point) AS mesec, u.first_name, u.last_name,
       COUNT(DISTINCT r.id_reservation) AS broj_rezervacii,
       COUNT(DISTINCT ar.id_activity) AS broj_aktivnosti
FROM travel_sage.users u
JOIN travel_sage.reservation r ON u.id_user = r.id_user
LEFT JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
GROUP BY mesec, u.id_user
ORDER BY mesec, broj_rezervacii DESC;

Просечен попуст на премиум корисници

SELECT AVG(pu.discount) AS prosek_popust
FROM premium pu;