Airbnb-Specific Data Patterns Questions

from pyspark.sql import functions as F, Window

# assume bookings: user_id, listing_id, booked_at (timestamp)
tz = "America/Los_Angeles"  # business timezone
# normalize to timezone and date
b = bookings.withColumn("booked_ts", F.from_utc_timestamp("booked_at", tz))
b = b.withColumn("week_start", F.date_trunc("week", F.col("booked_ts"))) 

# user first booking (user-level dedupe)
first = b.groupBy("user_id").agg(F.min("week_start").alias("cohort_week"))

# join back and compute week_index
joined = b.select("user_id", "week_start").distinct() \
    .join(first, on="user_id") \
    .withColumn("week_index", F.floor(F.datediff("week_start", "cohort_week"))) \
    .filter(F.col("week_index").between(0,12))

# cohort sizes
cohort_sizes = first.groupBy("cohort_week").agg(F.countDistinct("user_id").alias("cohort_users"))

# retention counts
ret = joined.groupBy("cohort_week", "week_index").agg(F.countDistinct("user_id").alias("retained_users"))

# retention rates
result = ret.join(cohort_sizes, on="cohort_week") \
    .withColumn("retention_rate", F.col("retained_users")/F.col("cohort_users")) \
    .orderBy("cohort_week","week_index")

WITH booked AS (
  SELECT
    user_id,
    DATE_TRUNC(DATE(TIMESTAMP(booked_at), 'America/Los_Angeles'), WEEK) AS week_start
  FROM bookings
),
first_cohort AS (
  SELECT user_id, MIN(week_start) AS cohort_week
  FROM booked
  GROUP BY user_id
),
user_weeks AS (
  SELECT DISTINCT b.user_id, b.week_start,
    DATE_DIFF(b.week_start, f.cohort_week, WEEK) AS week_index,
    f.cohort_week
  FROM booked b JOIN first_cohort f USING (user_id)
)
SELECT
  cohort_week,
  week_index,
  COUNT(DISTINCT user_id) AS retained_users,
  (COUNT(DISTINCT user_id) / NULLIF((SELECT COUNT(*) FROM first_cohort fc WHERE fc.cohort_week = uw.cohort_week),0)) AS retention_rate
FROM user_weeks uw
WHERE week_index BETWEEN 0 AND 12
GROUP BY cohort_week, week_index
ORDER BY cohort_week, week_index;