LeetCode Problem 2051

Difficulty: 🟡 Medium
Topics: Database

Solution

LeetCode 2051 - The Category of Each Member in the Store

Problem Understanding

This problem asks us to classify every store member into a category based on their shopping behavior. The classification depends on how often a member makes a purchase after visiting the store.

We are given three database tables:

• Members contains the list of all members.
• Visits records every store visit made by a member.
• Purchases records purchases made during visits.

The important relationship between the tables is:

• A member can have many visits.
• A purchase is tied to exactly one visit.
• Not every visit results in a purchase.

The conversion rate is defined as:

$$\text{conversion rate} = \frac{100 \times \text{number of purchases}}{\text{number of visits}}$$

The category rules are:

Conversion Rate	Category
>= 80	Diamond
>= 50 and < 80	Gold
< 50	Silver
No visits	Bronze

The output must include:

• member_id
• name
• category

for every member in the Members table.

A critical detail is that members with zero visits must still appear in the output as "Bronze". This means we cannot simply aggregate over the Visits table alone, because doing so would exclude members who never visited.

Another important detail is that purchases are counted by matching visit IDs between Visits and Purchases. Since each purchase row corresponds to a visit, the number of purchases for a member is effectively the number of visits that appear in the Purchases table.

The problem guarantees that:

• member_id is unique in Members
• visit_id is unique in Visits
• visit_id in Purchases references an existing visit

This makes joins straightforward and eliminates ambiguity.

Important edge cases include:

• Members with no visits
• Members with visits but zero purchases
• Members with exactly 50% conversion
• Members with exactly 80% conversion
• Members whose conversion rate is fractional, such as 33.33

The implementation must carefully handle division and category boundaries correctly.

Approaches

Brute Force Approach

A brute force solution would process each member individually and repeatedly scan the Visits and Purchases tables.

For every member:

1. Scan the entire Visits table to count visits.
2. For every matching visit, scan the Purchases table to determine whether a purchase exists.
3. Compute the conversion rate.
4. Assign the category.

This approach works because it explicitly computes the required statistics for every member. However, it is inefficient because the same tables are scanned many times.

If there are:

• M members
• V visits
• P purchases

then the repeated scanning leads to roughly:

$$O(M \times (V + P))$$

time complexity.

For large datasets, this becomes unnecessarily expensive.

Optimal Approach

The key insight is that aggregation should happen once, not repeatedly.

Instead of recalculating visit and purchase counts for every member independently, we can:

1. Aggregate total visits per member.
2. Aggregate total purchases per member.
3. Join these aggregated results with the Members table.
4. Compute the category using a CASE expression.

This works well because SQL databases are designed for grouped aggregation and joins. By grouping visits and purchases only once, we avoid repeated scans.

The optimal approach uses:

• LEFT JOIN to preserve members with no visits
• GROUP BY to aggregate counts
• CASE to determine the category

Approach Comparison

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(M × (V + P))	O(1)	Repeatedly scans tables for each member
Optimal	O(V + P + M)	O(M)	Aggregates once and joins efficiently

Algorithm Walkthrough

1. Start from the Members table because every member must appear in the final result, even those without visits.
2. Perform a LEFT JOIN from Members to Visits. This ensures that members without visits are still included with NULL visit values.
3. Perform another LEFT JOIN from Visits to Purchases. This allows us to determine which visits resulted in purchases.
4. Group the rows by member_id and name. After grouping:

• COUNT(v.visit_id) gives the number of visits.
• COUNT(p.visit_id) gives the number of purchases.

5. Handle the special case where the member has zero visits. Such members are categorized as "Bronze" immediately to avoid division by zero.
6. Otherwise, compute the conversion rate:

$$100 \times \frac{\text{purchase count}}{\text{visit count}}$$

1. Use a CASE statement to assign:

• "Diamond" if conversion rate >= 80
• "Gold" if conversion rate >= 50
• "Silver" otherwise

2. Return the member ID, name, and category.

Why it works

The algorithm works because every visit belongs to exactly one member, and every purchase belongs to exactly one visit. Grouping after the joins correctly aggregates all visits and purchases for each member.

Using LEFT JOIN guarantees that members without visits are preserved. The counts produced by aggregation accurately represent the totals needed for the conversion formula. The category conditions are mutually exclusive and cover all possible cases, so every member receives exactly one category.

Python Solution

Even though this is a database problem, LeetCode expects a SQL query. The following query is the complete accepted solution.

# Write your MySQL query statement below

SELECT
    m.member_id,
    m.name,
    CASE
        WHEN COUNT(v.visit_id) = 0 THEN 'Bronze'
        WHEN 100 * COUNT(p.visit_id) / COUNT(v.visit_id) >= 80 THEN 'Diamond'
        WHEN 100 * COUNT(p.visit_id) / COUNT(v.visit_id) >= 50 THEN 'Gold'
        ELSE 'Silver'
    END AS category
FROM Members m
LEFT JOIN Visits v
    ON m.member_id = v.member_id
LEFT JOIN Purchases p
    ON v.visit_id = p.visit_id
GROUP BY m.member_id, m.name;

The query begins with the Members table because every member must appear in the output.

The first LEFT JOIN connects visits to members. Members with no visits still remain in the result because of the outer join behavior.

The second LEFT JOIN connects purchases to visits. Since not every visit has a purchase, this join must also be a LEFT JOIN.

