Market Analysis I

Market Analysis I

What is this problem about?

The Market Analysis I interview question is a Database/SQL problem. You are given three tables: Users (user info and join date), Orders (order details including the buyer ID and order date), and Items. Your task is to write a query that finds the total number of orders placed by each user strictly during the year 2019. If a user placed no orders in 2019, their count should be 0.

Why is this asked in interviews?

This is a core SQL problem asked frequently by e-commerce companies like Amazon. It tests a candidate's mastery of the LEFT JOIN and conditional aggregations. Interviewers use it to identify a very specific, common error candidates make regarding where to place filtering conditions (in the WHERE clause versus the ON clause). It evaluates your ability to retain base records even when joining data doesn't exist.

Algorithmic pattern used

The solution relies on the LEFT JOIN with Conditional ON clause pattern. To ensure that all users appear in the final output (even those with 0 orders), you must LEFT JOIN the Users table with the Orders table. The crucial trick is that the filter for the year 2019 must be placed in the ON clause of the join, NOT in the WHERE clause. After joining, you group by the user ID and COUNT the order_id.

Example explanation

Users table:

• User 1, joined 2018
• User 2, joined 2019

Orders table:

• Order A: Buyer 1, Date '2019-03-01'
• Order B: Buyer 1, Date '2020-01-01'

If we use LEFT JOIN Orders ON Users.id = Orders.buyer_id AND YEAR(order_date) = '2019':

• User 1 successfully joins with Order A. (Count will be 1).
• User 1 tries to join with Order B, but it's 2020, so it fails the join.
• User 2 tries to join, has no orders. The LEFT JOIN keeps User 2 with null order columns. (Count will be 0). Result: User 1 has 1 order, User 2 has 0 orders.

Common mistakes candidates make

The absolute most common mistake is writing: LEFT JOIN Orders ON Users.id = Orders.buyer_id WHERE YEAR(order_date) = '2019'. Placing the year filter in the WHERE clause turns the LEFT JOIN into an INNER JOIN. If User 2 has no orders, their order_date is null. null = '2019' evaluates to false, and User 2 is completely removed from the final result set!

Interview preparation tip

To ace the Market Analysis I SQL problem, permanently etch this rule into your memory: when doing a LEFT JOIN to count or aggregate child records, filters applying to the child table (like date ranges or statuses) MUST go in the ON clause. Filters applying to the parent table (like "only show users from New York") can go in the WHERE clause.