Geospatial and Time-Series Data Questions

Specialized data shapes and their stores: geospatial data types, spatial indexing, and location queries; and time-series data with high-ingest, retention, downsampling, and cardinality management. Covers storage-engine tuning such as compression, chunking, and indexing for range queries, and when purpose-built extensions or databases beat general-purpose stores for these workloads. A niche but recurring topic for location- and telemetry-heavy systems.

MediumTechnical

76 practiced

In Python (you may reference scikit-learn or scipy), implement or describe a function that returns the k nearest drivers to a rider location when n can be up to 100k drivers. Explain coordinate choices (use BallTree with haversine vs KDTree on projected coordinates), memory/time complexity, and how you would support dynamic updates when driver positions change.

Sample Answer

r_k \approx \sqrt{\frac{k}{\pi \rho}}

HardSystem Design

76 practiced

A rideshare analytics warehouse needs to answer ad-hoc questions like 'find trips that started within 500 meters of these coordinates, during this one-hour window' at petabyte scale, with sub-minute response for analysts running exploratory queries. Design the schema and query strategy: how would you make a query like this fast without a full scan, and what would you trade off between query latency, storage overhead, and the extra work you do at write time to keep the index maintained?

Sample Answer

\begin{aligned} \text{hours in 3 years} &= 3 \times 365 \times 24 = 26{,}280 \\ \text{naive: one hour of } \texttt{trips} &\approx 10^{15} / 26{,}280 \approx 38 \text{ GB (still a lot, and only if trips is time-partitioned)} \\ \text{trip\_starts per day} &= 20{,}000{,}000 \times 60 \text{ B} = 1.2 \text{ GB} \\ \text{trip\_starts per hour} &\approx 1.2 \text{ GB} / 24 = 50 \text{ MB} \end{aligned}

MediumTechnical

100 practiced

Write an efficient PostGIS SQL query to find up to 5 nearest available listings within 5 km for each search coordinate in 'searches(lat, lon, search_time)'. Return listing_id, distance_km, price_usd, and compute the percentage of searches per city with at least 3 results. Assume 'listings(listing_id, lat, lon, is_active, price_usd)'. Use spatial indexes and lateral joins to keep the query performant.

Sample Answer

EasyTechnical

105 practiced

Explain what downsampling and rollups are in TSDBs. Provide an example policy for converting 1-second resolution to 1-minute and 1-hour rollups and describe the query implications for dashboards and alerts.

Sample Answer

\begin{aligned} \text{raw, 7 days} &= 86{,}400 \times 7 = 604{,}800 \\ \text{minute, 90 days} &= 1{,}440 \times 90 \times 4 = 518{,}400 \\ \text{hour, 2 years} &= 24 \times 730 \times 4 = 70{,}080 \\ \text{total} &= 1{,}193{,}280 \end{aligned}

HardSystem Design

123 practiced

Your table stores time-series events and needs to answer 'rolling metrics for this user over the last 7/30/90 days' at query time, without maintaining separate precomputed rollup tables. Design the schema, partitioning, clustering, and secondary indexes that would keep those queries fast as the table grows, and explain the query pattern you'd use to compute the rolling windows efficiently.

Sample Answer

