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.
MediumTechnical
61 practiced
You need to compute unique active users across overlapping cohorts defined by different marketing channels. Explain whether to implement this using set operations (UNION/INTERSECT/EXCEPT), GROUP BY with conditional logic, or approximate distinct sketches. Provide a recommended approach and justify it based on accuracy, cost, and flexibility for downstream analysis.
EasyTechnical
66 practiced
Describe INTERSECT and EXCEPT (or MINUS) set operations in SQL and provide a realistic BI example using orders_2023(order_id, customer_id) and orders_2024(order_id, customer_id). Explain when to use INTERSECT and when to use EXCEPT to identify returning customers and lost customers, and discuss how NULLs and duplicate rows affect results.
MediumTechnical
69 practiced
Describe the tradeoffs between using UNION to merge multiple partitioned files or tables versus querying a single partitioned table with a partition key. A BI dashboard needs daily and monthly aggregates over two years of partitioned data. Which approach is preferable and why?
EasyTechnical
67 practiced
Explain why COUNT(DISTINCT user_id) can be expensive on high-cardinality columns and describe two practical strategies a BI analyst can use to reduce cost without significantly losing accuracy. Mention approximate aggregation functions and data modeling choices used in warehouses like BigQuery or Redshift.
HardTechnical
68 practiced
An ETL job experienced partial failure and re-ingested a slice of data, leading to duplicate rows in your fact table. Analysts use UNION-based queries to merge multiple sources; deduping at query time is now expensive. Design an idempotent ETL pattern and explain how primary keys, upserts, tombstones, or change-data-capture can prevent such problems for BI consumers.
Unlock Full Question Bank
Get access to hundreds of Set Operations and Complex Aggregations interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.