Apples & Oranges - Practice Coding Problems

Apples & Oranges - Problem

Database Medium

You're the manager of a fruit stand that sells only apples and oranges. Each day, you record the sales of both fruits in your database. Now you need to analyze your daily performance by calculating the net difference between apple and orange sales.

Given a Sales table with the following structure:

Column Name	Type
sale_date	date
fruit	enum ('apples', 'oranges')
sold_num	int

Where (sale_date, fruit) is the primary key, write a SQL query to calculate the difference between apples and oranges sold each day. The result should show apples_sold - oranges_sold for each date.

Goal: Return a table with sale_date and diff (positive if more apples sold, negative if more oranges sold), ordered by sale_date.

Input & Output

example_1.sql — Basic Case

$ Input: Sales table: | sale_date | fruit | sold_num | |------------|----------|---------| | 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 |

› Output: | sale_date | diff | |------------|----- | | 2020-05-01 | 2 | | 2020-05-02 | 0 | | 2020-05-03 | 20 |

💡 Note: For 2020-05-01: 10 apples - 8 oranges = 2. For 2020-05-02: 15 apples - 15 oranges = 0. For 2020-05-03: 20 apples - 0 oranges = 20 (no oranges sold that day).

example_2.sql — Only Oranges Some Days

$ Input: Sales table: | sale_date | fruit | sold_num | |------------|----------|---------| | 2020-05-01 | oranges | 12 | | 2020-05-02 | apples | 25 | | 2020-05-03 | oranges | 18 | | 2020-05-03 | apples | 5 |

› Output: | sale_date | diff | |------------|----- | | 2020-05-01 | -12 | | 2020-05-02 | 25 | | 2020-05-03 | -13 |

💡 Note: For 2020-05-01: 0 apples - 12 oranges = -12. For 2020-05-02: 25 apples - 0 oranges = 25. For 2020-05-03: 5 apples - 18 oranges = -13.

example_3.sql — Single Entry Per Day

$ Input: Sales table: | sale_date | fruit | sold_num | |------------|----------|---------| | 2020-05-01 | apples | 7 | | 2020-05-02 | oranges | 3 |

› Output: | sale_date | diff | |------------|----- | | 2020-05-01 | 7 | | 2020-05-02 | -3 |

💡 Note: Edge case where some days only have one type of fruit. For 2020-05-01: 7 apples - 0 oranges = 7. For 2020-05-02: 0 apples - 3 oranges = -3.

Visualization

Tap to expand

Understanding the Visualization

Daily Sales Records

Each row represents sales of one fruit type on a specific date

Group by Date

Collect all sales records for the same date together

Score Calculation

Add points for apples (+), subtract points for oranges (-)

Daily Winner

Positive score means apples won, negative means oranges won

Key Takeaway

🎯 Key Insight: Conditional aggregation with CASE statements lets us calculate the difference in a single pass, making it the most efficient solution at O(n) time complexity.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with GROUP BY operation, linear time complexity

✓ Linear Growth

Space Complexity

O(k)

Space proportional to number of unique dates (k), typically much smaller than total records (n)

✓ Linear Space

Constraints

1 ≤ number of rows in Sales ≤ 1000
sale_date is in format 'YYYY-MM-DD'
fruit is either 'apples' or 'oranges'
1 ≤ sold_num ≤ 100
Each (sale_date, fruit) combination appears at most once

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses conditional aggregation with GROUP BY sale_date and SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE -sold_num END) to calculate the apple-orange difference in a single pass with O(n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation (Optimal)	O(n)	O(k)	Use GROUP BY with conditional SUM to calculate differences in one pass
Brute Force (Correlated Subqueries)	O(n²)	O(1)	Use separate subqueries for each fruit type per date
Self-Join Approach	O(n log n)	O(n)	Join the table with itself to match apple and orange sales by date

Conditional Aggregation (Optimal) — Algorithm Steps

Group all records by sale_date
Use SUM with CASE to conditionally sum apples (positive)
Use SUM with CASE to conditionally sum oranges (negative)
Calculate net difference in a single expression
Order results by sale_date

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Date

Partition all records by sale_date

Conditional Sum

For each group, sum apples positively and oranges negatively

Calculate Diff

The SUM result is automatically the difference

Order Results

Sort by sale_date for final output

Code -

solution.c — C

// Optimal SQL query using conditional aggregation
char* sqlQuery = "\
SELECT \
    sale_date, \
    SUM( \
        CASE \
            WHEN fruit = 'apples' THEN sold_num \
            WHEN fruit = 'oranges' THEN -sold_num \
            ELSE 0 \
        END \
    ) AS diff \
FROM Sales \
GROUP BY sale_date \
ORDER BY sale_date;";

// Execute with SQLite C API
#include <sqlite3.h>
#include <stdio.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    printf("%s: %s\n", argv[0], argv[1]);
    return 0;
}

int main() {
    sqlite3* db;
    int rc = sqlite3_open("database.db", &db);
    
    if (rc) {
        sqlite3_close(db);
        return 1;
    }
    
    rc = sqlite3_exec(db, sqlQuery, callback, 0, NULL);
    sqlite3_close(db);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with GROUP BY operation, linear time complexity

✓ Linear Growth

Space Complexity

O(k)

Space proportional to number of unique dates (k), typically much smaller than total records (n)

✓ Linear Space

Constraints

1 ≤ number of rows in Sales ≤ 1000
sale_date is in format 'YYYY-MM-DD'
fruit is either 'apples' or 'oranges'
1 ≤ sold_num ≤ 100
Each (sale_date, fruit) combination appears at most once

58.6K Views

High Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Conditional Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler