Analytical and statistical querying

1. Finding routes and subroutes for arbitrary locations

This SQL query retrieves trips that include both a specified start location and end location as stops, ensuring the start location comes before the end location in the stop sequence, excluding direct routes between them unless the WHERE clause is commented out.

WITH start_location AS (
    SELECT ts.trip_id, ts.stop_time
    FROM trip_stops ts
    WHERE ts.location_id = 100 -- ohrid
),
end_location AS (
    SELECT ts.trip_id, ts.stop_time
    FROM trip_stops ts
    WHERE ts.location_id = 300 -- skopje
),
trips AS (
    SELECT s.trip_id
    FROM start_location s
    JOIN end_location e ON s.trip_id = e.trip_id
    WHERE s.stop_time < e.stop_time
)
SELECT 
    t.trip_id,
    t.route_id,
    r.from_location_id,
    r.to_location_id,
    from_loc.name AS from_name,
    to_loc.name AS to_name,
    to_org.company_name AS transport_company,
    t.status
FROM trip t
JOIN trips tr ON t.trip_id = tr.trip_id
JOIN route r ON t.route_id = r.route_id
JOIN location from_loc ON r.from_location_id = from_loc.location_id
JOIN location to_loc ON r.to_location_id = to_loc.location_id
JOIN transport_organizer to_org ON r.transport_organizer_id = to_org.transport_organizer_id
WHERE NOT (r.from_location_id = 100 AND r.to_location_id = 300) -- commenting this line here gives ONLY SUBROUTES!

2. Transport company performance overview

This SQL query aggregates performance metrics for each transport organizer, including routes operated, trips organized, tickets sold, total revenue, average ticket price, unique customers, and average rating, sorted by total revenue in descending order.

WITH company_performance AS (
    SELECT 
        to_org.company_name,
        COUNT(DISTINCT r.route_id) as routes_operated,
        COUNT(DISTINCT t.trip_id) as trips_organized,
        COUNT(tk.ticket_id) as total_tickets_sold,
        SUM(COALESCE(tk.price, 0)) as total_revenue,
        AVG(COALESCE(tk.price, 0)) as avg_ticket_price,
        COUNT(DISTINCT tk.account_id) as unique_customers,
        AVG(COALESCE(rev.rating, 0)) as avg_rating
    FROM transport_organizer to_org
    JOIN route r ON to_org.transport_organizer_id = r.transport_organizer_id
    JOIN trip t ON r.route_id = t.route_id
    LEFT JOIN ticket tk ON t.trip_id = tk.trip_id
    LEFT JOIN review rev ON tk.ticket_id = rev.ticket_id
    GROUP BY to_org.transport_organizer_id, to_org.company_name
)
SELECT 
    company_name,
    routes_operated,
    trips_organized,
    total_tickets_sold,
    total_revenue,
    avg_ticket_price,
    unique_customers,
    ROUND(avg_rating, 2) as avg_rating
FROM company_performance
ORDER BY total_revenue DESC;

3. Top user-purchased routes

This SQL query calculates a weighted score for routes based on ticket purchases by a specific account over the past three months (with weights of 0.5, 0.3, and 0.2 for the most recent to oldest month), returning routes with scores at or above the average, ordered by weighted score in descending order.

WITH route_weighted_usage AS (
    SELECT 
        r.route_id,
        l_from.name AS from_location,
        l_to.name AS to_location,
        to_.company_name AS transport_company,
        SUM(
            CASE 
                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE) 
                    THEN 0.5
                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') 
                    THEN 0.3
                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months') 
                    THEN 0.2
                ELSE 0
            END -- this will be used as a weighted sum to give edge to recently bought tickets
        ) AS weighted_score
    FROM ticket t
    JOIN trip tr ON t.trip_id = tr.trip_id
    JOIN route r ON tr.route_id = r.route_id
    JOIN location l_from ON r.from_location_id = l_from.location_id
    JOIN location l_to ON r.to_location_id = l_to.location_id
    JOIN transport_organizer to_ ON r.transport_organizer_id = to_.transport_organizer_id
    WHERE t.account_id = 300 AND t.date_purchased >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY r.route_id, l_from.name, l_to.name, to_.company_name
)
SELECT 
    route_id,
    from_location,
    to_location,
    transport_company,
    weighted_score
FROM route_weighted_usage
WHERE weighted_score >= (SELECT AVG(weighted_score) FROM route_weighted_usage)
ORDER BY weighted_score DESC;

4. Ticket sales revenue by weekday and route

This query analyzes completed trips to show ticket volume, total and average revenue, and route details. Useful to check trends throughout the week.

SELECT 
    TO_CHAR(tk.date_purchased, 'Day') AS day_of_week,
    COUNT(tk.ticket_id) AS total_tickets,
    SUM(p.total_price) AS total_revenue,
    AVG(p.total_price) AS avg_ticket_price,
    r.from_location_id,
    r.to_location_id
FROM ticket tk
JOIN payment p ON tk.payment_id = p.payment_id
JOIN trip t ON tk.trip_id = t.trip_id
JOIN route r ON t.route_id = r.route_id
WHERE t.status = 'COMPLETED'
GROUP BY TO_CHAR(tk.date_purchased, 'Day'), r.from_location_id, r.to_location_id
ORDER BY total_tickets DESC;

5. Most popular destinations in the past year

This SQL query aggregates ticket data by destination over the past year, calculating total passengers, unique customers, revenue, and specific ticket types, then returns destinations with passenger counts at or above the average, ordered by total passengers in descending order.

WITH destinations AS (
    SELECT 
        l.name AS destination,
        COUNT(t.ticket_id) AS total_passengers,
        COUNT(DISTINCT t.account_id) AS unique_customers,
        SUM(t.price) AS total_revenue,
        COUNT(st.student_ticket_id) AS student_tickets,
        COUNT(ct.child_ticket_id) AS child_tickets,
        l.latitude,
        l.longitude
    FROM location l
    JOIN ticket t ON l.location_id = t.gets_off_location_id
    JOIN trip tr ON t.trip_id = tr.trip_id
    JOIN transport_organizer to_org ON tr.transport_organizer_id = to_org.transport_organizer_id
    LEFT JOIN student_ticket st ON t.ticket_id = st.ticket_id
    LEFT JOIN child_ticket ct ON t.ticket_id = ct.ticket_id
    GROUP BY l.location_id, l.name, l.latitude, l.longitude
),
stats AS (
    SELECT AVG(total_passengers) AS mean_passengers -- this is used to avoid hard limits 
    FROM destinations
)
SELECT 
    d.destination,
    d.total_passengers,
    d.unique_customers,
    d.total_revenue,
    d.student_tickets,
    d.child_tickets
FROM destinations d, stats
WHERE d.total_passengers >= stats.mean_passengers
ORDER BY d.total_passengers DESC;

6. Top selling routes for transport organizers

This query retrieves the most popular routes for a specific transport organizer, ranking them by the number of tickets sold while also showing total revenue and average ticket price.

WITH route_stats AS (
    SELECT 
        r.route_id,
        r.from_location_id,
        r.to_location_id,
        COUNT(tk.ticket_id) AS total_tickets_sold,
        SUM(p.total_price) AS total_revenue,
        AVG(p.total_price) AS avg_ticket_price
    FROM route r
    JOIN trip tr ON r.route_id = tr.route_id
    JOIN ticket tk ON tr.trip_id = tk.trip_id
    JOIN payment p ON tk.payment_id = p.payment_id
    WHERE r.transport_organizer_id = 100 -- just for reference
    GROUP BY r.route_id, r.from_location_id, r.to_location_id
)
SELECT rs.*
FROM route_stats rs
WHERE rs.total_tickets_sold >= 0.8 * 
	(SELECT 
             MAX(total_tickets_sold) FROM route_stats) -- this is used to retrieve top selling tickets, the percentage is flexible
ORDER BY rs.total_tickets_sold DESC;