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.
EasyTechnical
0 practiced
Given tables products(product_id int, category text) and sales(product_id int, sale_date date, amount numeric), write SQL that returns the top 3 products by total revenue in each category for the last quarter. Use window functions to rank items per category and ensure ties are handled deterministically (e.g., by product_id).
HardTechnical
0 practiced
Nightly ETL acquires exclusive locks on a reporting table and dashboard queries block and fail. As a BI analyst/data engineer describe how you would diagnose the locking problem, including the SQL/views you would query (e.g., pg_locks, pg_stat_activity in Postgres or sys.dm_tran_locks in SQL Server). Provide multiple mitigation strategies: staging tables and partition swap, smaller transactions, COPY/bulk load, isolation level adjustments, and read replicas.
HardTechnical
0 practiced
Explain how database statistics and histograms influence the query planner. Describe symptoms of stale statistics and how they can lead to bad execution plans. Provide commands to update statistics in PostgreSQL and SQL Server and describe monitoring queries or checks you would run to detect planner misestimates in production.
EasyTechnical
0 practiced
Given a transactions table:transactions(transaction_id int, user_id int, transaction_ts timestamp, amount numeric, source_id int)Write a SQL query using window functions (Postgres) to identify duplicate business-key occurrences where the business key is (user_id, source_id, date_trunc('minute', transaction_ts)). For each duplicate group keep the earliest transaction_id and output the list of transaction_ids to delete. Explain how to delete duplicates safely in batches.
EasyTechnical
0 practiced
In BI reports, missing dimension values should be shown as 'Unknown' rather than NULL. Given fact table fact_sales(fact_id, product_id, amount) and dim_product(product_id, product_name), write SQL that joins the two and returns product_name with 'Unknown' when missing, ensuring aggregate calculations include the Unknown bucket. Discuss any performance considerations.
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.