Напредни извештаи од базата (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;

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

Овој извештај покажува кои дестинации имаат најголем удел на евтини активности (<20) во однос на вкупната понуда.

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)

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

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 travel_sage.premium pu;

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

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

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;

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

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

• Се групираат корисниците според месецот и годината на нивната регистрација (или датум на креирање во системот).
• За секој месец се прикажува бројот на нови корисници што се приклучиле.

Со ова се добива тренд анализа на растот на платформата:

• Може да се забележат периоди кога бројот на регистрации нагло расте (на пример, после маркетинг кампања или воведување на нова функционалност).
• Може да се идентификуваат и „слаби“ месеци кога има малку нови регистрации.

  CREATE OR REPLACE FUNCTION GetUsersReport(month INT, year INT)
  RETURNS TABLE(
      UserFullName varchar(100),
      TotalReservations bigint,
      TotalActivities bigint,
      TotalSpent numeric,
      AverageReview numeric
  ) 
  LANGUAGE plpgsql
  AS $$
  BEGIN
      RETURN QUERY
      SELECT 
         u.first_name || ' ' || u.last_name AS UserFullName,
         COUNT(DISTINCT r.id_reservation) AS TotalReservations,
         COUNT(DISTINCT ar.id_activity) AS TotalActivities,
         COALESCE(SUM(p.price),0) AS TotalSpent,
         COALESCE(AVG(rv.quality),0) AS AverageReview
      FROM travel_sage.users u
      LEFT 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
      LEFT JOIN travel_sage.package_reservation pr ON r.id_reservation = pr.id_reservation
      LEFT JOIN travel_sage.package p ON pr.id_package = p.id_package
      LEFT JOIN travel_sage.review rv ON u.id_user = rv.id_user
      WHERE EXTRACT(MONTH FROM r.time_point) = month AND EXTRACT(YEAR FROM r.time_point) = year
      GROUP BY u.id_user;
  END;
  $$;
  

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

Овој извештај прикажува како пакетите (туристички аранжмани) се користат низ времето.

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

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

• Може да се открие кои пакети се најбарани во летниот период, а кои во зимскиот.
• Овозможува да се прави подобро планирање на ресурсите и промоциите (на пример, да се засили реклама за пакет кој е популарен пред лето).

  CREATE OR REPLACE FUNCTION GetPackagesReport(month INT, year INT)
  RETURNS TABLE(
      PackageName varchar(100),
      ReservationCount bigint,
      TotalRevenue numeric,
      AvgDuration numeric,
      PricePerDay numeric
  ) 
  LANGUAGE plpgsql
  AS $$
  BEGIN
      RETURN QUERY
      SELECT 
         p.package_name,
         COUNT(pr.id_reservation) AS ReservationCount,
         SUM(p.price) AS TotalRevenue,
         AVG(DATE_PART('day', p.end_date - p.start_date)) AS AvgDuration,
         AVG(p.price / GREATEST(DATE_PART('day', p.end_date - p.start_date),1)) AS PricePerDay
      FROM travel_sage.package p
      JOIN travel_sage.package_reservation pr ON p.id_package = pr.id_package
      JOIN travel_sage.reservation r ON pr.id_reservation = r.id_reservation
      WHERE EXTRACT(MONTH FROM r.time_point) = month AND EXTRACT(YEAR FROM r.time_point) = year
      GROUP BY p.package_name;
  END;
  $$;