WITH billed_by_courier AS (
  SELECT
    vendor_ref
  FROM
    accounting_vendor_billables
  INNER JOIN
  	accounting_vendor_invoices ON accounting_vendor_invoices.id = accounting_vendor_billables.vendor_invoice_id
  WHERE
  	accounting_vendor_invoices.type IN ('Accounting::VendorInvoices::UPS', 'Accounting::VendorInvoices::DPD')
  GROUP BY
    vendor_ref
), billed_by_via AS (
  SELECT
    (array_agg(date_trunc('day', accounting_billables.created_at)))[1] AS billed_at,
    vendor_ref,
    SUM(amount) AS sum_amount,
    (array_agg(last_mile_deliveries.service))[1] AS service
  FROM
  	accounting_billables
  INNER JOIN
  	last_mile_deliveries ON last_mile_deliveries.id = accounting_billables.parent_id AND accounting_billables.parent_type = 'LastMile::Delivery'
  WHERE
  	last_mile_deliveries.service != 'FCE'
  	AND accounting_billables.vendor_ref IS NOT NULL
	AND accounting_billables.created_at > NOW() - INTERVAL '90 days'
  GROUP BY vendor_ref
)

SELECT
	SUM(sum_amount)
FROM
	billed_by_via
WHERE
	NOT EXISTS (
	  SELECT 1 FROM billed_by_courier WHERE billed_by_courier.vendor_ref = billed_by_via.vendor_ref
	)
GROUP BY service