Compute First Order Proportions by Day and Category

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.