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
0 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.
MediumSystem Design
0 practiced
You want to use materialized views to precompute hourly features for millions of users. Design a strategy for materialized views considering refresh modes (CONCURRENTLY vs full), storage, and locking in PostgreSQL, and alternatives (incremental refresh using CDC or ETL). Explain how you'd handle staleness, invalidation, and rollbacks for failed refreshes.
MediumTechnical
0 practiced
A query includes a join to a staging_users table but only selects columns from users and filters on staging_users.is_active. Explain when the optimizer can eliminate the join (join elimination) and provide rewritten examples where join elimination occurs and where it doesn't. Discuss how foreign key/unique constraints influence join elimination.
MediumTechnical
0 practiced
Given a table events(user_id bigint, event_ts timestamp, metric_value double precision) in PostgreSQL, write a SQL query that outputs daily rows per user with columns: user_id, dt (date), daily_sum, rolling_7d_avg (7-day trailing average of daily_sum including current day). Ensure days with no events appear with daily_sum = 0 (i.e., fill gaps). Use window functions and explain how you handle time zones and gaps in dates.
MediumTechnical
0 practiced
Given a category hierarchy table categories(category_id int, parent_id int), write a recursive CTE in PostgreSQL that produces for each node its full path string and depth, and include cycle detection to avoid infinite loops. Explain performance concerns and alternatives for very deep or very wide hierarchies.
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.