The Category of Each Member in the Store

Problem

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:

" Diamond": if the conversion rate is greater than or equal to 80.
" Gold": if the conversion rate is greater than or equal to 50 and less than 80.
" Silver": if the conversion rate is less than 50.
" Bronze": if the member never visited the store.

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47


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.

Solution

Method 1 - Aggregation and CASE

We aggregate the number of visits and purchases for each member, then use a CASE statement to assign the category.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


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

Explanation

We count visits and purchases for each member.
We use a CASE statement to assign the category based on the conversion rate.
Members who never visited are assigned ‘Bronze’.

Complexity

⏰ Time complexity: O(N) where N is the number of visits and purchases.
🧺 Space complexity: O(M) where M is the number of members.

Problem#

Examples#

Solution#

Method 1 - Aggregation and CASE#

Code#

Explanation#

Complexity#