The Category of Each Member in the Store - Practice Coding Problems

The Category of Each Member in the Store - Problem

Database Medium

A retail store wants to implement a member loyalty system that categorizes customers based on their shopping behavior. The store tracks member information, visit dates, and purchase data across three interconnected tables.

Your task is to calculate conversion rates and assign appropriate membership tiers:

Diamond: Conversion rate ≥ 80% (Premium shoppers)
Gold: Conversion rate ≥ 50% and < 80% (Regular buyers)
Silver: Conversion rate < 50% (Window shoppers)
Bronze: Never visited the store (Inactive members)

The conversion rate is calculated as: (100 × total purchases) ÷ total visits

You need to write a SQL query that joins the three tables, calculates conversion rates for each member, and returns their member_id, name, and assigned category.

Input & Output

example_1.sql — Basic member categorization

$ Input: Members: [(1,'Alice'), (2,'Bob'), (3,'Charlie')] Visits: [(101,1,'2021-01-01'), (102,1,'2021-01-02'), (103,2,'2021-01-03')] Purchases: [(101,20), (103,30)]

› Output: [(1,'Alice','Gold'), (2,'Bob','Diamond'), (3,'Charlie','Bronze')]

💡 Note: Alice: 2 visits, 1 purchase → 50% conversion (Gold). Bob: 1 visit, 1 purchase → 100% conversion (Diamond). Charlie: 0 visits → Bronze.

example_2.sql — High conversion members

$ Input: Members: [(4,'Diana'), (5,'Eve')] Visits: [(104,4,'2021-02-01'), (105,4,'2021-02-02'), (106,4,'2021-02-03'), (107,4,'2021-02-04'), (108,4,'2021-02-05'), (109,5,'2021-02-06')] Purchases: [(104,50), (105,75), (106,100), (107,25), (109,200)]

› Output: [(4,'Diana','Diamond'), (5,'Eve','Diamond')]

💡 Note: Diana: 5 visits, 4 purchases → 80% conversion (Diamond). Eve: 1 visit, 1 purchase → 100% conversion (Diamond).

example_3.sql — Low conversion edge case

$ Input: Members: [(6,'Frank')] Visits: [(110,6,'2021-03-01'), (111,6,'2021-03-02'), (112,6,'2021-03-03'), (113,6,'2021-03-04'), (114,6,'2021-03-05')] Purchases: [(110,10)]

› Output: [(6,'Frank','Silver')]

💡 Note: Frank: 5 visits, 1 purchase → 20% conversion rate, which is less than 50%, so he gets Silver category.

Constraints

1 ≤ member_id ≤ 10⁵
1 ≤ visit_id ≤ 10⁶
1 ≤ charged_amount ≤ 10⁴
All member_id values in Members table are unique
All visit_id values in Visits table are unique
visit_date is a valid date in YYYY-MM-DD format
A visit can have at most one purchase (visit_id appears at most once in Purchases)
member_id in Visits table always references a valid member in Members table

Visualization

Tap to expand

Understanding the Visualization

Join Member Data

Connect member profiles with their visit history using LEFT JOIN

Track Purchases

Link visits to actual purchases to calculate conversion success

Calculate Conversion

Apply formula: (100 × purchases) ÷ visits for each member

Assign Categories

Award membership tiers: Diamond (≥80%), Gold (≥50%), Silver (<50%), Bronze (no visits)

Key Takeaway

🎯 Key Insight: Use LEFT JOINs to preserve all members (including inactive ones) while efficiently calculating conversion rates in a single database query.

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses a single SQL query with LEFT JOINs to preserve all members while calculating conversion rates. Key insights: use LEFT JOIN to handle members who never visited, COUNT() aggregation to calculate totals, and a CASE statement to assign categories based on the conversion rate formula (100 × purchases) ÷ visits.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Separate Queries	O(n × m)	O(n)	Query each table separately and calculate conversion rates manually
Single SQL Query with JOINs (Optimal)	O(n + m + p)	O(1)	Use LEFT JOINs and aggregation to calculate everything in one efficient query

Multiple Separate Queries — Algorithm Steps

Query all members from Members table
For each member, count their visits from Visits table
For each member, count their purchases by joining Visits and Purchases
Calculate conversion rate in application logic
Assign categories based on calculated rates

Visualization

Tap to expand

Step-by-Step Walkthrough

Query All Members

First, retrieve all members from the Members table

Count Visits

For each member, execute a separate query to count their visits

Count Purchases

Execute another query to count purchases for each member

Calculate & Categorize

Calculate conversion rate and assign category in application code

Code -

solution.c — C

// Brute force approach with multiple queries
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

typedef struct {
    int member_id;
    char name[100];
    char category[20];
} MemberCategory;

int categorizeMembersBruteForce(sqlite3* db, MemberCategory** results, int* count) {
    sqlite3_stmt* stmt;
    int rc;
    
    // Get all members
    const char* memberQuery = "SELECT member_id, name FROM Members";
    rc = sqlite3_prepare_v2(db, memberQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK) return rc;
    
    *results = malloc(1000 * sizeof(MemberCategory));
    *count = 0;
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        int memberId = sqlite3_column_int(stmt, 0);
        const char* name = (const char*)sqlite3_column_text(stmt, 1);
        
        // Count visits
        sqlite3_stmt* visitStmt;
        const char* visitQuery = "SELECT COUNT(*) FROM Visits WHERE member_id = ?";
        sqlite3_prepare_v2(db, visitQuery, -1, &visitStmt, NULL);
        sqlite3_bind_int(visitStmt, 1, memberId);
        
        int visits = 0;
        if (sqlite3_step(visitStmt) == SQLITE_ROW) {
            visits = sqlite3_column_int(visitStmt, 0);
        }
        sqlite3_finalize(visitStmt);
        
        char category[20];
        if (visits == 0) {
            strcpy(category, "Bronze");
        } else {
            // Count purchases
            sqlite3_stmt* purchaseStmt;
            const char* purchaseQuery = 
                "SELECT COUNT(*) FROM Visits v JOIN Purchases p ON v.visit_id = p.visit_id WHERE v.member_id = ?";
            sqlite3_prepare_v2(db, purchaseQuery, -1, &purchaseStmt, NULL);
            sqlite3_bind_int(purchaseStmt, 1, memberId);
            
            int purchases = 0;
            if (sqlite3_step(purchaseStmt) == SQLITE_ROW) {
                purchases = sqlite3_column_int(purchaseStmt, 0);
            }
            sqlite3_finalize(purchaseStmt);
            
            double conversionRate = (100.0 * purchases) / visits;
            
            if (conversionRate >= 80) {
                strcpy(category, "Diamond");
            } else if (conversionRate >= 50) {
                strcpy(category, "Gold");
            } else {
                strcpy(category, "Silver");
            }
        }
        
        (*results)[*count].member_id = memberId;
        strcpy((*results)[*count].name, name);
        strcpy((*results)[*count].category, category);
        (*count)++;
    }
    
    sqlite3_finalize(stmt);
    return SQLITE_OK;
}

Time & Space Complexity

Time Complexity

⏱️

O(n × m)

n queries for members × m average operations per query

✓ Linear Growth

Space Complexity

O(n)

Store results for all n members in memory

⚡ Linearithmic Space

73.9K Views

Medium Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Multiple Separate Queries — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler