get_tariff_result
The snippet can be accessed without any authentication.
Authored by
Emanuel Mosegue
Edited
get_tariff_result.sql 1.37 KiB
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)
Please register or sign in to comment