Geospatial SQL Analysis: Store Cannibalization and Market Coverage,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n

Write geospatial SQL queries for [COMPANY NAME]'s retail network with [NUMBER] stores and [NUMBER] customer records.\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nTables:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`stores\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` (id, name, lat, lon, city, state, monthly_revenue, open_date, size_sqft)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`customers\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` (id, lat, lon, city, state, total_spend, last_purchase, store_id)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`transactions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` (id, customer_id, store_id, amount, date)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 1 — Distance Matrix:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Distance between every pair of stores (Haversine formula or ST_Distance)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Identify stores within [X] km of each other (cannibalization risk)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- For each close pair: combined revenue vs. expected revenue if independent\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 2 — Customer Catchment Areas:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Assign each customer to nearest store\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Calculate: average distance to store, % of customers within 5km/10km/20km\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Revenue per customer by distance band\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Identify customers traveling >[X] km (potential new store locations)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 3 — Market Coverage Analysis:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Population coverage: % of target population within [X] km of a store\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Revenue density: revenue per km² by region\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Underserved areas: high population, low store density\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Over-served areas: multiple stores competing for same customer base\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 4 — Cannibalization Impact:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- When a new store opened, how much did nearby stores' revenue drop?\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Compare revenue trend of stores near new openings vs. isolated stores\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Estimate cannibalization rate: % of new store revenue that came from existing stores\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 5 — Optimal Location Recommendation:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Score candidate locations by: population density, distance to nearest store, competitor presence, demographic fit\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Rank top [NUMBER] locations for expansion\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSQL dialect: PostgreSQL with PostGIS extensions.