Airbnb-Specific Data Patterns Questions

WITH normalized_bookings AS (
  SELECT
    booking_id,
    user_id,
    -- normalize timestamp to UTC (Postgres syntax example)
    (booked_at AT TIME ZONE 'UTC') AS booked_utc,
    status
  FROM bookings
  WHERE status = 'confirmed'
),
-- optional: map users to canonical users if identity_map exists
canonical_users AS (
  -- if you have an identity resolution table: identity_map(external_id, canonical_user_id)
  -- otherwise canonical_user_id = user_id and we later dedupe by earliest created_at
  SELECT user_id AS canonical_user_id, user_id FROM users
),
bookings_canonical AS (
  SELECT b.booking_id, c.canonical_user_id AS user_id, b.booked_utc
  FROM normalized_bookings b
  JOIN canonical_users c USING (user_id)
),
first_booking AS (
  SELECT
    user_id,
    MIN(booked_utc) AS first_booked_utc
  FROM bookings_canonical
  GROUP BY user_id
),
cohorts AS (
  SELECT
    f.user_id,
    f.first_booked_utc,
    DATE_TRUNC('week', f.first_booked_utc) AS first_booking_week
  FROM first_booking f
  -- exclude bots by heuristics (example): created_at too recent + >100 bookings/day, or email domain
  JOIN users u ON u.user_id = f.user_id
  WHERE NOT (
    u.email LIKE '%@botdomain.com' OR u.is_bot = TRUE
  )
),
subsequent AS (
  SELECT
    c.user_id,
    c.first_booking_week,
    -- did user have any booking in (0,7] days after first
    MAX(CASE WHEN b.booked_utc > c.first_booked_utc
                 AND b.booked_utc <= c.first_booked_utc + INTERVAL '7 day' THEN 1 ELSE 0 END) AS retained_7,
    MAX(CASE WHEN b.booked_utc > c.first_booked_utc
                 AND b.booked_utc <= c.first_booked_utc + INTERVAL '30 day' THEN 1 ELSE 0 END) AS retained_30
  FROM cohorts c
  LEFT JOIN bookings_canonical b
    ON b.user_id = c.user_id
  GROUP BY c.user_id, c.first_booking_week
)
SELECT
  first_booking_week,
  COUNT(*) AS cohort_size,
  SUM(retained_7) AS retained_7_count,
  ROUND(100.0 * SUM(retained_7) / COUNT(), 2) AS retained_7_pct,
  SUM(retained_30) AS retained_30_count,
  ROUND(100.0 * SUM(retained_30) / COUNT(), 2) AS retained_30_pct
FROM subsequent
GROUP BY first_booking_week
ORDER BY first_booking_week;