Apples vs Oranges

The ENUM table definition is MySQL-specific. The conditional aggregation query itself also handles a missing fruit row because the unmatched branch contributes zero.

Problem Statement

A fruit sales business sells both apples and oranges, and wants to track the daily difference between how many apples and how many oranges were sold.

Business context Knowing this daily gap helps with inventory decisions (should more apples be stocked than oranges?), pricing strategy (is one fruit consistently outselling the other?), and planning promotions (maybe oranges need a discount push on days they lag behind).

Goal For each day, calculate: (total apples sold that day) minus (total oranges sold that day).

Input A table listing daily sales, one row per fruit per day.

Output Each date along with the calculated difference, sorted chronologically.

Table Structure

Table: Sales

Column Meaning

• sale_date — the date on which the sale happened
• fruit — which fruit was sold (an enum type, meaning it’s restricted to a fixed set of values, like ‘apples’ or ‘oranges’)
• sold_num — how many units of that fruit were sold on that date

Important Notes

• The primary key is the combination of (sale_date, fruit), meaning there is exactly one row per fruit per day, never duplicated. So for any given date, there’s one row for apples and one row for oranges, not multiple scattered rows for the same fruit.
• This means you don’t need to worry about summing across multiple rows for the same fruit on the same day, the raw numbers are already one-per-day-per-fruit. However, using SUM() is still the safest and most flexible approach, since it works correctly whether there’s one row or several for a given fruit and date.
• The output needs to be sorted by sale_date, so ORDER BY is required at the end, similar to Problem 3.
• Since fruit is an enum, only a fixed, known set of values can appear in that column (in this case, presumably just ‘apples’ and ‘oranges’), which makes it safe to check for exact string matches like fruit = 'apples'.

Example

Input: Sales table

Expected Output

CREATE TABLE Sales (sale_date TEXT, fruit TEXT, sold_num INTEGER);
INSERT INTO Sales VALUES
  ('2020-05-01', 'apples', 10),
  ('2020-05-01', 'oranges', 8),
  ('2020-05-02', 'apples', 15),
  ('2020-05-02', 'oranges', 15),
  ('2020-05-03', 'apples', 20),
  ('2020-05-03', 'oranges', 0),
  ('2020-05-04', 'apples', 15),
  ('2020-05-04', 'oranges', 16);

Explanation

• 2020-05-01: 10 apples − 8 oranges = 2
• 2020-05-02: 15 apples − 15 oranges = 0
• 2020-05-03: 20 apples − 0 oranges = 20
• 2020-05-04: 15 apples − 16 oranges = −1 (oranges outsold apples that day, so the difference is negative)

Intuition

Picture a small fruit stand keeping two separate tally counters each day, one for apples sold, one for oranges sold. At the end of each day, the shopkeeper simply subtracts one tally from the other to see which fruit did better.

This problem is really two smaller problems stacked together:

1. First, for each day, add up all apples sold and all oranges sold separately (even though in this specific table each fruit only has one row per day, the general technique should still work as if there were several rows to add up).
2. Then, subtract the orange total from the apple total, for that same day.

This is a textbook case of conditional aggregation, the same pattern used earlier in the CASE Expression concept notes with the “apples vs oranges sold” example, except now it’s applied across multiple dates instead of a flat total.

Mental Model

Imagine sorting a big pile of daily receipts into small stacks, one stack per date. Within each stack, you go through every receipt and ask two questions: “was this an apple sale? If so, add its number to my apple total for today” and “was this an orange sale? If so, add its number to my orange total for today.”

Once you’ve gone through every receipt in that day’s stack, you subtract the orange total from the apple total, and that becomes the day’s final number.

Then you move on to the next date’s stack and repeat the whole process.

This is exactly what GROUP BY does for you automatically, it splits the table into date-based “stacks,” and then applies your SUM(CASE…) logic separately within each stack.

Thought Process

Before writing SQL, break the problem into pieces:

1. What am I grouping by? Since I need one result row per date, and the calculation needs to happen separately for each date, I need GROUP BY sale_date.
2. Within each date, how do I get the apple total? I need to add up sold_num, but only for rows where fruit = 'apples'. This is a filtered sum, which is where CASE comes in.
3. How do I get the orange total the same way? Same idea, but checking fruit = 'oranges' instead.
4. How do I combine both totals into one number? Subtract the orange total from the apple total.
5. Does this need to happen inside one SELECT, or can I do two separate SUMs and subtract them? Both approaches work. Writing two separate SUM(CASE WHEN ...) expressions and subtracting them directly in the SELECT clause is the cleanest way, since SQL evaluates each SUM independently before doing the subtraction.
6. What about the final order? The problem explicitly asks for results sorted by sale_date, so ORDER BY is required at the end.

