Geospatial SQL Analysis: Store Cannibalization and Market Coverage,

Write geospatial SQL queries for [COMPANY NAME]'s retail network with [NUMBER] stores and [NUMBER] customer records.

Tables:
- `stores` (id, name, lat, lon, city, state, monthly_revenue, open_date, size_sqft)
- `customers` (id, lat, lon, city, state, total_spend, last_purchase, store_id)
- `transactions` (id, customer_id, store_id, amount, date)

**Query 1 — Distance Matrix:**
- Distance between every pair of stores (Haversine formula or ST_Distance)
- Identify stores within [X] km of each other (cannibalization risk)
- For each close pair: combined revenue vs. expected revenue if independent

**Query 2 — Customer Catchment Areas:**
- Assign each customer to nearest store
- Calculate: average distance to store, % of customers within 5km/10km/20km
- Revenue per customer by distance band
- Identify customers traveling >[X] km (potential new store locations)

**Query 3 — Market Coverage Analysis:**
- Population coverage: % of target population within [X] km of a store
- Revenue density: revenue per km² by region
- Underserved areas: high population, low store density
- Over-served areas: multiple stores competing for same customer base

**Query 4 — Cannibalization Impact:**
- When a new store opened, how much did nearby stores' revenue drop?
- Compare revenue trend of stores near new openings vs. isolated stores
- Estimate cannibalization rate: % of new store revenue that came from existing stores

**Query 5 — Optimal Location Recommendation:**
- Score candidate locations by: population density, distance to nearest store, competitor presence, demographic fit
- Rank top [NUMBER] locations for expansion

SQL dialect: PostgreSQL with PostGIS extensions.