Query optimisation - how to reference lateral join table to avoid duplicate lookup

First time posting here but long time reader. I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in javascript but don't play well with SQL. I'm trying to improve the query below. The goal of the query is to produce a table listing a merchant's vending machines and the total amounts and counts taken through different gateways (eg AMEX, VISA, EBT, COUPON, NULL[Cash]) for accounting periods. I've reproduced it fully although I've altered the gateway names to ABC / XYZ for example purposes. Clearly there is plenty of potential for improvement but where I would specifically would like to ask for help is this: The query has two lateral joins that are essentially identical. vend_total and vend_partial. The only distinction is in the timestamp- and "vend"."timestamp" <= cash_accounting_period_ids.closed_at AS vend_total - or between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at AS vend_partial To my mind that makes vend_partial just a subset of vend_total. I would like to rewrite the query so that it does not have the obvious duplication of filters and does not fetch data twice, but I cannot wrap my head around how. It seems to me I should be able to reference vend_total as the basis of vend_partial instead of duplicating it, or instead rewriting the whole filter aggregate "totals" as a cte and then reference that, but tbh the complexity of this query is beating me. I'd really appreciate any suggestions people might have as to how to streamline this query, and more than that I'd love some recommendations for resources or strategies for query analysis and optimisation. I'm new to this and I enjoy the challenge but I work for a small startup and at this point I'm apparently the SQL expert so will take any advice I can get! George.

with "cash_accounting_period_ids" as (select "cash_accounting_period".*
                                      from "cash_accounting_period"
                                      where "cash_accounting_period"."merchant_id" = ?1
                                        and "cash_accounting_period"."closed_at" is not null
                                      order by closed_at DESC NULLS FIRST, cash_accounting_period.created_at DESC
                                      limit ?2)
select "cash_accounting_period_ids".*,
       (select row_to_json(vm)
        from (select vending_machine.*, ST_AsGeoJSON(location) as location) as vm) AS "vendingMachine",
       row_to_json("verified_by".*)                                                AS "verifiedBy",
       row_to_json("closed_by".*)                                                  AS "closedBy",
       row_to_json("company".*)                                                    AS "client",
       row_to_json(vend_total.*)                                                   AS "vendTotal",
       row_to_json(vend_partial.*)                                                 AS "vendPartial",
       row_to_json(route.*)                                                        AS route
from "cash_accounting_period_ids"
         left join "vending_machine" on "vending_machine"."id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "user" AS "verified_by" on "verified_by"."id" = "cash_accounting_period_ids"."verified_by"
         left join "user" AS "closed_by" on "closed_by"."id" = "cash_accounting_period_ids"."closed_by"
         left join "company" on "company"."id" = "cash_accounting_period_ids"."client_id"
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" <= cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_total ON TRUE
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_partial ON TRUE
         left join "route_vending_machine"
                   on "route_vending_machine"."vending_machine_id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "route" on "route"."id" = "route_vending_machine"."route_id";

EXPLAIN (ANALYSE, BUFFERS) is too long to share here in post or comment so I dumped it here: https://justpaste.it/bolah