SQL Scenarios Questions
Advanced SQL query design and optimization scenarios, including complex joins, subqueries, window functions, common table expressions (CTEs), set operations, indexing strategies, explain plans, and performance considerations across relational databases.
HardTechnical
36 practiced
You store last_processed_event_id in a small state table. Write an atomic PostgreSQL SQL (single transaction using CTEs) that: 1) selects events with event_id > last_processed_event_id, 2) aggregates counts per user_id, 3) upserts the aggregated counts into a user_features table (incrementing an events_count field), and 4) updates last_processed_event_id to the max processed id. Explain concurrency considerations and idempotency.
HardSystem Design
26 practiced
Design a refresh strategy for hourly materialized views that compute features for 50M users. Compare full refresh, partitioned refresh (per hour/day), and incremental refresh using change logs. Describe an implementation approach to perform incremental refresh (e.g., delta tables/CDC or rolling partitions) and trade-offs on complexity vs latency.
HardSystem Design
27 practiced
Design schema and retrieval patterns for a low-latency online feature store that must serve 5k QPS with sub-10ms latency per request. Evaluate trade-offs between using a relational DB (Postgres read replicas), a key-value store (Redis/Memcached), and a columnar analytical DB. Provide SQL/schema examples for batch feature storage and describe typical online retrieval queries or key lookups.
MediumTechnical
25 practiced
Explain partition pruning behavior: why queries that use functions on the partition key (e.g., WHERE date_trunc('day', occurred_at) = '2025-01-01') might prevent pruning in Postgres/Redshift/BigQuery. Show how to rewrite such queries to enable partition elimination (e.g., occurred_at >= '2025-01-01' AND occurred_at < '2025-01-02').
MediumTechnical
29 practiced
Discuss pros and cons of using non-materialized CTEs (WITH ... AS ...) versus temporary tables for multi-step feature pipelines in PostgreSQL. Explain when a CTE forces materialization (acts as an optimization fence) and how that can hurt performance; show how to avoid unnecessary materialization.
Unlock Full Question Bank
Get access to hundreds of SQL Scenarios interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.