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

0 practiced

Given events(id, user_id, event_ts timestamp, payload text, source text) that contain duplicates and late arrivals, write a safe Postgres sequence of statements to deduplicate in place keeping the earliest id per (user_id, source, date_trunc('minute', event_ts)). The job must be resumable, run in batches, and avoid long locks that impact reporting.

EasyTechnical

0 practiced

Given two tables in a transactional database:

orders(order_id int, user_id int, order_amount numeric, order_date date)users(user_id int, country text)

Write a single SQL query (standard SQL / PostgreSQL) to compute total revenue and number of distinct buyers per country for the last 30 days, including countries with zero revenue. Show the expected output columns: country, total_revenue, unique_buyers. Explain any assumptions about time zones and how you handle NULL user_id values.

EasyTechnical

0 practiced

Build a SQL query (standard SQL / Postgres) that computes daily unique active users from events(user_id int, event_date date) and then a 7-day moving average of the daily unique count. Output columns: event_date, daily_unique_users, moving_avg_7d. Note that COUNT(DISTINCT) and window functions interact differently—show an approach that works correctly.

MediumTechnical

0 practiced

Write a SQL query to compute per-user running total of order amounts ordered by order_date: output fields should be user_id, order_id, order_date, order_amount, running_total. Then explain how you would scale this for millions of users and hundreds of millions of orders: mention partitioning, incremental precomputation, and other practical optimizations.

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.

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.