Analyze Order Spending Patterns Across Cities Using SQL

Orders

order_id | user_id | order_date | city | order_value

1 | 101 | 2023-01-03 | LA | 23.50

2 | 102 | 2023-01-04 | NY | 45.00

3 | 101 | 2023-01-10 | LA | 19.00

4 | 103 | 2023-01-11 | LA | 55.75

5 | 104 | 2023-01-12 | SF | 32.20

Scenario

An e-commerce marketplace wants detailed insight into order spending patterns across cities.

Question

Using the Orders table, write one SQL query that returns for every order:

1. order_id, user_id, order_date, city, order_value, 2) previous_order_value of the same user (NULL if first), 3) rank of the order_value within its city ordered from highest to lowest, and
2. percent_rank of the order_value within its city.

Hints

Combine LAG(), RANK() and PERCENT_RANK() partitioned appropriately; remember to order windows by order_date or order_value as required.

Order analytics with window functions by user and city

Table Orders

column	type	constraints
order_id	INTEGER
user_id	INTEGER
order_date	DATE
city	VARCHAR(50)
order_value	DECIMAL(10,2)

Sample data:

city	order_date	order_id	order_value	user_id
LA	2023-01-03	1	23.5	101
NY	2023-01-04	2	45	102
LA	2023-01-10	3	19	101
LA	2023-01-11	4	55.75	103
SF	2023-01-12	5	32.2	104

Expected output

For each order, include the order details, the previous order's value by the same user (based on order_date), the rank of order_value within the same city from highest to lowest, and the percent_rank within that city.

city	order_date	order_id	order_value	percent_rank_in_city	previous_order_value	user_id	value_rank_in_city
LA	2023-01-03	1	23.5	0.5	NULL	101	2
NY	2023-01-04	2	45	0	NULL	102	1
LA	2023-01-10	3	19	1	23.5	101	3
LA	2023-01-11	4	55.75	0	NULL	103	1
SF	2023-01-12	5	32.2	0	NULL	104	1

Hints

1. Use LAG(order_value) OVER (PARTITION BY user_id ORDER BY order_date, order_id) to retrieve the previous order value for each user.
2. Use RANK() and PERCENT_RANK() OVER (PARTITION BY city ORDER BY order_value DESC) to compute within-city rankings.