Ride-Hailing Data Modeling & Analytics Requirements Questions

from h3 import h3
def map_event_to_cells(lat, lng):
    res_list = [7,8,9]
    base = h3.geo_to_h3(lat,lng,9)  # highest-res cell
    return {r: h3.h3_to_parent(base, r) for r in res_list}

-- Standard SQL (works in BigQuery / Snowflake)
SELECT
  t.rider_id,
  SUM(ROUND((t.fare_amount_cents - COALESCE(t.refund_cents, 0)) * usd_rate_for_trip)) AS ltv_usd_cents
FROM trips t
CROSS JOIN -- no-op; keeps query portable between engines
(
  -- placeholder so correlated subqueries below can refer to this alias in some engines;
  SELECT 1 AS one
) _dummy
LEFT JOIN LATERAL
(
  -- determine usd_rate to use for this trip:
  -- 1) most recent rate with date <= trip date
  -- 2) if none, the most recent rate available for that currency
  SELECT f1.usd_rate AS usd_rate_for_trip
  FROM fx_rates f1
  WHERE f1.currency = t.currency
    AND f1.date = (
      SELECT MAX(f2.date)
      FROM fx_rates f2
      WHERE f2.currency = t.currency
        AND f2.date <= DATE(t.occurred_at)
    )
  LIMIT 1
) fx1 ON TRUE
LEFT JOIN LATERAL
(
  -- fallback: if fx1 is null (no rate on-or-before trip), pick latest available
  SELECT f3.usd_rate AS usd_rate_last_known
  FROM fx_rates f3
  WHERE f3.currency = t.currency
  ORDER BY f3.date DESC
  LIMIT 1
) fx2 ON fx1.usd_rate_for_trip IS NULL
CROSS JOIN
(
  -- final USD rate, prefer fx1 then fx2; if still NULL, treat as 0 (or you may choose to exclude)
  SELECT 1
) _join
-- compute usd_rate_for_trip using COALESCE of the two lateral results
-- note: use a sub-select in SELECT to coalesce the lateral joins' outputs
WHERE t.occurred_at >= CURRENT_TIMESTAMP() - INTERVAL '365' DAY
GROUP BY t.rider_id
HAVING SUM(ROUND((t.fare_amount_cents - COALESCE(t.refund_cents, 0)) *
       COALESCE(
         (SELECT fx1.usd_rate_for_trip FROM (SELECT 1) AS s WHERE TRUE), -- placeholder; see note
         (SELECT fx2.usd_rate_last_known FROM (SELECT 1) AS s WHERE TRUE),
         0
       )
     )) IS NOT NULL;