-- source: https://github.com/tconbeer/sqlfmt/issues/343
WITH associated_selector AS (SELECT DISTINCT recent.int_flight_id AS anon_1 FROM net_leg_metrics AS recent LEFT OUTER JOIN net_leg_metrics AS xdays ON recent.int_flight_id = xdays.int_flight_id AND xdays.scenario_date = (('2022-12-22')::DATE - interval '7 days')::DATE AND true LEFT OUTER JOIN (SELECT flyr_cabin_class_hierarchy.cabin_class AS cabin_class, flyr_cabin_class_hierarchy.hierarchy AS hierarchy FROM (VALUES ('Y', 1), ('W', 2), ('J', 3), ('F', 4)) AS flyr_cabin_class_hierarchy (cabin_class, hierarchy)) AS flyr_cabin_class_hierarchy ON recent.cabin_class = flyr_cabin_class_hierarchy.cabin_class WHERE recent.client_cabin_class IN ('Y') AND recent.scenario_date = ('2022-12-22')::DATE), recent_leg_od AS (SELECT mappings_table.int_od_id AS int_od_id, sum(mappings_table.bookings_leg_od) AS bookings_leg_od, sum(mappings_table.revenue_leg_od) AS revenue_leg_od FROM net_od_leg_mapping AS mappings_table WHERE mappings_table.scenario_date = ('2022-12-22')::DATE AND mappings_table.int_flight_id IN (SELECT associated_selector.anon_1 FROM associated_selector) GROUP BY mappings_table.int_od_id), xdays_leg_od AS (SELECT mappings_table.int_od_id AS int_od_id, sum(mappings_table.bookings_leg_od) AS bookings_leg_od, sum(mappings_table.revenue_leg_od) AS revenue_leg_od FROM net_od_leg_mapping AS mappings_table WHERE mappings_table.scenario_date = (('2022-12-22')::DATE - interval '7 days')::DATE AND mappings_table.int_flight_id IN (SELECT associated_selector.anon_1 FROM associated_selector) GROUP BY mappings_table.int_od_id)  SELECT round(CAST((round(CAST(sum(xdays_leg_od.bookings_leg_od) AS NUMERIC), 0) / nullif(round(CAST(sum(xdays.bookings) AS NUMERIC), 0), 0)) * 100 AS NUMERIC), 0) AS x_day_leg_od_bookings_share_percent_to_x_day_bookings, mode() WITHIN GROUP (ORDER BY recent.comp_fare_cirrus_match_airline DESC) AS comp_fare_cirrus_match_airline, round(CAST(round(CAST(sum(recent.bookings) AS NUMERIC), 0) - round(CAST(sum(recent.bookings_baseline) AS NUMERIC), 0) AS NUMERIC), 0) AS net_bookings_diff_to_baseline, CASE WHEN (sum(xdays_leg_od.bookings_leg_od) = 0) THEN 0 ELSE round(CAST(sum(xdays_leg_od.revenue_leg_od) / sum(xdays_leg_od.bookings_leg_od) AS NUMERIC), 0) END AS x_day_leg_od_revenue_per_booking, round(CAST(sum(recent_leg_od.revenue_leg_od) - sum(xdays_leg_od.revenue_leg_od) AS NUMERIC), 0) AS x_day_leg_od_revenue_build, round(CAST(sum(recent.bookings) AS NUMERIC), 0) AS net_bookings, round(CAST(avg(recent.wtp_lac_price) AS NUMERIC), 0) AS wtp_fare, round(CAST(((sum(recent.final_revenue_expected) * 100) / nullif(sum(recent.final_revenue_baseline), 0) - 100) - ((sum(xdays.final_revenue_expected) * 100) / nullif(sum(xdays.final_revenue_baseline), 0) - 100) AS NUMERIC), 1) AS x_day_final_revenue_expected_build_percent_diff_to_baseline, round(CAST(sum(recent_leg_od.revenue_leg_od) AS NUMERIC), 0) AS leg_od_revenue, round(CAST(avg(recent.target_price_frm) AS NUMERIC), 0) AS target_fare_frm, round(CAST(CASE WHEN (sum(recent_leg_od.bookings_leg_od) = 0) THEN 0 ELSE sum(recent_leg_od.revenue_leg_od) / sum(recent_leg_od.bookings_leg_od) END - CASE WHEN (sum(xdays_leg_od.bookings_leg_od) = 0) THEN 0 ELSE sum(xdays_leg_od.revenue_leg_od) / sum(xdays_leg_od.bookings_leg_od) END AS NUMERIC), 0) AS x_day_leg_od_revenue_per_booking_build, sum(CASE WHEN (recent.target_price != recent.target_price_frm AND recent.wtp_lac_price != recent.target_price_frm) THEN 1 ELSE 0 END) AS number_of_impacted_subjects, round(CAST(sum(recent.revenue) AS NUMERIC), 0) AS revenue, round(CAST(avg(recent.target_price) AS NUMERIC), 0) AS target_fare, round(CAST(avg(recent.lowest_vff * fx_rates_for_lowest_vff.exchange_rate) AS NUMERIC), 0) AS lowest_vff, CASE WHEN (sum(recent_leg_od.bookings_leg_od) = 0) THEN 0 ELSE round(CAST(sum(recent_leg_od.revenue_leg_od) / sum(recent_leg_od.bookings_leg_od) AS NUMERIC), 0) END AS leg_od_revenue_per_booking, round(CAST(avg(recent.comp_fare_cirrus_match_fare * fx_rates_comp_fare_cirrus_match_currency.exchange_rate) AS NUMERIC), 0) AS comp_fare_cirrus_match_fare, max(recent.rt_market) AS rt_market, round(CAST(sum(xdays_leg_od.revenue_leg_od) AS NUMERIC), 0) AS x_day_leg_od_revenue, round(CAST(sum(recent.final_revenue_expected) AS NUMERIC), 0) AS final_revenue_expected, round(CAST(sum(recent_leg_od.bookings_leg_od) AS NUMERIC), 0) AS leg_od_bookings, round(CAST(sum(recent.revenue) - sum(recent.revenue_baseline) AS NUMERIC), 0) AS diff_to_erb, round(CAST(sum(recent.final_bookings_expected) AS NUMERIC), 0) AS final_net_bookings_expected, round(CAST(avg(recent.wtp_lac_price_frm) AS NUMERIC), 0) AS wtp_fare_frm, round(CAST((sum(xdays_leg_od.revenue_leg_od) / nullif(sum(xdays.revenue), 0)) * 100 AS NUMERIC), 0) AS x_day_leg_od_revenue_share_percent_to_x_day_revenue, round(CAST(avg(recent.wtp_lac_rank) AS NUMERIC), 1) AS wtp_lac_rank, round(CAST(round(CAST(sum(recent.final_bookings_expected) AS NUMERIC), 0) - round(CAST(sum(xdays.final_bookings_expected) AS NUMERIC), 0) AS NUMERIC), 0) AS x_day_final_net_bookings_expected_build, round(CAST((round(CAST(sum(recent_leg_od.bookings_leg_od) AS NUMERIC), 0) / nullif(round(CAST(sum(recent.bookings) AS NUMERIC), 0), 0)) * 100 AS NUMERIC), 0) AS leg_od_bookings_share_percent_to_bookings, round(CAST(round(CAST(sum(recent_leg_od.bookings_leg_od) AS NUMERIC), 0) - round(CAST(sum(xdays_leg_od.bookings_leg_od) AS NUMERIC), 0) AS NUMERIC), 0) AS x_day_leg_od_bookings_build, max(recent.client_cabin_class) AS cabin_class, round(CAST((sum(recent.final_revenue_expected) * 100) / nullif(sum(recent.final_revenue_baseline), 0) - 100 AS NUMERIC), 1) AS final_revenue_expected_percent_diff_to_baseline, count(*) AS number_of_subjects, round(CAST((sum(recent_leg_od.revenue_leg_od) / nullif(sum(recent.revenue), 0)) * 100 AS NUMERIC), 0) AS leg_od_revenue_share_percent_to_revenue, round(CAST(avg(CASE WHEN (recent.lowest_vff * fx_rates_for_lowest_vff.exchange_rate = recent.wtp_lac_price) THEN 100 ELSE 0 END) AS NUMERIC), 1) AS lowest_vff_share, round(CAST(avg(recent.wtp_lac_price) - avg(recent.lowest_vff * fx_rates_for_lowest_vff.exchange_rate) AS NUMERIC), 0) AS wtp_fare_diff_to_lowest_vff, round(CAST(sum(xdays_leg_od.bookings_leg_od) AS NUMERIC), 0) AS x_day_leg_od_bookings FROM net_od_metrics AS recent LEFT OUTER JOIN net_od_metrics AS xdays ON recent.int_od_id = xdays.int_od_id AND xdays.scenario_date = (('2022-12-22')::DATE - interval '7 days')::DATE AND true JOIN recent_leg_od ON recent_leg_od.int_od_id = recent.int_od_id LEFT OUTER JOIN xdays_leg_od ON xdays_leg_od.int_od_id = xdays.int_od_id LEFT OUTER JOIN (SELECT flyr_cabin_class_hierarchy.cabin_class AS cabin_class, flyr_cabin_class_hierarchy.hierarchy AS hierarchy FROM (VALUES ('Y', 1), ('W', 2), ('J', 3), ('F', 4)) AS flyr_cabin_class_hierarchy (cabin_class, hierarchy)) AS flyr_cabin_class_hierarchy ON recent.cabin_class = flyr_cabin_class_hierarchy.cabin_class LEFT OUTER JOIN fx_rates AS fx_rates_comp_fare_cirrus_match_currency ON fx_rates_comp_fare_cirrus_match_currency.from_currency = recent.comp_fare_cirrus_match_currency AND fx_rates_comp_fare_cirrus_match_currency.to_currency = 'USD' LEFT OUTER JOIN fx_rates AS fx_rates_for_lowest_vff ON fx_rates_for_lowest_vff.from_currency = recent.currency AND fx_rates_for_lowest_vff.to_currency = 'USD' WHERE true AND recent.scenario_date = ('2022-12-22')::DATE GROUP BY recent.rt_market, recent.client_cabin_class HAVING true ORDER BY round(CAST(sum(recent_leg_od.revenue_leg_od) AS NUMERIC), 0) DESC NULLS LAST, max(recent.dep_date) ASC, max(recent.origin) ASC, max(recent.destination) ASC, max(recent.dep_time) ASC, max(flyr_cabin_class_hierarchy.hierarchy) ASC  LIMIT 25 OFFSET 0