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
Design SQL to maintain an hourly aggregated table agg_hourly(date_hour timestamp, product_id int, total_sales numeric) from a streaming events table events(product_id int, event_ts timestamp, amount numeric). Provide an idempotent MERGE or UPSERT statement (Postgres or SQL Server) that merges new hourly aggregates into agg_hourly and supports late-arriving events by reprocessing affected hours.
HardSystem Design
0 practiced
Customer master is in Postgres and order history lives in Redshift. A report needs to join both systems. Explain three approaches: ETL to copy Postgres into Redshift, federated queries that join across systems at query time, and joining in the application layer. For each approach discuss performance, consistency, operational complexity, and recommended use cases for BI reporting.
HardTechnical
0 practiced
Write a single SQL query (Postgres / standard SQL) to find the top 3 products with the largest single-day consecutive percentage growth observed across any 7-day window in the past year. Input table: sales(product_id int, sale_date date, amount numeric). The query should compute day-over-day percent changes within moving 7-day windows, find the maximum consecutive-day jump per product, and return the top 3 products by that metric. Explain your approach and edge-case handling (zero sales days).
EasyTechnical
0 practiced
Using a sales table (sale_date date, region text, product_category text, amount numeric), write a SQL query that returns totals grouped by region, totals grouped by product_category, and the overall total in one result set using GROUPING SETS or ROLLUP. Show how to distinguish subtotal rows in the output.
HardSystem Design
0 practiced
Design an architecture to support consistent, near-real-time BI reports while minimizing load on the OLTP system. Include replication, change data capture (CDC), read replicas, ELT vs ETL, and query routing. Discuss latency, consistency models, cost considerations, and failure modes.
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.