Ride-Hailing Business Metrics Calculation and Understanding Questions

WITH trips_month AS (
  SELECT
    t.*,
    DATE_TRUNC('month', t.occurred_at) AS month
  FROM trips t
),
city_month_stats AS (
  SELECT
    city, month,
    COUNT(*) AS trip_count,
    f.fixed_cost
  FROM trips_month m
  LEFT JOIN monthly_fixed_costs f
    ON m.city = f.city AND m.month = f.month
  GROUP BY city, month, f.fixed_cost
),
trip_costs AS (
  SELECT
    t.trip_id, t.city, t.month, t.gross_fare,
    dp.driver_payout, i.incentive,
    (cm.fixed_cost::numeric / NULLIF(cm.trip_count,0)) AS alloc_fixed_per_trip
  FROM trips_month t
  LEFT JOIN driver_payouts dp USING (trip_id)
  LEFT JOIN incentives i USING (trip_id)
  JOIN city_month_stats cm
    ON t.city = cm.city AND t.month = cm.month
)
SELECT
  trip_id,
  gross_fare,
  COALESCE(driver_payout,0) AS driver_payout,
  COALESCE(incentive,0) AS incentive,
  COALESCE(alloc_fixed_per_trip,0) AS alloc_fixed_per_trip,
  gross_fare - COALESCE(driver_payout,0) - COALESCE(incentive,0) - COALESCE(alloc_fixed_per_trip,0) AS contribution_margin
FROM trip_costs;

import pandas as pd

trips['month'] = trips['occurred_at'].dt.to_period('M').dt.to_timestamp()
# join payouts and incentives
df = trips.merge(driver_payouts, on='trip_id', how='left') \
          .merge(incentives, on='trip_id', how='left') \
          .merge(fixed_costs, left_on=['city','month'], right_on=['city','month'], how='left')
# compute trips per city-month
counts = df.groupby(['city','month']).trip_id.transform('count')
df['alloc_fixed_per_trip'] = df['fixed_cost'] / counts
df[['gross_fare','driver_payout','incentive','alloc_fixed_per_trip']] = df[['gross_fare','driver_payout','incentive','alloc_fixed_per_trip']].fillna(0)
df['contribution_margin'] = df['gross_fare'] - df['driver_payout'] - df['incentive'] - df['alloc_fixed_per_trip']

WITH installs_cohort AS (
  SELECT
    user_id,
    date_trunc('week', install_date)::date AS install_week,
    channel,
    install_date
  FROM installs
),
week_series AS (
  SELECT generate_series(0,12) AS week_number
),
-- transactions attributed to user install channel and compute week offset
tx_enriched AS (
  SELECT
    i.install_week,
    i.channel,
    i.user_id,
    t.tx_date::date AS tx_date,
    t.amount,
    FLOOR(EXTRACT(EPOCH FROM (t.tx_date::date - i.install_date)) / 86400 / 7)::int AS week_number
  FROM installs_cohort i
  LEFT JOIN transactions t
    ON t.user_id = i.user_id
  WHERE t.tx_date IS NULL OR t.tx_date::date >= i.install_date::date
),
-- cohort sizes
cohort_sizes AS (
  SELECT install_week, channel, COUNT(DISTINCT user_id) AS cohort_size
  FROM installs_cohort
  GROUP BY 1,2
),
-- per-cohort-week metrics: active users (>=1 tx in that week) and revenue in that week
cohort_week AS (
  SELECT
    i.install_week,
    i.channel,
    w.week_number,
    cs.cohort_size,
    COUNT(DISTINCT CASE WHEN te.week_number = w.week_number THEN te.user_id END) AS active_users,
    COALESCE(SUM(CASE WHEN te.week_number = w.week_number THEN te.amount END),0) AS revenue_in_week
  FROM (SELECT DISTINCT install_week, channel FROM installs_cohort) i
  CROSS JOIN week_series w
  LEFT JOIN tx_enriched te
    ON te.install_week = i.install_week
    AND te.channel = i.channel
    AND te.week_number BETWEEN 0 AND 12
  LEFT JOIN cohort_sizes cs
    ON cs.install_week = i.install_week AND cs.channel = i.channel
  GROUP BY 1,2,3,4
),
-- cumulative revenue up to each week per cohort
cohort_cume AS (
  SELECT
    cw.install_week,
    cw.channel,
    cw.week_number,
    cw.cohort_size,
    cw.active_users,
    cw.revenue_in_week,
    SUM(cw.revenue_in_week) OVER (PARTITION BY cw.install_week, cw.channel ORDER BY cw.week_number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
  FROM cohort_week cw
)
SELECT
  install_week,
  channel,
  week_number,
  CASE WHEN cohort_size = 0 THEN 0.0 ELSE active_users::numeric / cohort_size END AS retention_rate,
  CASE WHEN cohort_size = 0 THEN 0.0 ELSE cumulative_revenue::numeric / cohort_size END AS cumulative_ltv
FROM cohort_cume
ORDER BY install_week, channel, week_number;

SELECT
  r.city,
  COUNT(*)                         AS rides_count,
  SUM(company_revenue)             AS total_company_revenue,
  AVG(company_revenue)             AS avg_company_revenue_per_ride,
  SUM(gross_fare)                  AS total_gross_fare,
  -- take-rate = total company revenue divided by total gross fares; NULL if total_gross_fare = 0
  CASE WHEN SUM(gross_fare) = 0 THEN NULL
       ELSE SUM(company_revenue) / SUM(gross_fare)
  END                               AS company_take_rate
FROM (
  SELECT
    r.trip_id,
    r.city,
    -- normalize nulls to 0
    COALESCE(r.gross_fare, 0)       AS gross_fare,
    COALESCE(r.discount_amount, 0)  AS discount_amount,
    COALESCE(dp.driver_payout, 0)   AS driver_payout,
    -- company keeps fare minus discounts and driver payout
    (COALESCE(r.gross_fare, 0) - COALESCE(r.discount_amount, 0) - COALESCE(dp.driver_payout, 0))
                                    AS company_revenue
  FROM rides r
  LEFT JOIN driver_payouts dp USING (trip_id)
  WHERE r.start_time >= CURRENT_DATE - INTERVAL '30' DAY
    -- exclude clearly refunded or invalid trips (assumption)
    AND COALESCE(r.gross_fare, 0) > 0
) t
GROUP BY r.city
ORDER BY total_gross_fare DESC;