get_tariff_result

SELECT 
  ss.clientId,
  TIMESTAMP_DIFF(se.service.serviceEndAt, se.service.serviceStartAt, HOUR) AS duration,
  se.service.requirements.assetKindAlias as asset_kind,
  CASE 
    WHEN se.service.requirements.auxiliaries = 0 THEN false
    ELSE true
  END AS auxiliaries,
  ss.hubId,
  ers.municipality as destination,
  p.details.serviceId,
  p.details.driverId,
  CAST(p.details.totalAmount / 100 AS INT64)  as price,
  CAST(c.costTotal / 100 AS INT64) as cost,
  ss.name
FROM cabify-prodops.cabify_dw.logistics_pricing_ServiceBillableV1 p
INNER JOIN cabify-prodops.cabify_dw.logistics_pricing_LogisticsCostV1 c
  ON p.details.serviceId = c.serviceId 
  AND p.details.driverId = c.driverId
INNER JOIN `cabify-prodops.cabify_dw.service_offers_IndexServiceV2` ss 
  ON p.details.serviceId = ss.serviceId
INNER JOIN `cabify-prodops.cabify_dw.barad_dur_ExternalRouteServiceStartedV1` ers 
  ON ss.serviceId = ers.serviceId 
INNER JOIN `cabify-prodops.cabify_dw.service_offers_ServiceEventV1` se 
  ON ss.serviceID = se.service.serviceId
WHERE ss.deleted = false 
  AND ss.serviceType = "SERVICE_TYPE_EXTERNAL_ROUTE"
  GROUP BY 
  p.details.serviceId,
  p.details.driverId,
  ss.name,
  p.details.totalAmount,
  c.costTotal,
  ss.clientId,
  se.service.requirements.assetKindAlias,
  ss.hubId,
  ers.municipality,
  auxiliaries,
  TIMESTAMP_DIFF(se.service.serviceEndAt, se.service.serviceStartAt, HOUR)