Core Entities
Property: property_id, address, city, state, zip, lat, lng, type (APARTMENT, HOUSE, CONDO, TOWNHOUSE), status (FOR_SALE, FOR_RENT, SOLD, OFF_MARKET), price, bedrooms, bathrooms, area_sqft, year_built, description, features (JSON array: pool, garage, gym), agent_id, listed_at, updated_at. Agent: agent_id, name, license_number, phone, email, agency_id, areas_served (city/zip list), rating, total_listings. Listing: listing_id, property_id, agent_id, listing_price, listing_type (SALE, RENT), status, open_house_dates. Inquiry: inquiry_id, property_id, user_id, agent_id, message, status (PENDING, RESPONDED, CLOSED), created_at. SavedSearch: search_id, user_id, filters (JSON), alert_enabled, last_notified_at.
Property Search and Filtering
Users filter by: location (city, zip, radius from a point), price range, bedrooms/bathrooms (min), property type, size range, features (pool, garage), listing status, and date listed. Relational database (PostgreSQL): can handle most filters with composite indexes. Performance problem: geospatial queries (properties within 10 miles of lat/lng) are slow without a spatial index. Solution: use PostGIS extension. Add a geometry column: ALTER TABLE properties ADD COLUMN location GEOMETRY(Point, 4326). Index: CREATE INDEX ON properties USING GIST(location). Query: SELECT * FROM properties WHERE ST_DWithin(location, ST_MakePoint(:lng, :lat)::geography, :radius_meters) AND price BETWEEN :min AND :max AND bedrooms >= :beds ORDER BY price ASC LIMIT 20.
For complex faceted search with full-text on description: index properties in Elasticsearch. Sync from PostgreSQL via Debezium CDC. Elasticsearch geo_point field enables: geo_distance queries (within radius), geo_bounding_box (map viewport). Filters in Elasticsearch: term filters for categorical fields (type, status), range filters for price/bedrooms. Full-text on description uses analyzed text. Return property_ids from Elasticsearch, fetch details from PostgreSQL. This pattern keeps the source of truth in Postgres while Elasticsearch handles search.
Map-Based Search
Users drag a map to browse listings. On each map pan/zoom: send the viewport bounding box (sw_lat, sw_lng, ne_lat, ne_lng) to the API. Query PostGIS: WHERE ST_Within(location, ST_MakeEnvelope(sw_lng, sw_lat, ne_lng, ne_lat, 4326)). Return clustered results for high-zoom-out views (too many points to render): use a clustering algorithm (geohash or H3 hex grids). At zoom level 10: return cluster centroids with counts. At zoom level 14: return individual property pins. Cache viewport queries in Redis with a short TTL (60 seconds) — adjacent users viewing the same area get the same result.
Agent Matching
When a user submits an inquiry, route to the best agent. Matching criteria: agent serves the property’s zip code/city (areas_served contains the property’s location), agent is active and has capacity (current_listings < 50), highest rating. Score = rating_weight * rating + recency_weight * (1 / days_since_last_listing). If no agent matches the zip: fall back to city-level match. If no city match: assign to agency's default agent. Store the routing decision: inquiry.agent_id. Notify the assigned agent via push/email. Track agent response time — slow-responding agents are deprioritized in future routing.
Saved Searches and Alerts
Users save search criteria to receive alerts when new listings match. Store saved searches with their filter JSON. When a new property is listed: a background job (runs every 15 minutes or triggered by the listing event) finds all saved searches whose filters match the new property. For each matching saved search with alert_enabled=true: check last_notified_at to rate-limit alerts (max one alert per search per day). Send a push notification or email with the new listing details. Matching saved searches to a new property: query saved_searches and evaluate filters in application code (feasible for < 1M saved searches). For scale: use a reverse index — for each city/zip, maintain a list of saved_search_ids that target that area. On new listing, only evaluate searches for that area.
Media and Virtual Tours
Property photos: upload via pre-signed S3 URLs (client uploads directly, never through the server). Store photo metadata: photo_id, property_id, s3_key, order_index, width, height. On upload: trigger a Lambda to generate thumbnails (800×600 for list view, 400×300 for map pins). Virtual tours: 360-degree photos stitched into a tour (Matterport-style). Store tour_id, property_id, and the tour manifest URL. Embed the viewer iframe in the listing detail page. Video walkthroughs: store as regular video, transcode to HLS for adaptive streaming.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you implement geospatial search for properties within a radius?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Use a spatial index — the standard approach is PostGIS in PostgreSQL. Store each property’s location as a geometry point: ST_SetSRID(ST_MakePoint(lng, lat), 4326). Create a GIST index on the location column. For radius search: SELECT * FROM properties WHERE ST_DWithin(location::geography, ST_MakePoint(:lng, :lat)::geography, :radius_meters) AND status = FOR_SALE ORDER BY ST_Distance(location::geography, ST_MakePoint(:lng, :lat)::geography) ASC LIMIT 20. The geography type handles Earth curvature for accurate distances. ST_DWithin with a spatial index is O(log n + k) where k is the result count. For even larger scale: use Elasticsearch with geo_distance queries. Elasticsearch distributes across nodes and handles hundreds of millions of documents.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle concurrent updates to property status (AVAILABLE u2192 SOLD)?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Property status changes (listing u2192 sale) can have race conditions: two buyers submit offers simultaneously. Optimistic locking: include a version number on the property record. On update: UPDATE properties SET status=UNDER_CONTRACT, version=version+1, buyer_id=X WHERE property_id=P AND status=FOR_SALE AND version=:current_version. If rows_affected == 0: another transaction modified the record — retry or return a conflict error. This prevents two buyers from both “winning” the property. For the final status change to SOLD: require an agent action with a signed purchase agreement document ID. Audit log: record every status change with timestamp, user_id, and reason — essential for disputes. Status transitions should form a state machine: FOR_SALE u2192 UNDER_CONTRACT u2192 SOLD (no backward transitions except with explicit agent override).”
}
},
{
“@type”: “Question”,
“name”: “How do you implement saved search alerts efficiently at scale?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Naive approach: when a new listing is added, scan all saved searches and evaluate each filter. With 10M saved searches and 10K new listings per day: 100B filter evaluations — too slow. Efficient approach: inverted index on key filter dimensions. For each (city, property_type, price_bucket) combination, maintain a list of saved_search_ids that match. On new listing: look up saved searches for the listing’s (city, type, price_bucket). Evaluate remaining filters (bedrooms, features) only for those matching searches. This reduces evaluations by 99%. Implementation: store the inverted index in Redis as sets: SADD searches:city:NYC:type:CONDO:price:500k-750k search_id. On new listing, SMEMBERS for the matching bucket, evaluate each returned search. Send alerts via a job queue (one job per alert to avoid blocking).”
}
},
{
“@type”: “Question”,
“name”: “How do you design property photo upload and display at scale?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Upload flow: client requests a pre-signed S3 URL from the API (valid for 15 minutes). Client uploads photo directly to S3 — never through the server. S3 triggers a Lambda on upload completion. Lambda: (1) validates the file (MIME type check, virus scan), (2) calls a resize function to generate thumbnails: 1200×800 for full-view, 400×267 for list cards, 200×133 for map thumbnails. Store thumbnails in S3 under predictable keys: photos/{property_id}/{photo_id}/{size}.webp. Serve via CloudFront CDN — edge-cached globally. Photo ordering: stored as order_index on the photo record. Drag-and-drop reordering updates order_index values in batch. Primary photo (first in order) is used as the listing card image. Lazy load non-primary photos on scroll.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle search ranking for property listings?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Simple: sort by price (ascending/descending), date listed (newest), or distance. Better: relevance ranking. Score each property: freshness_score (recently listed properties rank higher — decay function on days_since_listed), completeness_score (properties with more photos, virtual tours, and description length rank higher — better listings convert better), match_score (how well the property matches the search criteria — exact bedroom match scores higher than inexact), agent_response_rate (agents who respond to inquiries quickly get a boost). Combine: final_score = 0.4 * freshness + 0.3 * match + 0.2 * completeness + 0.1 * agent_quality. Store the pre-computed score on the property record, updated nightly. Use Elasticsearch function_score query to blend the relevance score with filter results.”
}
}
]
}
Asked at: Airbnb Interview Guide
Asked at: Stripe Interview Guide
Asked at: Shopify Interview Guide
Asked at: Atlassian Interview Guide