## Problem You have a table of geographic points. Write a SQL query to return all points within a given radius (in kilometers) of a target latitude/longitude, ordered by distance ascending. ```sql -- Table: points -- id INT, name VARCHAR, lat FLOAT, lon FLOAT -- Use the Haversine approximation formula: -- distance_km = 6371 * acos( -- cos(radians(:target_lat)) * cos(radians(lat)) -- * cos(radians(lon) - radians(:target_lon)) -- + sin(radians(:target_lat)) * sin(radians(lat)) -- ) ``` **Example query parameters:** `target_lat=37.7749, target_lon=-122.4194, radius_km=10` **Expected columns:** `id, name, lat, lon, distance_km` ```sql SELECT id, name, lat, lon, 6371 * acos(...) AS distance_km FROM points HAVING distance_km <= :radius_km ORDER BY distance_km ASC; ``` ## Follow-ups 1. Why must you use `HAVING` instead of `WHERE` when filtering on the computed `distance_km` alias? 2. At what scale would this full-table scan become a bottleneck, and what index type (e.g., spatial index, geohash) would you add? 3. How does the Haversine formula break down near the poles, and what formula is more accurate? 4. Rewrite this query to also return a bounding box check first as a pre-filter before the expensive `acos` computation.

InterviewDB Experience

Points Query: SQL to Find All Points Within a Given Radius of a Location

phone

Interview Experience

Problem You have a table of geographic points. Write a SQL query to return all points within a given radius (in kilometers) of a target latitude/longitude, ordered by distance ascending. Example query parameters: target_lat=37.7749, target_lon=-122.4194, radius_km=10 Expected columns: id, name, lat, lon, distance_km Follow-ups Why must you use HAVING instead of WHERE when filtering on the computed distance_km alias? At what scale would this full-table scan become a bottleneck, and what index typ…

Full Details

🔒

Unlock all Applied Intuition questions

Full insider details, leaked discussions, and candidate experiences.

Get full access — $100 a year, unlimited access

About This Question

This is a candidate experience report from a applied intuition interview during the phone round.

It covers the following topics: Onsite, Sql, Phone, Coding .

Topics

Onsite Sql Phone Coding

Points Query: SQL to Find All Points Within a Given Radius of a Location

Interview Experience

Full Details

About This Question

Topics

More Applied Intuition Interview Questions