Напредни извештаи од базата (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 AS event_type
FROM travel_sage.event e
JOIN travel_sage.destination d ON e.id_destination = d.id_destination
WHERE d.location_name = 'Ohrid';

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

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

SELECT d.location_name AS destination_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 AS destination_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 AS destination_name,
       COUNT(a.id_activity) AS total_activities,
       SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS cheap_activities,
       (SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.id_activity)) AS percent_cheap_activities
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 percent_cheap_activities DESC; 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT DATE_TRUNC('month', r.time_point) AS month, 
       a.category AS activity_category, 
       COUNT(a.id_activity) AS activity_count
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 month, a.category
ORDER BY month, activity_count DESC;

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

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

SELECT DATE_TRUNC('month', r.time_point) AS month, 
       u.first_name, u.last_name,
       COUNT(DISTINCT r.id_reservation) AS reservation_count,
       COUNT(DISTINCT ar.id_activity) AS activity_count
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 month, u.id_user
ORDER BY month, reservation_count DESC;

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

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

Детален преглед на дестинација

Детален приказ на одредена туристичка дестинација т.е. покрај основните информации (име, опис, држава), ги вклучува и сите поврзани тагови, настани, активности и туристички пакети, како и тековни метеоролошки услови.

Најчеста примена: во view за деталите на дестинација каде корисникот сака целосен преглед на достапни понуди и услови за избраното место.

SELECT 
    d.id_destination,
    d.location_name,
    d.location_desc,
    d.country,
    ARRAY_AGG(DISTINCT t.tag_name) AS tags,
    ARRAY_AGG(DISTINCT e.event_name) AS events,
    ARRAY_AGG(DISTINCT a.activity_name) AS activities,
    ARRAY_AGG(DISTINCT p.package_name) AS packages,
    m.current_temp,
    m.weather_condition
FROM travel_sage.destination d
LEFT JOIN travel_sage.destination_tag dt 
       ON d.id_destination = dt.id_destination
LEFT JOIN travel_sage.tag t 
       ON dt.id_tag = t.id_tag
LEFT JOIN travel_sage.event e 
       ON d.id_destination = e.id_destination
LEFT JOIN travel_sage.activity a 
       ON d.id_destination = a.id_destination
LEFT JOIN travel_sage.package p 
       ON d.id_destination = p.id_destination
LEFT JOIN travel_sage.meteorological_condition m 
       ON d.id_destination = m.id_destination
WHERE d.id_destination = 2
GROUP BY 
    d.id_destination, 
    d.location_name, 
    d.location_desc, 
    d.country, 
    m.current_temp, 
    m.weather_condition;