Compute First Order Proportions by Day and Category

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Business Intelligence Engineer candidates at Amazon during technical interviews.

Question

ORDERS

+----------+------------+----------+-------------+
| order_id | date       | category | customer_id |
+----------+------------+----------+-------------+
| 100      | 2025-01-01 | Books    | 1           |
| 101      | 2025-01-02 | Books    | 1           |
| 102      | 2025-01-01 | Music    | 2           |
| 103      | 2025-01-03 | Music    | 3           |
| 104      | 2025-01-03 | Books    | 2           |
+----------+------------+----------+-------------+

##### Scenario

E-commerce platform analyzing customer purchasing behavior over time.

##### Question

For each calendar day and product category, compute the proportion of orders that are the customer's first order in that category. For each calendar day, compute the proportion of all orders that are a customer’s first order across any category.

##### Hints

Use window functions or self-joins to flag first orders, then aggregate by day and category.

PracHub · Accepted Answer

This question evaluates a candidate's ability to perform event-level data manipulation and customer-level deduplication using SQL or Python, focusing on calculating the proportion of orders that represent a customer's first purchase by day and category.

Quick Overview

Compute First Order Proportions by Day and Category