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

0 practiced

How does EXCEPT handle NULL values in SQL? Given two small sample tables A and B with rows that include NULLs, show the expected output of A EXCEPT B. Explain the reasoning and how this might surprise BI analysts when cleaning data using set operations.

MediumTechnical

0 practiced

You have a large sales table and an analyst asks to filter out product categories with total revenue under 10000 across the entire year. Should you use HAVING or a subquery with GROUP BY and join the results back to the sales table? Discuss performance and readability tradeoffs for dashboard queries that rely on these filters.

MediumTechnical

0 practiced

Write SQL that computes running totals of revenue per day using window functions on sales(sale_date, revenue). Then show how you could UNION the running totals with a real-time partial window to produce a combined view for a dashboard that displays historical running totals plus the current in-progress day.

HardTechnical

0 practiced

You are choosing where to perform aggregations: at query time in the warehouse, or pre-aggregated into star-schema aggregate tables for a BI product with many dashboards. Given a user base of 100M users and high cardinality dimensions, list criteria you would use to decide and propose an approach that minimizes query latency while controlling storage and refresh cost.

HardTechnical

0 practiced

Given this query that unions two detailed datasets then groups by user and day to compute daily revenue, rewrite it to reduce scans and improve performance. Original pattern:

SELECT user_id, day, SUM(amount) FROM (
  SELECT user_id, date_trunc('day', ts) as day, amount FROM payments
  UNION ALL
  SELECT user_id, date_trunc('day', ts) as day, amount FROM refunds
) t
GROUP BY user_id, day;

Provide a more efficient equivalent and explain why it is better.

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.