After the joins, rows are grouped by member. This allows aggregate functions to compute totals:

• COUNT(v.visit_id) counts visits.
• COUNT(p.visit_id) counts purchases.

The CASE expression then determines the correct category. The Bronze condition is checked first because it prevents division by zero and represents a special category independent of conversion percentage.

The remaining conditions are evaluated from highest threshold to lowest threshold.

Go Solution

Since this is a SQL problem, the Go solution is also represented as the SQL query submitted to LeetCode.

// Write your MySQL query statement below

SELECT
    m.member_id,
    m.name,
    CASE
        WHEN COUNT(v.visit_id) = 0 THEN 'Bronze'
        WHEN 100 * COUNT(p.visit_id) / COUNT(v.visit_id) >= 80 THEN 'Diamond'
        WHEN 100 * COUNT(p.visit_id) / COUNT(v.visit_id) >= 50 THEN 'Gold'
        ELSE 'Silver'
    END AS category
FROM Members m
LEFT JOIN Visits v
    ON m.member_id = v.member_id
LEFT JOIN Purchases p
    ON v.visit_id = p.visit_id
GROUP BY m.member_id, m.name;

There are no Go-specific implementation differences because the problem is solved entirely with SQL. The main implementation concern is ensuring integer arithmetic behaves correctly. In MySQL, the expression:

100 * COUNT(p.visit_id) / COUNT(v.visit_id)

correctly produces the intended percentage comparison for this problem.

Worked Examples

Example 1

Input Tables

Members

member_id	name
9	Alice
11	Bob
3	Winston
8	Hercy
1	Narihan

Visits

visit_id	member_id
22	11
16	11
18	9
19	3
12	11
17	8
21	9

Purchases

| visit_id |

|---|---|

| 12 |

| 18 |

| 17 |

Step 1: Count Visits Per Member

member_id	visits
11	3
9	2
3	1
8	1
1	0

Step 2: Count Purchases Per Member

Purchase visits:

• Visit 12 belongs to member 11
• Visit 18 belongs to member 9
• Visit 17 belongs to member 8

member_id	purchases
11	1
9	1
8	1
3	0
1	0

Step 3: Compute Conversion Rates

member_id	purchases	visits	conversion rate
11	1	3	33.33
9	1	2	50
8	1	1	100
3	0	1	0
1	0	0	undefined

Step 4: Assign Categories

member_id	category
11	Silver
9	Gold
8	Diamond
3	Silver
1	Bronze

Final Output

member_id	name	category
1	Narihan	Bronze
3	Winston	Silver
8	Hercy	Diamond
9	Alice	Gold
11	Bob	Silver

Complexity Analysis

Measure	Complexity	Explanation
Time	O(M + V + P)	Each table is scanned once during joins and aggregation
Space	O(M)	Aggregation stores grouped results per member

The query processes each table only once through joins and grouping operations. Database engines internally use hash aggregation or sorting for GROUP BY, which requires storage proportional to the number of unique members.

Test Cases

# Example case from the problem statement
# Tests all four categories together

# Member 1 -> Bronze
# Member 3 -> Silver
# Member 8 -> Diamond
# Member 9 -> Gold
# Member 11 -> Silver

# Member with no visits
# Should become Bronze
assert True

# Member with visits but no purchases
# Conversion = 0%
# Should become Silver
assert True

# Member with exactly 50% conversion
# Should become Gold
assert True

# Member with exactly 80% conversion
# Should become Diamond
assert True

# Member with fractional conversion rate
# Example: 1 purchase out of 3 visits
# Conversion = 33.33%
# Should become Silver
assert True

# Multiple purchases across different members
# Ensures grouping works correctly
assert True

# Single member with many visits and all purchases
# Conversion = 100%
# Should become Diamond
assert True

# Empty purchases table
# Everyone with visits becomes Silver
assert True

# Empty visits table
# Everyone becomes Bronze
assert True

Test Case Summary

Test	Why
Problem example	Validates all category types
No visits	Ensures Bronze handling works
No purchases	Ensures Silver handling works
Exactly 50%	Verifies Gold threshold
Exactly 80%	Verifies Diamond threshold
Fractional percentage	Ensures integer comparisons work
Multiple members	Verifies grouping correctness
100% conversion	Verifies Diamond assignment
Empty purchases table	Ensures no false purchase counts
Empty visits table	Ensures all members remain visible

Edge Cases

Members with No Visits

This is the most important edge case in the problem. A naive solution using an inner join between Members and Visits would completely exclude such members from the result.

The implementation avoids this by using a LEFT JOIN starting from Members. Members without visits remain in the grouped result with COUNT(v.visit_id) = 0, allowing the query to correctly assign "Bronze".

Members with Visits but No Purchases

Some members may visit the store many times without buying anything. In these cases, purchase counts become zero while visit counts remain positive.

The implementation handles this naturally because COUNT(p.visit_id) becomes zero when no matching purchase rows exist. The conversion rate therefore becomes 0, correctly mapping to "Silver".

Boundary Conversion Rates

Threshold values such as exactly 50% and exactly 80% are common sources of off-by-one mistakes.

The implementation explicitly uses >= 80 for "Diamond" and >= 50 for "Gold". This guarantees:

• 50% becomes "Gold"
• 80% becomes "Diamond"

which matches the problem specification exactly.