Lyft Power User Segmentation

0:00:00

Lyft’s analytics team is preparing a city-level rider loyalty program. A rider qualifies as a power user if they are in the top 5% of riders by completed trips within their city over the last 90 days. The 90-day window is defined relative to the maximum request_time in the dataset.

Using the rides table, write a query that:

Counts the number of completed trips per (city_id, user_id) in the last 90 days as trip_count_90d
Computes, within each city_id, the 95th percentile of trip_count_90d and uses it as the power-user threshold
Labels each user as:
- 'power_user' if their trip_count_90d is greater than or equal to the threshold
- 'regular' otherwise

Note: If a city has fewer than 20 users, treat the single highest-trip user as the power user (break any ties by lowest user_id).

Schema:

Input:

rides table

Column	Type
`ride_id`	INTEGER
`user_id`	INTEGER
`city_id`	INTEGER
`request_time`	TIMESTAMP
`status`	VARCHAR

Output:

Column	Type
`city_id`	INTEGER
`user_id`	INTEGER
`trip_count_90d`	INTEGER
`user_segment`	VARCHAR

Example:

Input:

ride_id	user_id	city_id	request_time	status
1	101	10	2024-05-01 08:00:00	completed
2	101	10	2024-05-03 09:00:00	completed
3	102	10	2024-05-02 10:00:00	completed
4	103	10	2024-05-04 11:00:00	cancelled
5	102	10	2024-05-06 12:00:00	completed

Output:

city_id	user_id	trip_count_90d	user_segment
10	101	2	regular
10	102	2	regular

Explanation:

The maximum request_time in the dataset is 2024-05-06 12:00:00, so the 90-day window includes all rides
User 101 in city 10: 2 completed trips (rides 1 and 2).
User 102 in city 10: 2 completed trips (rides 3 and 5).
User 103 in city 10: 0 completed trips (ride 4 was cancelled, so excluded).
City 10 has fewer than 20 users (only 2 users with completed trips), so the top user should be the power user. However, both users are tied at 2 trips. User 101 has the lower user_id, so user 101 would normally be the power user.

.
.
.
.
.

Comments

Loading comments