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.

Customer First Order Proportions by Day and Category

You are given an ORDERS table for an e-commerce platform:

• order_id: unique identifier of the order
• date: calendar date of the order
• category: product category of the order
• customer_id: unique identifier of the customer

Task:

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

Return both metrics in a single result set. Use category = 'ALL' for the per-day overall proportion rows and label metrics using a column named metric (e.g., 'first_in_category_rate' and 'first_overall_rate').

Table ORDERS

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
date	DATE	NOT NULL
category	VARCHAR(50)	NOT NULL
customer_id	INTEGER	NOT NULL

Sample data:

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

Expected output

Output a row per date and category with the proportion of orders that are the customer’s first order in that category, and a row per date with the proportion of all orders that are the customer’s first order across any category. Overall rows use category = 'ALL'. The metric column distinguishes 'first_in_category_rate' versus 'first_overall_rate'. Proportion is a numeric value between 0 and 1.

category	date	metric	proportion
Books	2025-01-01	first_in_category_rate	1
Music	2025-01-01	first_in_category_rate	1
ALL	2025-01-01	first_overall_rate	1
Books	2025-01-02	first_in_category_rate	0
ALL	2025-01-02	first_overall_rate	0
Books	2025-01-03	first_in_category_rate	1
Music	2025-01-03	first_in_category_rate	1
ALL	2025-01-03	first_overall_rate	0.5

Hints

1. Use ROW_NUMBER() over partitions by (customer_id, category) to flag a customer’s first order in each category.
2. Use ROW_NUMBER() over partitions by customer_id (without category) to flag each customer’s first order overall.
3. Aggregate by date and category (and by date alone) and compute the AVG of the 0/1 flags to get proportions, then UNION ALL the two result sets.