Airbnb-Specific Data Patterns Questions

WITH ordered AS (
  SELECT
    user_id,
    occurred_at,
    event_type,
    -- time (in seconds) since previous event for same user
    EXTRACT(EPOCH FROM (occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at))) AS seconds_since_prev
  FROM events
),
boundaries AS (
  SELECT
    user_id,
    occurred_at,
    -- start a new session when no previous event OR gap > 30 minutes (1800s)
    CASE WHEN seconds_since_prev IS NULL OR seconds_since_prev > 1800 THEN 1 ELSE 0 END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    user_id,
    occurred_at,
    -- cumulative session number per user
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at ROWS UNBOUNDED PRECEDING) AS session_num
  FROM boundaries
)
SELECT
  -- deterministic session id: md5 of user_id + session_start (ISO)
  md5(user_id || '|' || TO_CHAR(MIN(occurred_at) OVER (PARTITION BY user_id, session_num), 'YYYY-MM-DD"T"HH24:MI:SS')) AS session_id,
  user_id,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count
FROM sessionized
GROUP BY user_id, session_num
ORDER BY user_id, session_start;

WITH city_bbox AS (
  SELECT
    ST_Transform(ST_Extent(ST_SetSRID(ST_MakePoint(lon, lat), 4326)), 3857) AS geom_3857
  FROM listings
  WHERE city = 'TargetCity'
),
grid AS (
  SELECT
    ST_SetSRID(
      ST_MakeBox2D(
        ST_Point(x, y),
        ST_Point(x + 1000, y + 1000)
      ), 3857
    ) AS cell_geom_3857,
    x AS x0, y AS y0
  FROM city_bbox,
  generate_series(
    floor(ST_XMin(city_bbox.geom_3857)/1000)*1000,
    ceil(ST_XMax(city_bbox.geom_3857)/1000)*1000 - 1000,
    1000
  ) AS gs_x(x),
  generate_series(
    floor(ST_YMin(city_bbox.geom_3857)/1000)*1000,
    ceil(ST_YMax(city_bbox.geom_3857)/1000)*1000 - 1000,
    1000
  ) AS gs_y(y)
),
listings_in_cells AS (
  SELECT g.x0, g.y0, g.cell_geom_3857,
         l.listing_id,
         ST_Transform(ST_SetSRID(ST_MakePoint(l.lon, l.lat),4326),3857) AS pt
  FROM grid g
  JOIN listings l
    ON l.city = 'TargetCity'
   AND ST_Contains(g.cell_geom_3857, ST_Transform(ST_SetSRID(ST_MakePoint(l.lon, l.lat),4326),3857))
),
agg AS (
  SELECT
    x0, y0,
    COUNT(DISTINCT listing_id) AS listings_count,
    COUNT(b.booking_id) FILTER (WHERE b.booking_id IS NOT NULL) AS bookings_count
  FROM listings_in_cells lic
  LEFT JOIN bookings b ON b.listing_id = lic.listing_id
  GROUP BY x0, y0
)
SELECT
  ST_Transform(ST_Centroid(cell_geom_3857), 4326) AS cell_centroid_wgs84,
  listings_count,
  bookings_count,
  (bookings_count::float / NULLIF(listings_count,0)) AS conversion_rate
FROM agg
JOIN grid USING (x0,y0)
WHERE listings_count >= 50
ORDER BY conversion_rate DESC
LIMIT 3;