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

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

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

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

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

Го прикажува бројот на активности по ценовни групи (<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;

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

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

SELECT p.package_name, 
       (p.price / GREATEST((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;
  $$;
  

Годишен извештај за резервации по пакет

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

CREATE OR REPLACE FUNCTION GetMonthlyPackageReport(year INT)
RETURNS TABLE(
    Month INT,
    PackageName varchar(100),
    ReservationCount bigint,
    TotalRevenue numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        EXTRACT(MONTH FROM r.time_point)::INT AS Month,
        p.package_name,
        COUNT(pr.id_reservation) AS ReservationCount,
        SUM(p.price) AS TotalRevenue
    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(YEAR FROM r.time_point) = year
    GROUP BY Month, p.package_name
    ORDER BY Month, TotalRevenue DESC;
END;
$$;

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

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

CREATE OR REPLACE FUNCTION GetAverageActivitiesPerDestination()
RETURNS TABLE(
    DestinationName varchar(100),
    TotalActivities bigint,
    AverageActivitiesPerDestination numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.location_name AS DestinationName,
        COUNT(a.id_activity) AS TotalActivities,
        ROUND(COUNT(a.id_activity)::numeric / COUNT(DISTINCT d.id_destination),2) AS AverageActivitiesPerDestination
    FROM travel_sage.destination d
    LEFT JOIN travel_sage.activity a ON d.id_destination = a.id_destination
    GROUP BY d.location_name
    ORDER BY TotalActivities DESC;
END;
$$;

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

Прикажува процент на активности по дестинација кои се евтини (<20), агрегирано, за да се препорачаат буџетски дестинации.

CREATE OR REPLACE FUNCTION GetDestinationsCheapActivitiesReport()
RETURNS TABLE(
    DestinationName varchar(100),
    TotalActivities bigint,
    CheapActivities bigint,
    PercentCheapActivities numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        d.location_name AS DestinationName,
        COUNT(a.id_activity) AS TotalActivities,
        SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS CheapActivities,
        ROUND(SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.id_activity),2) AS PercentCheapActivities
    FROM travel_sage.destination d
    LEFT 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 PercentCheapActivities DESC;
END;
$$;

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

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

CREATE OR REPLACE FUNCTION GetMonthlyActivityReport(year INT)
RETURNS TABLE(
    Month INT,
    TotalActivityReservations bigint
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        EXTRACT(MONTH FROM r.time_point)::INT AS Month,
        COUNT(DISTINCT ar.id_activity) AS TotalActivityReservations
    FROM travel_sage.reservation r
    JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
    WHERE EXTRACT(YEAR FROM r.time_point) = year
    GROUP BY Month
    ORDER BY Month;
END;
$$;

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

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

CREATE OR REPLACE FUNCTION GetMonthlyUserReservationReport(year INT)
RETURNS TABLE(
    Month INT,
    NewUsers bigint,
    TotalReservations bigint
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        m.Month,
        COUNT(DISTINCT u.id_user) AS NewUsers,
        COALESCE(SUM(r.ReservationCount),0) AS TotalReservations
    FROM (
        SELECT EXTRACT(MONTH FROM birth_date)::INT AS Month, id_user
        FROM travel_sage.users
        WHERE EXTRACT(YEAR FROM birth_date) = year
    ) u
    LEFT JOIN (
        SELECT EXTRACT(MONTH FROM time_point)::INT AS Month, id_reservation,
               id_user, COUNT(*) AS ReservationCount
        FROM travel_sage.reservation
        WHERE EXTRACT(YEAR FROM time_point) = year
        GROUP BY Month, id_reservation, id_user
    ) r ON u.id_user = r.id_user AND u.Month = r.Month
    RIGHT JOIN (SELECT generate_series(1,12) AS Month) m ON m.Month = u.Month OR m.Month = r.Month
    GROUP BY m.Month
    ORDER BY m.Month;
END;
$$;

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

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

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';