The Category of Each Member in the Store - Problem
A store wants to categorize its members based on their conversion rate. The conversion rate of a member is calculated as: (100 * total number of purchases) / total number of visits.
Member Categories:
- Diamond: conversion rate ≥ 80
- Gold: conversion rate ≥ 50 and < 80
- Silver: conversion rate < 50
- Bronze: member never visited the store
Write a SQL query to report the member_id, name, and category of each member.
Table Schema
Members
| Column Name | Type | Description |
|---|---|---|
member_id
PK
|
int | Unique member identifier |
name
|
varchar | Member name |
Primary Key: member_id
Visits
| Column Name | Type | Description |
|---|---|---|
visit_id
PK
|
int | Unique visit identifier |
member_id
|
int | Foreign key to Members table |
visit_date
|
date | Date of the visit |
Primary Key: visit_id
Purchases
| Column Name | Type | Description |
|---|---|---|
visit_id
PK
|
int | Foreign key to Visits table |
charged_amount
|
int | Amount charged during the visit |
Primary Key: visit_id
Input & Output
Example 1 — Different Member Categories
Input Tables:
Members
| member_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Visits
| visit_id | member_id | visit_date |
|---|---|---|
| 1 | 1 | 2021-11-28 |
| 2 | 1 | 2021-12-03 |
| 3 | 1 | 2021-12-14 |
| 4 | 2 | 2021-11-28 |
| 5 | 2 | 2021-12-03 |
Purchases
| visit_id | charged_amount |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
Output:
| member_id | name | category |
|---|---|---|
| 1 | Alice | Diamond |
| 2 | Bob | Gold |
| 3 | Charlie | Bronze |
💡 Note:
Alice has 3 visits and 3 purchases (conversion rate = 100%), so she's Diamond. Bob has 2 visits and 1 purchase (conversion rate = 50%), so he's Gold. Charlie never visited the store, so he's Bronze.
Example 2 — Silver Category Member
Input Tables:
Members
| member_id | name |
|---|---|
| 1 | David |
Visits
| visit_id | member_id | visit_date |
|---|---|---|
| 1 | 1 | 2021-11-28 |
| 2 | 1 | 2021-12-03 |
| 3 | 1 | 2021-12-14 |
Purchases
| visit_id | charged_amount |
|---|---|
| 1 | 100 |
Output:
| member_id | name | category |
|---|---|---|
| 1 | David | Silver |
💡 Note:
David has 3 visits but only 1 purchase, giving him a conversion rate of 33.33%, which is less than 50%, so he's categorized as Silver.
Constraints
-
1 ≤ member_id ≤ 1000 -
1 ≤ visit_id ≤ 1000 -
charged_amount ≥ 0 - All dates are valid
Visualization
Tap to expand
Understanding the Visualization
1
Join Tables
LEFT JOIN Members with Visits and Purchases
2
Calculate
Count visits and purchases per member
3
Categorize
Apply conversion rate thresholds
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to preserve all members, even those who never visited the store
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code