Putting this together: this is a GROUP BY problem where CASE is used inside two separate SUM() calls, one for each fruit, and the two sums are subtracted from each other.

Approach

Step 1: Group by date Since the output needs one row per date, group the table using GROUP BY sale_date.

Step 2: Total apples sold per date Use SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END). This adds sold_num only for rows where the fruit is apples, and contributes 0 for every other row, so it does not disturb the total.

Step 3: Total oranges sold per date Same idea, but checking fruit = 'oranges' instead.

Step 4: Subtract the two totals Subtract the orange total from the apple total to get the day’s difference.

Step 5: Sort by date Add ORDER BY sale_date at the end so the output appears in chronological order.

SQL Query

SELECT
    sale_date,
    -- Total apples sold on this date (0 contributed by non-apple rows)
    SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) -
    -- Total oranges sold on this date, subtracted from the apple total
    SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;

Query Explanation

• SUM(CASE WHEN fruit = ‘apples’ THEN sold_num ELSE 0 END) — for every row within a given date group, this checks if the fruit is apples. If yes, it adds sold_num to the running total. If no, it adds 0, meaning it has no effect. The result is the total apples sold for that date.
• SUM(CASE WHEN fruit = ‘oranges’ THEN sold_num ELSE 0 END) — the exact same idea, but for oranges.
• Subtraction between the two SUMs — since both SUM expressions are calculated independently per date group, subtracting them gives the final difference for that specific date.
• AS diff — names the resulting column, matching the expected output format.
• GROUP BY sale_date — this is what makes the two SUM expressions calculate separately for each date, instead of adding up across the entire table at once.
• ORDER BY sale_date — sorts the final grouped results chronologically, as required by the problem.

Dry Run

Original table

↓

Step 1: GROUP BY sale_date forms four separate groups

↓

Step 2: Evaluate CASE inside each SUM, for each group

For 2020-05-01:

• apples sum = 10 (only the apples row contributes, oranges row contributes 0)
• oranges sum = 8 (only the oranges row contributes, apples row contributes 0)

For 2020-05-02:

• apples sum = 15
• oranges sum = 15

For 2020-05-03:

• apples sum = 20
• oranges sum = 0

For 2020-05-04:

• apples sum = 15
• oranges sum = 16

↓

Step 3: Subtract to get diff, per group

↓

Final output (already in date order)

SQL Execution Flow

FROM Sales
   ↓
GROUP BY sale_date (splits rows into per-date groups)
   ↓
Within each group: evaluate CASE per row, feed into SUM (apples total)
   ↓
Within each group: evaluate CASE per row, feed into SUM (oranges total)
   ↓
Subtract oranges total from apples total → diff
   ↓
SELECT sale_date, diff
   ↓
ORDER BY sale_date
   ↓
Return final sorted output

The two SUM(CASE…) expressions are both aggregate functions, so they are calculated during the GROUP BY step, before the final SELECT list is assembled. ORDER BY, as always, is the very last operation applied.

Time Complexity

Time Complexity: O(n log n) Grouping the rows by sale_date typically requires either sorting the data or building a hash-based grouping structure, both of which commonly run in O(n log n) time for large datasets (though a hash-based GROUP BY can sometimes behave closer to O(n) depending on the database engine). The additional ORDER BY at the end doesn’t add extra complexity here, since the number of groups (dates) is usually much smaller than the number of raw rows, and many databases can reuse the same sorted structure for both grouping and ordering.

Space Complexity: O(d), where d is the number of distinct dates The database needs to keep a running total for apples and oranges for each unique date while scanning through the rows, so the memory used grows with the number of distinct groups, not the number of raw rows.

Why: Unlike Problems 1 through 3, this query cannot make a decision using just one row at a time, it needs to gather and total values across multiple rows that share the same date. This grouping step is what introduces the extra complexity compared to a simple per-row CASE.

Common Mistakes to Avoid

Mistake 1: Forgetting GROUP BY entirely

Why it happens: It’s easy to write the SUM(CASE…) logic correctly but forget that grouping is what makes it calculate per date instead of across the whole table.

Wrong:

