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
Member Category Classification INPUT TABLES Members Table: member_id name 1 Alice 2 Bob 3 Charlie 4 Diana Visits Table: member_id visit_date 1 2023-01-01 2 2023-01-02 ... Purchases Table: member_id purchase_id 1 101 ... ALGORITHM STEPS 1 LEFT JOIN Tables Join Members with Visits and Purchases 2 Count Aggregations COUNT visits per member COUNT purchases per member 3 Calculate Rate rate = 100 * purchases / visits 4 Apply CASE Logic Categorize by rate CASE WHEN visits=0 --> 'Bronze' WHEN rate >= 80 --> 'Diamond' WHEN rate >= 50 --> 'Gold' ELSE 'Silver' FINAL RESULT id name category 1 Alice Diamond 2 Bob Gold 3 Charlie Silver 4 Diana Bronze Category Thresholds: Diamond: rate >= 80 Gold: 50 <= rate < 80 Silver: rate < 50 Bronze: no visits OK - All members categorized! Key Insight: Use LEFT JOIN to include all members, even those with no visits (Bronze category). The CASE statement handles NULL visits by checking for zero visits FIRST before calculating the conversion rate, avoiding division by zero errors. TutorialsPoint - The Category of Each Member in the Store | Optimal Solution

Related Problems

Asked in
Amazon 15 Google 12 Microsoft 8
24.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen