I am trying to run this Query, but it is telling me I have an error in the second WITH, where it says WITH tabla AS.

This is my Query, does anyone have any comments or how can I make it run?

Please help!

WITH reservations AS (
        SELECT r.id "trip_id",
        r.departure_time AT TIME ZONE 'America/Mexico_City' "trip_departure_time",
        (r.departure_time AT TIME ZONE 'America/Mexico_City')::DATE "trip_departure_date",
        (r.departure_time AT TIME ZONE 'America/Mexico_City')::TIME "trip_time",
        rs.name "route_name",
        rl.name "line_name",
        CASE
        WHEN rs.short_name ILIKE '%Intercity%' OR rs.short_name ILIKE '%Travel%' THEN 'Travel'
        ELSE 'City' END "vertical",
        r.seats "trip_seats",
        CASE
        when rr.id IS NULL THEN r.seat_price
        ELSE rr.cost END "trip_cost",
        rr.id "reservation_id",
        rr.seats "tickets_sold",
        rr.cost "ticket_cost",
        vv.short_name "van_name",
        rs.km_google "km"
        FROM routes_route r
        INNER JOIN routes_stopslayout rs on r.stops_id = rs.id AND
        (r.departure_time AT TIME ZONE 'America/Mexico_City' BETWEEN '{start_date}' AND '{end_date}') AND r.is_active AND
        NOT rs.is_private --AND NOT (rs.short_name ILIKE '%%Intercity%%' OR rs.short_name ILIKE '%%Travel%%')
        INNER JOIN routes_lines rl on rs.line_fk_id = rl.id AND NOT rl.name ILIKE 'Sigma Tepotzotlán' AND NOT rl.name ILIKE 'Flow Fest 2021' AND NOT rl.name ILIKE 'San Marcos Especial'
        INNER JOIN vans_van vv on r.van_id = vv.id
        LEFT JOIN reservations_reservation rr on r.id = rr.route_id AND rr.is_active AND rr.successfully_charged
--GROUP BY r.id,rs.id,rl.id
        )
        
WITH tabla AS(
        SELECT
        trip_id,
        trip_departure_date,
        trip_time,
        route_name,
        line_name,
        vertical,
        van_name,
        MAX(trip_seats) "trip_seats",
        MAX(trip_cost) "trip_cost",
        COUNT(DISTINCT reservation_id) "num_reservations",
        SUM(tickets_sold) "tickets_sold",
        SUM(ticket_cost) "revenue",
        MAX(km) "km",
        SUM(tickets_sold)/MAX(trip_seats) "occ"
    

FROM reservations
GROUP BY 1,2,3,4,5,6,7
)

SELECT 
trip_departure_date,
trip_time,
route_name,
line_name,
vertical,
van_name,
MAX(trip_seats) trip_seats,
MAX(trip_cost) "trip_cost",
MAX(num_reservations) "num_reservations",
MAX(tickets_sold) "tickets sold",
AVG(revenue) "revenue",
MAX(km) "km",
AVG(occ) "occ"

FROM tabla

GROUP BY line_name

CodePudding user response：

If you want to use multiple CTE the WITH clause only needs to add to the first one, others just need to use , instead of WITH

WITH reservations AS (
        SELECT r.id "trip_id",
        r.departure_time AT TIME ZONE 'America/Mexico_City' "trip_departure_time",
        (r.departure_time AT TIME ZONE 'America/Mexico_City')::DATE "trip_departure_date",
        (r.departure_time AT TIME ZONE 'America/Mexico_City')::TIME "trip_time",
        rs.name "route_name",
        rl.name "line_name",
        CASE
        WHEN rs.short_name ILIKE '%Intercity%' OR rs.short_name ILIKE '%Travel%' THEN 'Travel'
        ELSE 'City' END "vertical",
        r.seats "trip_seats",
        CASE
        when rr.id IS NULL THEN r.seat_price
        ELSE rr.cost END "trip_cost",
        rr.id "reservation_id",
        rr.seats "tickets_sold",
        rr.cost "ticket_cost",
        vv.short_name "van_name",
        rs.km_google "km"
        FROM routes_route r
        INNER JOIN routes_stopslayout rs on r.stops_id = rs.id AND
        (r.departure_time AT TIME ZONE 'America/Mexico_City' BETWEEN '{start_date}' AND '{end_date}') AND r.is_active AND
        NOT rs.is_private --AND NOT (rs.short_name ILIKE '%%Intercity%%' OR rs.short_name ILIKE '%%Travel%%')
        INNER JOIN routes_lines rl on rs.line_fk_id = rl.id AND NOT rl.name ILIKE 'Sigma Tepotzotlán' AND NOT rl.name ILIKE 'Flow Fest 2021' AND NOT rl.name ILIKE 'San Marcos Especial'
        INNER JOIN vans_van vv on r.van_id = vv.id
        LEFT JOIN reservations_reservation rr on r.id = rr.route_id AND rr.is_active AND rr.successfully_charged
), tabla AS(
    SELECT
            trip_id,
            trip_departure_date,
            trip_time,
            route_name,
            line_name,
            vertical,
            van_name,
            MAX(trip_seats) "trip_seats",
            MAX(trip_cost) "trip_cost",
            COUNT(DISTINCT reservation_id) "num_reservations",
            SUM(tickets_sold) "tickets_sold",
            SUM(ticket_cost) "revenue",
            MAX(km) "km",
            SUM(tickets_sold)/MAX(trip_seats) "occ"
    FROM reservations
    GROUP BY 1,2,3,4,5,6,7
)
SELECT 
    trip_departure_date,
    trip_time,
    route_name,
    line_name,
    vertical,
    van_name,
    MAX(trip_seats) trip_seats,
    MAX(trip_cost) "trip_cost",
    MAX(num_reservations) "num_reservations",
    MAX(tickets_sold) "tickets sold",
    AVG(revenue) "revenue",
    MAX(km) "km",
    AVG(occ) "occ"

FROM tabla
GROUP BY line_name