SELECT
    sale_date,
    SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) -
    SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) AS diff
FROM Sales;

Without GROUP BY, most databases will either throw an error (since sale_date is a non-aggregated column mixed with aggregate functions) or, in more lenient databases, silently collapse everything into a single row, which is not the expected output at all.

Correct: Always include GROUP BY sale_date when mixing a plain column with aggregate functions like SUM.

Mistake 2: Forgetting ELSE 0 inside the CASE

Why it happens: It might seem like ELSE isn’t strictly necessary, similar to some earlier problems.

Wrong:

SUM(CASE WHEN fruit = 'apples' THEN sold_num END)

Without ELSE, non-apple rows return NULL instead of 0. SUM does ignore NULLs by default, so this actually still produces the correct total in this specific case, since SUM only adds up non-NULL values anyway. However, relying on this behavior without understanding it can lead to confusion, especially if someone later changes SUM to something like AVG, where ignoring NULLs versus counting them as 0 produces very different results.

Correct: Explicitly write ELSE 0 for clarity and to avoid surprises if the aggregate function ever changes.

Mistake 3: Using two separate queries and trying to join them manually

Why it happens: Beginners sometimes think they need one query for apple totals and another for orange totals, then join them together on sale_date.

Wrong (overly complex):

SELECT a.sale_date, a.apple_total - o.orange_total AS diff
FROM (SELECT sale_date, SUM(sold_num) AS apple_total FROM Sales WHERE fruit = 'apples' GROUP BY sale_date) a
JOIN (SELECT sale_date, SUM(sold_num) AS orange_total FROM Sales WHERE fruit = 'oranges' GROUP BY sale_date) o
ON a.sale_date = o.sale_date;

This can work, but it’s needlessly complex, requires two subqueries and a join, and can break entirely if a date has apples sold but zero oranges rows recorded (or vice versa), since an INNER JOIN would silently drop that date from the results.

Correct: Use conditional aggregation with SUM(CASE…) in a single pass over the table, as shown in the main solution, which naturally handles missing fruit rows for a given date without dropping anything.

Mistake 4: Assuming fruit values are always lowercase or always match exactly

Why it happens: The enum type suggests a fixed set of values, but real-world data can still contain inconsistent casing (like ‘Apples’ instead of ‘apples’) if the enum wasn’t strictly enforced or if data was imported from multiple sources.

Why it matters: fruit = 'apples' will not match 'Apples' in a case-sensitive comparison, causing those rows to be silently missed and treated as 0 contribution.

Correct: If there’s any doubt about casing consistency, consider using a case-insensitive comparison (like LOWER(fruit) = 'apples') or confirm the enum’s exact allowed values before writing the query.

Mistake 5: Placing ORDER BY before GROUP BY in the query

Why it happens: Beginners sometimes write clauses in a different order than SQL expects.

Wrong:

SELECT sale_date, SUM(...) - SUM(...) AS diff
FROM Sales
ORDER BY sale_date
GROUP BY sale_date;

This is a syntax error in standard SQL. The clause order must be SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Correct:

SELECT sale_date, SUM(...) - SUM(...) AS diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;

Further Questions

• “How would you modify this to also show the total combined fruit sales per day, alongside the difference?”
• “What would happen if a new fruit type, like ‘bananas,’ was added to the table? Would this query still work correctly?”
• “How would you calculate the running total of the difference over time, instead of just the daily difference?”
• “How would you find the single day with the largest absolute difference between apples and oranges?”
• “How would this query behave if a date had apples sold but no orange row recorded for that date at all?”

Similar Problems

• The original “apples vs oranges total” example from the CASE Expression concept notes, which solved the same idea but without grouping by date
• Problems involving pivoting row-based data into column-based summaries using conditional aggregation
• Problems requiring day-over-day or period-over-period comparisons, often extended further using window functions like LAG() or LEAD()

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to compare two categories side-by-side, per group (like per day, per region, per department) → SUM(CASE WHEN …) for each category, then subtract or compare them directly in the SELECT clause
• Need one row of output per date, category, or group, with multiple calculated totals inside it → GROUP BY combined with conditional aggregation, instead of separate queries or joins
• Tempted to solve a comparison problem with two subqueries and a JOIN → consider whether conditional aggregation in a single query would be simpler and safer against missing rows
• Output needs multiple aggregate expressions combined with plain arithmetic (like subtraction) → this is completely valid, SQL allows arithmetic directly between aggregate function results in the same SELECT