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.

MediumTechnical

26 practiced

Explain the difference between ROWS and RANGE window frames in SQL. Provide an example where RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW produces different results from ROWS BETWEEN 1 PRECEDING AND CURRENT ROW on irregular timestamped events, and explain implications for computing time-based rolling features.

MediumTechnical

25 practiced

Compute a rolling 90th percentile (P90) of request latency per minute per service using SQL at scale. Provide a BigQuery example using APPROX_QUANTILES and a Postgres approach (percentile_disc/percentile_cont) and discuss accuracy, performance, and when to prefer approximate methods like t-digest or stored sketches.

EasyTechnical

34 practiced

Given a purchases table (purchase_id bigint PRIMARY KEY, user_id bigint, item_id bigint, price numeric, purchased_at timestamp), write a PostgreSQL SQL query to produce per-user features: total_purchases, total_spent, avg_price, last_purchase_date, and a recency_bucket ('0-30d', '31-90d', '>90d') based on a reference date parameter. Show how you handle users with no purchases and explain indexing choices to speed this aggregation.

MediumTechnical

36 practiced

Rewrite this correlated subquery into a join/aggregate in PostgreSQL for better performance:

SELECT u.user_id FROM users u WHERE (SELECT COUNT(*) FROM purchases p WHERE p.user_id = u.user_id AND p.ts > now() - interval '30 days') > 5;

Provide a performant rewrite using CTE or join and explain why the rewritten query can be faster with proper indexing.

MediumTechnical

43 practiced

Explain how result/query caching in Snowflake or BigQuery works and how it can affect repeated feature queries during development. When will a query hit the cache, what are pitfalls (stale results, exact-text matching), and ways to leverage cache safely in training pipelines?

Unlock Full Question Bank

Get access to hundreds of SQL Scenarios interview questions and detailed answers.

Join thousands of developers preparing for their dream job.