Apples & Oranges

Apples & Oranges

What is this problem about?

The "Apples & Oranges interview question" is a SQL data transformation problem. You are given a table of sales data where each row contains a date, the type of fruit (Apples or Oranges), and the quantity sold. Your task is to calculate the difference between the number of apples sold and the number of oranges sold for each day.

Why is this asked in interviews?

Meta asks the "Apples & Oranges coding problem" to test a candidate's ability to perform Pivoting or Conditional Aggregation in SQL. It's a common task in business intelligence to compare two different categories side-by-side when they are stored in a long-format (normalized) table.

Algorithmic pattern used

This problem uses the GROUP BY with CASE WHEN pattern.

1. Aggregation: Group the data by the sale_date.
2. Conditional Sum: Inside the SELECT statement, use SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) to get the apple total.
3. Difference: Subtract the result of a similar SUM for oranges.
4. Ordering: Sort the final result by date.

Example explanation

Data:

• 2023-01-01, Apples, 10
• 2023-01-01, Oranges, 8
• 2023-01-02, Apples, 15
• 2023-01-02, Oranges, 20 Processing:
• Day 1: $(10) - (8) = 2$.
• Day 2: $(15) - (20) = -5$. Output: (2023-01-01, 2), (2023-01-02, -5).

Common mistakes candidates make

• Hardcoding dates: Not using GROUP BY, which would make the query fail if new dates are added.
• Incorrect joins: Trying to join the table to itself on the date. While this works, conditional aggregation is usually cleaner and more performant.
• Null handling: Forgetting that if a fruit wasn't sold on a specific day, the SUM might be NULL (use COALESCE or ELSE 0 to fix this).

Interview preparation tip

Practice "Pivoting" data in SQL. Converting rows to columns is one of the most useful skills for data analysis interviews. Master the CASE WHEN statement as it is the Swiss Army knife of SQL logic.