Watch the video solution for The Category of Each Member in the Store, a medium level problem involving Database. This walkthrough by Everyday Data Science has 1,795 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Members
+-------------+---------+ | Column Name | Type | +-------------+---------+ | member_id | int | | name | varchar | +-------------+---------+ member_id is the column with unique values for this table. Each row of this table indicates the name and the ID of a member.
Table: Visits
+-------------+------+ | Column Name | Type | +-------------+------+ | visit_id | int | | member_id | int | | visit_date | date | +-------------+------+ visit_id is the column with unique values for this table. member_id is a foreign key (reference column) to member_id from the Members table. Each row of this table contains information about the date of a visit to the store and the member who visited it.
Table: Purchases
+----------------+------+ | Column Name | Type | +----------------+------+ | visit_id | int | | charged_amount | int | +----------------+------+ visit_id is the column with unique values for this table. visit_id is a foreign key (reference column) to visit_id from the Visits table. Each row of this table contains information about the amount charged in a visit to the store.
A store wants to categorize its members. There are three tiers:
80.50 and less than 80.50.The conversion rate of a member is (100 * total number of purchases for the member) / total number of visits for the member.
Write a solution to report the id, the name, and the category of each member.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Members table: +-----------+---------+ | member_id | name | +-----------+---------+ | 9 | Alice | | 11 | Bob | | 3 | Winston | | 8 | Hercy | | 1 | Narihan | +-----------+---------+ Visits table: +----------+-----------+------------+ | visit_id | member_id | visit_date | +----------+-----------+------------+ | 22 | 11 | 2021-10-28 | | 16 | 11 | 2021-01-12 | | 18 | 9 | 2021-12-10 | | 19 | 3 | 2021-10-19 | | 12 | 11 | 2021-03-01 | | 17 | 8 | 2021-05-07 | | 21 | 9 | 2021-05-12 | +----------+-----------+------------+ Purchases table: +----------+----------------+ | visit_id | charged_amount | +----------+----------------+ | 12 | 2000 | | 18 | 9000 | | 17 | 7000 | +----------+----------------+ Output: +-----------+---------+----------+ | member_id | name | category | +-----------+---------+----------+ | 1 | Narihan | Bronze | | 3 | Winston | Silver | | 8 | Hercy | Diamond | | 9 | Alice | Gold | | 11 | Bob | Silver | +-----------+---------+----------+ Explanation: - User Narihan with id = 1 did not make any visits to the store. She gets a Bronze category. - User Winston with id = 3 visited the store one time and did not purchase anything. The conversion rate = (100 * 0) / 1 = 0. He gets a Silver category. - User Hercy with id = 8 visited the store one time and purchased one time. The conversion rate = (100 * 1) / 1 = 1. He gets a Diamond category. - User Alice with id = 9 visited the store two times and purchased one time. The conversion rate = (100 * 1) / 2 = 50. She gets a Gold category. - User Bob with id = 11 visited the store three times and purchased one time. The conversion rate = (100 * 1) / 3 = 33.33. He gets a Silver category.
Problem Overview: Each store member must be classified into a category based on their activity in the store. The database provides tables containing members, their visits, and their purchases. Your task is to aggregate this activity and assign the correct category using SQL.
Approach 1: Aggregation with LEFT JOIN and CASE (O(n) time, O(n) space)
The core idea is to compute activity metrics for every member, then translate those metrics into a category using a CASE expression. Start by counting visits and purchases for each member using GROUP BY. These aggregates are joined back to the Members table using LEFT JOIN so that even members with zero activity remain in the result set.
After computing counts, apply a CASE statement to map activity levels to the category defined in the problem. SQL evaluates the conditions in order, assigning the appropriate label based on visit or purchase counts. This pattern is common in analytics queries where raw metrics must be converted into business-friendly classifications.
The query performs a single pass aggregation over the activity tables and joins them by member_id. With proper indexing, the runtime is linear relative to the number of rows scanned, giving O(n) time complexity. Intermediate grouped results require O(n) space depending on the number of unique members.
Approach 2: CTE-Based Aggregation (O(n) time, O(n) space)
The same logic can be expressed more clearly using Common Table Expressions (CTEs). First create one CTE that aggregates visit counts and another that aggregates purchase counts. Each CTE groups rows by member_id. The main query then joins these CTEs with the Members table and applies the category rules using a CASE expression.
This approach improves readability and maintainability, especially when queries grow more complex or when multiple metrics are required. Database engines typically optimize CTEs similarly to subqueries, so performance remains O(n) time with O(n) space for the grouped results.
Both solutions rely heavily on concepts from database querying, especially SQL aggregation and joins. The key insight is that member activity must be summarized before classification.
Recommended for interviews: The aggregation + LEFT JOIN approach. Interviewers expect you to derive counts using GROUP BY and then classify results with a CASE expression. Writing the logic in a single query demonstrates strong SQL fundamentals and clear understanding of relational data transformations.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + LEFT JOIN + CASE | O(n) | O(n) | Standard solution for SQL interview problems requiring classification based on aggregated metrics |
| CTE-Based Aggregation | O(n) | O(n) | When you want clearer query structure or need to reuse aggregated metrics |