Member Engagement Tier by Visit-Purchase Conversion

Medium

A retail operations team tracks customer store activity and checkout outcomes. They want each member placed into an engagement tier based on how often visits convert into purchases. Table: members member_id: unique member identifier name: member display name Table: visits visit_id: unique visit identifier member_id: references members.member_id visit_date: calendar date of the store visit Table: purchases visit_id: unique purchase event identifier; references visits.visit_id charged_amount: billed amount for that purchased visit Business tiers: Diamond: conversion rate >= 80 Gold: conversion rate >= 50 and < 80 Silver: conversion rate < 50 Bronze: member has zero visits Conversion rate definition: (100 * number of purchased visits) / total visits for that member Task: Return every member's tier classification. Output requirements: Return exactly three columns: member_id, name, category Return one row for each member in members Output order may be arbitrary Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

members:
| member_id | name   |
|-----------|--------|
| 1         | Ava    |
| 2         | Bruno  |
| 3         | Cora   |
| 4         | Dev    |
| 5         | Eli    |

visits:
| visit_id | member_id | visit_date  |
|----------|-----------|-------------|
| 101      | 2         | 2024-03-01  |
| 102      | 2         | 2024-03-02  |
| 103      | 2         | 2024-03-03  |
| 104      | 3         | 2024-03-04  |
| 105      | 3         | 2024-03-05  |
| 106      | 4         | 2024-03-06  |
| 107      | 4         | 2024-03-07  |
| 108      | 4         | 2024-03-08  |
| 109      | 4         | 2024-03-09  |
| 110      | 4         | 2024-03-10  |
| 111      | 5         | 2024-03-11  |

purchases:
| visit_id | charged_amount |
|----------|----------------|
| 101      | 320            |
| 104      | 210            |
| 106      | 700            |
| 107      | 650            |
| 108      | 540            |
| 109      | 580            |

Output

[
  {"member_id":1,"name":"Ava","category":"Bronze"},
  {"member_id":2,"name":"Bruno","category":"Silver"},
  {"member_id":3,"name":"Cora","category":"Gold"},
  {"member_id":4,"name":"Dev","category":"Diamond"},
  {"member_id":5,"name":"Eli","category":"Silver"}
]

Explanation

Member 1 has no visits (Bronze). Member 2 has 1/3 purchases (~33.33%, Silver). Member 3 has 1/2 (50%, Gold). Member 4 has 4/5 (80%, Diamond). Member 5 has 0/1 (Silver).

Example 2

Input

members:
| member_id | name   |
|-----------|--------|
| 10        | Iris   |
| 11        | Jude   |
| 12        | Kian   |
| 13        | Lina   |

visits:
| visit_id | member_id | visit_date  |
|----------|-----------|-------------|
| 201      | 10        | 2024-05-01  |
| 202      | 10        | 2024-05-02  |
| 203      | 10        | 2024-05-03  |
| 204      | 10        | 2024-05-04  |
| 205      | 11        | 2024-05-01  |
| 206      | 11        | 2024-05-02  |
| 207      | 11        | 2024-05-03  |
| 208      | 11        | 2024-05-04  |
| 209      | 11        | 2024-05-05  |
| 210      | 12        | 2024-05-01  |
| 211      | 12        | 2024-05-02  |
| 212      | 12        | 2024-05-03  |

purchases:
| visit_id | charged_amount |
|----------|----------------|
| 201      | 50             |
| 202      | 40             |
| 205      | 90             |
| 206      | 95             |
| 207      | 87             |
| 208      | 100            |
| 210      | 30             |

Output

[
  {"member_id":10,"name":"Iris","category":"Gold"},
  {"member_id":11,"name":"Jude","category":"Diamond"},
  {"member_id":12,"name":"Kian","category":"Silver"},
  {"member_id":13,"name":"Lina","category":"Bronze"}
]

Explanation

Iris is 2/4=50% (Gold), Jude is 4/5=80% (Diamond), Kian is 1/3 (~33.33%, Silver), and Lina has no visits (Bronze).

Example 3

Input

members:
[]

visits:
[]

purchases:
[]

Output

[]

Explanation

When there are no members, the result is empty.

Constraints

0 <= number of rows in members <= 650
0 <= number of rows in visits <= 1800
0 <= number of rows in purchases <= 1800
members.member_id values are unique
visits.visit_id values are unique
purchases.visit_id values are unique
visits.member_id references members.member_id
purchases.visit_id references visits.visit_id
1 <= charged_amount <= 100000
Return exactly columns: member_id, name, category
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns member_id, name, category

members: | member_id | name | |-----------|--------| | 1 | Ava | | 2 | Bruno | | 3 | Cora | | 4 | Dev | | 5 | Eli | visits: | visit_id | member_id | visit_date | |----------|-----------|-------------| | 101 | 2 | 2024-03-01 | | 102 | 2 | 2024-03-02 | | 103 | 2 | 2024-03-03 | | 104 | 3 | 2024-03-04 | | 105 | 3 | 2024-03-05 | | 106 | 4 | 2024-03-06 | | 107 | 4 | 2024-03-07 | | 108 | 4 | 2024-03-08 | | 109 | 4 | 2024-03-09 | | 110 | 4 | 2024-03-10 | | 111 | 5 | 2024-03-11 | purchases: | visit_id | charged_amount | |----------|----------------| | 101 | 320 | | 104 | 210 | | 106 | 700 | | 107 | 650 | | 108 | 540 | | 109 | 580 |

[ {"member_id":1,"name":"Ava","category":"Bronze"}, {"member_id":2,"name":"Bruno","category":"Silver"}, {"member_id":3,"name":"Cora","category":"Gold"}, {"member_id":4,"name":"Dev","category":"Diamond"}, {"member_id":5,"name":"Eli","category":"Silver"} ]

members: | member_id | name | |-----------|--------| | 10 | Iris | | 11 | Jude | | 12 | Kian | | 13 | Lina | visits: | visit_id | member_id | visit_date | |----------|-----------|-------------| | 201 | 10 | 2024-05-01 | | 202 | 10 | 2024-05-02 | | 203 | 10 | 2024-05-03 | | 204 | 10 | 2024-05-04 | | 205 | 11 | 2024-05-01 | | 206 | 11 | 2024-05-02 | | 207 | 11 | 2024-05-03 | | 208 | 11 | 2024-05-04 | | 209 | 11 | 2024-05-05 | | 210 | 12 | 2024-05-01 | | 211 | 12 | 2024-05-02 | | 212 | 12 | 2024-05-03 | purchases: | visit_id | charged_amount | |----------|----------------| | 201 | 50 | | 202 | 40 | | 205 | 90 | | 206 | 95 | | 207 | 87 | | 208 | 100 | | 210 | 30 |

[ {"member_id":10,"name":"Iris","category":"Gold"}, {"member_id":11,"name":"Jude","category":"Diamond"}, {"member_id":12,"name":"Kian","category":"Silver"}, {"member_id":13,"name":"Lina","category":"Bronze"} ]