Set Operations and Complex Aggregations Questions

Understanding UNION, UNION ALL, EXCEPT, INTERSECT operations and their performance implications. Complex GROUP BY queries, HAVING clauses, and multi-level aggregations.

HardTechnical

72 practiced

Technical task (SQL): Given multiple event sources events(source TEXT, event_id TEXT, user_id INT, event_ts TIMESTAMP) where the same event_id can appear in multiple sources, write a SQL query that dedupes events across sources (keeping the earliest event_ts when duplicates exist) and then produces sessionization per user with a 30-minute inactivity window. Outline edge cases (ties, missing event_ts) and how you handle them.

EasyTechnical

62 practiced

Describe the semantics and typical use cases of INTERSECT and EXCEPT (MINUS in some engines). Explain how these operations are commonly used in data engineering workflows such as change detection, incremental loads, and QA checks. Note any engine-specific availability or behavior differences you should watch for.

MediumTechnical

58 practiced

Technical task (SQL): You have monthly partitioned tables orders_2024_01(order_id, customer_id, updated_at), orders_2024_02(...), orders_2024_03(...). Design a SQL query that returns the latest row for each order_id across these monthly partitions. Assume partitions cannot be merged and you should avoid full-table sorts when possible. Show an approach using UNION ALL and window functions or partition pruning.

EasyTechnical

79 practiced

Technical task (SQL): Given a table events(user_id INT, occurred_at TIMESTAMP), write a query to compute Daily Active Users (DAU) for the last 30 days. Include timezone handling assuming occurred_at is UTC and the business operates in America/Los_Angeles. Return columns date (in America/Los_Angeles) and dau_count. Use ANSI SQL or state which dialect you target.

MediumTechnical

73 practiced

Theoretical question: Discuss how SQL set operations (UNION/INTERSECT/EXCEPT) treat NULL values according to the SQL standard and name any engine-specific differences you know. Provide examples that demonstrate surprising NULL-handling behavior and how you would defensively code around those differences.

Unlock Full Question Bank

Get access to hundreds of Set Operations and Complex Aggregations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.