Direct answer Partition the events table by time (monthly), and give every partition a composite index on (user_id, event_ts) that includes the measured value. Then a 90-day lookup for one user is one contiguous index range per partition, answered without reading the table. Compute all three windows in one pass over the last 90 days with conditional aggregation (FILTER (WHERE event_ts >= as_of - interval '7 days')), because the 7- and 30-day windows are subsets of the 90-day one. For a rolling time series (a 7-day value for every day), bucket events into days first, then use a window function with a RANGE frame on the day, so the work grows with the number of days, not the number of events. Schema, partitioning and indexes sqlCREATE TABLE events ( user_id bigint NOT NULL, event_ts timestamptz NOT NULL, amount numeric(12,2) NOT NULL ) PARTITION BY RANGE (event_ts); CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01'); -- one partition per month CREATE INDEX ON events (user_id, event_ts) INCLUDE (amount); Partitioning by month: a 90-day query touches at most 4 partitions however long history grows, and retention drops old months. Clustering by user: the composite index gives one user's events in time order inside each partition. In a columnar warehouse the same idea is PARTITION BY DATE(event_ts) plus clustering or sort key on user_id. INCLUDE (amount) makes the scan index-only: Postgres answers from the index without fetching table rows, as long as autovacuum (Postgres's background process that runs VACUUM to reclaim space and keep this bookkeeping current) keeps the table's visibility map (a per-table bitmap marking which pages hold only rows every transaction can see) current. No other secondary indexes on this path. Add one only for a different access pattern, such as (event_type, event_ts) for per-type analytics. The query patterns, runnable This script builds five monthly partitions, gives user 42 exactly one 10.00 event per day from 1 May to 29 September 2026, adds noise rows for 1,999 other users, and computes the windows as of 30 September 00:00 UTC. The as-of time is a literal so the output is reproducible; in production it is now() or a parameter. Run it with psql -f on PostgreSQL 16: sqlSET TIME ZONE 'UTC'; DROP TABLE IF EXISTS events CASCADE; CREATE TABLE events ( user_id bigint NOT NULL, event_ts timestamptz NOT NULL, amount numeric(12,2) NOT NULL ) PARTITION BY RANGE (event_ts); CREATE TABLE events_2026_05 PARTITION OF events FOR VALUES FROM ('2026-05-01') TO ('2026-06-01'); CREATE TABLE events_2026_06 PARTITION OF events FOR VALUES FROM ('2026-06-01') TO ('2026-07-01'); CREATE TABLE events_2026_07 PARTITION OF events FOR VALUES FROM ('2026-07-01') TO ('2026-08-01'); CREATE TABLE events_2026_08 PARTITION OF events FOR VALUES FROM ('2026-08-01') TO ('2026-09-01'); CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01'); CREATE INDEX ON events (user_id, event_ts) INCLUDE (amount); -- user 42: one 10.00 purchase per day from 2026-05-01 to 2026-09-29; other users as noise INSERT INTO events SELECT 42, timestamptz '2026-05-01 12:00' + d * interval '1 day', 10.00 FROM generate_series(0, 151) AS d; INSERT INTO events SELECT u, timestamptz '2026-05-01 00:00' + (u * 37 + d) % 150 * interval '1 day', 1.00 FROM generate_series(1, 2000) AS u, generate_series(0, 19) AS d WHERE u <> 42; ANALYZE events; \echo one pass, three windows, as of 2026-09-30 00:00 UTC SELECT sum(amount) FILTER (WHERE event_ts >= timestamptz '2026-09-30' - interval '7 days') AS sum_7d, sum(amount) FILTER (WHERE event_ts >= timestamptz '2026-09-30' - interval '30 days') AS sum_30d, sum(amount) AS sum_90d, count(*) AS events_90d FROM events WHERE user_id = 42 AND event_ts >= timestamptz '2026-09-30' - interval '90 days' AND event_ts < timestamptz '2026-09-30'; \echo rolling 7-day sum per day, last 3 days SELECT day, rolling_7d FROM ( SELECT day, sum(daily) OVER (ORDER BY day RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW) AS rolling_7d FROM ( SELECT date_trunc('day', event_ts) AS day, sum(amount) AS daily FROM events WHERE user_id = 42 AND event_ts >= timestamptz '2026-09-30' - interval '96 days' AND event_ts < timestamptz '2026-09-30' GROUP BY 1 ) d ) w ORDER BY day DESC LIMIT 3; \echo plan EXPLAIN (COSTS OFF) SELECT sum(amount) FROM events WHERE user_id = 42 AND event_ts >= timestamptz '2026-09-30' - interval '90 days' AND event_ts < timestamptz '2026-09-30'; Output: textone pass, three windows, as of 2026-09-30 00:00 UTC sum_7d | sum_30d | sum_90d | events_90d --------+---------+---------+------------ 70.00 | 300.00 | 900.00 | 90 (1 row) rolling 7-day sum per day, last 3 days day | rolling_7d ------------------------+------------ 2026-09-29 00:00:00+00 | 70.00 2026-09-28 00:00:00+00 | 70.00 2026-09-27 00:00:00+00 | 70.00 (3 rows) plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate -> Append Subplans Removed: 2 -> Index Only Scan using events_2026_07_user_id_event_ts_amount_idx on events_2026_07 events_1 Index Cond: ((user_id = 42) AND (event_ts >= ('2026-09-30 00:00:00+00'::timestamp with time zone - '90 days'::interval)) AND (event_ts < '2026-09-30 00:00:00+00'::timestamp with time zone)) -> Index Only Scan using events_2026_08_user_id_event_ts_amount_idx on events_2026_08 events_2 Index Cond: ((user_id = 42) AND (event_ts >= ('2026-09-30 00:00:00+00'::timestamp with time zone - '90 days'::interval)) AND (event_ts < '2026-09-30 00:00:00+00'::timestamp with time zone)) -> Index Only Scan using events_2026_09_user_id_event_ts_amount_idx on events_2026_09 events_3 Index Cond: ((user_id = 42) AND (event_ts >= ('2026-09-30 00:00:00+00'::timestamp with time zone - '90 days'::interval)) AND (event_ts < '2026-09-30 00:00:00+00'::timestamp with time zone)) (9 rows) Check the numbers by hand: 90 days before 30 September is 2 July, so the window holds 2 July to 29 September, which is 90 daily events of 10.00 = 900.00. The last 30 days hold 30 events (300.00) and the last 7 hold 7 (70.00). The plan shows the May and June partitions removed ("Subplans Removed: 2") and an index-only scan on the other three. Why these patterns One pass, three windows: three separate queries would read the last 7 days three times and the last 30 twice. FILTER evaluates all three sums while scanning the 90-day range once. Bucket, then window: RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW means "rows whose day is within 6 days before this one", which is correct even when a user has no events on some days. A ROWS BETWEEN 6 PRECEDING frame would silently span more than 7 calendar days whenever days are missing. Scanning 96 days for a 90-day series: the first output day needs 6 days of history before it, otherwise its 7-day value is truncated. Keeping it fast as the table grows Cost per query is proportional to one user's events in the window, not to table size, because the index range and partition pruning bound it. Worked example: a heavy user with 200 events/day has 18,000 events in 90 days; that is about 18,000 index entries across 4 partitions, which is a small read. The worst case is a bot account with millions of events: add a per-user daily cap at ingest, or accept that such users need a pre-aggregated path. Two things silently break it: Stable functions and pruning. now() is a stable function (a Postgres function guaranteed to return the same value for every row within one query, but not a fixed literal Postgres already knows ahead of time), so now() - interval '90 days' is not a constant at planning time (the step where Postgres works out, before running the query, which partitions could possibly match). Instead it prunes partitions (skips the ones that cannot match) at executor start (the moment it actually begins running the query, once "now" is known); that is still effective, as the plan shows, it just happens a beat later than pruning on a literal timestamp would. Wrapping the column, as in WHERE date(event_ts) >= ..., defeats both pruning and the index; always compare the raw column to a computed bound. Index-only scans need a current visibility map. On a table with heavy updates or deletes, the scan falls back to fetching rows from the table itself (shown in EXPLAIN output as heap fetches) instead of answering from the index alone. Events are insert-only, so this holds if autovacuum keeps up. Trade-offs and pitfalls Time zones: date_trunc('day', event_ts) uses the session time zone. Set it explicitly (the script uses UTC), or compute days in the user's own zone with AT TIME ZONE. Window boundaries: decide whether "last 7 days" means the last 168 hours (as here) or the last 7 calendar days including today; they differ by up to a day of events. Batch many users: for a feature pipeline scoring millions of users, the same FILTER query with GROUP BY user_id over one 90-day scan is better than millions of index lookups. What would change the design: if the SLO (service-level objective) is single-digit milliseconds at thousands of requests per second, the "no rollups" constraint is worth challenging, because incrementally maintained daily per-user totals (a background job adds each day's events to a running per-user-per-day row as they arrive, instead of a request recomputing sums from scratch) turn each request into reading at most 90 small rows.

Unlock Full Question Bank

Get access to all 12 Geospatial and Time-Series Data interview questions and detailed answers.

Join thousands of developers preparing for their dream job.

Operation	Time	Memory
Build BallTree / KDTree	O(n log n)	O(n): 100,000 × 2 float64 = 1.6 MB of coordinates plus 0.8 MB of int64 index array and node bounds
kNN query	about O(log n + k) for spread-out low-dimensional data; O(n) worst case when points are pathologically clustered (nearly all crammed into one tiny region, so the tree's branching gives it no leverage)	O(k)
Brute-force check	O(n) per query	O(n) temporary distances
Position update (snapshot design)	O(1)	none extra
Query with over-fetch m and re-rank	O(log n + k + m), plus O((k + m) log(k + m)) to sort	O(k + m)

Table	Contents	Role
`trips` (the petabyte)	Full trip records including GPS traces, fares, events	Source of truth; queried only by `trip_id` joins or broad batch jobs
`trip_starts`	`trip_id, start_ts, start_lat, start_lon, start_geohash6, city_id`	The ad-hoc search surface

Lever	Query latency	Storage overhead	Write-time work
Nothing (full scan of `trips`)	Minutes to hours; scans up to a petabyte per query	None	None
Time partitioning only	Reads 38 GB per hour queried	None	Trivial
`trip_starts` + hour partition + geohash sort (committed)	Seconds; reads megabytes	About 1.2 GB per day, a rounding error beside the traces	Compute a geohash per row (cheap), plus re-sorting recent partitions as late data lands
Engine-managed search index on geography (for example Snowflake search optimization, a paid Snowflake feature that builds and maintains a search index automatically)	Fast point lookups	Index storage billed separately	Background maintenance billed as compute
Pre-aggregated cell-by-hour counts	Milliseconds, but only for counting questions, never "which trips"	Small	A stream job

Decision	Choice	Reason
Point representation	Stored generated column (a column PostgreSQL computes automatically from other columns via a formula and writes to disk once, at insert or update time, instead of recomputing it on every read) `geog geography(Point,4326)`	Built once at write time. The alternative is an expression index (an index built on the result of a computation over columns rather than on a stored column) on `(ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography)`, which works if the table cannot change, but every query then has to repeat that exact expression or the index is ignored.
Index	`USING gist (geog) WHERE is_active`	A partial index holds only the rows the query can return, so it is smaller and every probe skips inactive listings for free. The query must repeat `l.is_active` for the planner (PostgreSQL's query optimizer: the component that decides which indexes and scan strategies to use for a given query) to use it.
Radius filter	`ST_DWithin(geog, pt, 5000)`	Index-aware: it expands the point by 5 km, uses the box for the index, then checks exact distance. `ST_Distance(...) < 5000` in a `WHERE` clause cannot use the index.
Ordering	`ORDER BY geog <-> pt LIMIT 5`	Stops after 5 rows per search.
Zero-result searches	`LEFT JOIN LATERAL ... ON true`	A plain lateral join would drop searches that found nothing, which would inflate the city percentage.
City	`LEFT JOIN cities ON ST_Covers(c.geom, pt)`	`ST_Covers` includes points exactly on the boundary (`ST_Contains` does not). Searches outside every polygon land in `'unassigned'` instead of vanishing.

Tier	Resolution	Kept for	Stored per bucket	Built from
Raw	1 s	7 days	the value	ingestion
Minute rollup	1 min	90 days	min, max, sum, count, last	raw
Hour rollup	1 h	2 years	min, max, sum, count, last	minute rollup

Source	Points read
1 s raw	30 x 86,400 = 2,592,000
1 min rollup	30 x 1,440 = 43,200
1 h rollup	30 x 24 = 720

Geospatial and Time-Series Data Questions

Sample Answer

Direct answer

Terms used

Approach

Code: static kNN and the coordinate choices

Supporting dynamic updates

Why 20 extra candidates is enough here (and when it is not)

Complexity

Edge cases

Trade-offs and pitfalls

Sample Answer

Direct answer

How warehouses make "without a full scan" possible

The design

Worked example: bytes scanned

Trade-offs: latency vs storage vs write-time work

Pitfalls

Sample Answer

Direct answer

Why each piece is there

Runnable solution (PostgreSQL 16, PostGIS 3.4)

Reading the result

Complexity

Edge cases and pitfalls

Sample Answer

Direct answer

Key terms, in plain language

The example policy

Worked example: what the policy buys per series

Query implications for dashboards

Query implications for alerts

Trade-offs and pitfalls

Sample Answer

Direct answer

Schema, partitioning and indexes

The query patterns, runnable

Why these patterns

Keeping it fast as the table grows

Trade-offs and pitfalls

Unlock Full Question Bank