Apples & Oranges - Solution & Explanation

Problem Overview: The table records how many apples and oranges were sold on each date. The task is to return the daily difference between apples and oranges sold (apples - oranges) for every sale_date.

Approach 1: Conditional Aggregation with GROUP BY (O(n) time, O(1) space)

The cleanest solution uses SQL conditional aggregation. Scan the table once, group rows by sale_date, and compute totals for each fruit using SUM(CASE WHEN ...). One conditional sum collects the number of apples sold and another collects oranges. Subtract the two aggregated values to produce the required difference. Because the table is processed once and aggregation happens during grouping, the time complexity is O(n) with constant extra space aside from the grouped result set.

This approach works well because each date already contains rows for both fruit types. Instead of joining rows or running multiple queries, conditional aggregation computes both totals in a single pass. The pattern is extremely common in database interview problems where multiple categories must be aggregated from the same dataset.

Approach 2: Self Join on Date (O(n log n) time, O(n) space)

Another option separates apples and oranges into two logical sets and joins them on sale_date. You can filter one subquery for apples and another for oranges, then join them and subtract the quantities. While conceptually simple, it introduces extra work for the database optimizer and may require sorting or indexing during the join. The result is typically O(n log n) time depending on the execution plan.

This method can still be useful if the dataset requires filtering or transformations before comparison. However, for straightforward aggregation problems, conditional sums are usually faster and easier to read.

Problems like this frequently appear in SQL interview rounds because they test your understanding of SQL aggregation and GROUP BY logic. You need to recognize when multiple rows should be summarized into a single metric per group.

Recommended for interviews: The GROUP BY + conditional SUM solution is the expected answer. It scans the table once, avoids unnecessary joins, and demonstrates strong knowledge of SQL aggregation patterns. Mentioning a join-based alternative shows breadth, but the single-pass aggregation solution signals better query design.