Geospatial and Time-Series Data Questions

-- Ensure indexes:
-- CREATE INDEX ON listings USING gist (st_setsrid(st_makepoint(lon, lat), 4326));
-- CREATE INDEX ON searches USING gist (st_setsrid(st_makepoint(lon, lat), 4326));

WITH search_points AS (
  SELECT s.*, st_setsrid(st_makepoint(s.lon, s.lat), 4326) AS geom
  FROM searches s
)
SELECT
  sp.*,
  l.listing_id,
  round(st_distance_sphere(sp.geom, l.geom)::numeric/1000, 3) AS distance_km,
  l.price_usd
FROM search_points sp
LEFT JOIN LATERAL (
  SELECT listing_id, price_usd, st_setsrid(st_makepoint(lon, lat),4326) AS geom
  FROM listings
  WHERE is_active
    AND st_dwithin(sp.geom::geography, st_setsrid(st_makepoint(lon, lat),4326)::geography, 5000)
  ORDER BY st_distance_sphere(sp.geom, st_setsrid(st_makepoint(lon, lat),4326))
  LIMIT 5
) l ON true;

WITH results_per_search AS (
  -- reuse previous lateral logic but count matches
  SELECT sp.city, sp.search_id,
    COUNT(l.listing_id) AS matches
  FROM search_points sp
  LEFT JOIN LATERAL (
    SELECT 1 FROM listings
    WHERE is_active
      AND st_dwithin(sp.geom::geography, st_setsrid(st_makepoint(lon, lat),4326)::geography, 5000)
    ORDER BY st_distance_sphere(sp.geom, st_setsrid(st_makepoint(lon, lat),4326))
    LIMIT 5
  ) l ON true
  GROUP BY sp.city, sp.search_id
)
SELECT
  city,
  100.0 * SUM(CASE WHEN matches >= 3 THEN 1 ELSE 0 END)::float / COUNT(*) AS pct_searches_with_>=3
FROM results_per_search
GROUP BY city;