Loyalty Tier of Each Subscriber

Problem

You are given three DataFrames. `subscribers` has columns `subscriber_id` and `handle` (`subscriber_id` is unique). `sessions` has columns `session_id` and `subscriber_id`; each row is one app session opened by a subscriber (`session_id` is unique). `orders` has columns `order_id` and `session_id`; each row is a purchase made during a session (`order_id` is unique).

For each subscriber, compute their conversion rate as `100 * (number of sessions that led to an order) / (number of sessions)` using integer division, then assign a tier: `Platinum` when the rate is at least `80`, `Silver` when it is in `[50, 80)`, `Bronze` when it is below `50`, and `Inactive` when the subscriber had no sessions at all. Return `subscriber_id`, `handle`, and the assigned `tier`, in any order.

Input data

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 4 rows with the columns subscriber_id, handle, tier.

Starter code (Pandas (Python))

import pandas as pd

def loyalty_tier_of_each_subscriber(subscribers, sessions, orders) -> pd.DataFrame:
    # Your code here
    return subscribers

Related Pandas questions

• 7-Day Rolling Signup AverageMedium
• Accounts Streaming From Two Devices at OnceMedium
• Agent Resolution RateMedium
• Agents With a Dominant Contact ChannelMedium
• Airbnb Popularity Bucket PricingMedium
• Amazon Unique Shipments per MonthMedium