Loyalty Tier of Each Subscriber

Problem

Table: `subscribers`

| Column Name | Type |
|---|---|
| subscriber_id | int |
| handle | varchar |

`subscriber_id` is the primary key.

Table: `sessions`

| Column Name | Type |
|---|---|
| session_id | int |
| subscriber_id | int |

`session_id` is the primary key. Each row is one app session opened by a subscriber.

Table: `orders`

| Column Name | Type |
|---|---|
| order_id | int |
| session_id | int |

`order_id` is the primary key. Each row is a purchase made during a session.

For each subscriber, compute their conversion rate as `100 * (number of sessions that led to an order) / (number of sessions)`, 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`. Return the result in any order.

Tables

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 (SQL)

SELECT *
FROM subscribers;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium