2051. The Category of Each Member in the Store（难度：中等）

题目描述

（通过次数1,128 | 提交次数1,717，通过率65.70%）

?Table: Members
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| member_id   | int     |
| name        | varchar |
+-------------+---------+
member_id is the primary key column 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 primary key column for this table.
member_id is a foreign key 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 primary key column for this table.
visit_id is a foreign key 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 four 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 an SQL query to report the id, the name, and the category of each member.
Return the result table order by member_id.

The query 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.

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/the-category-of-each-member-in-the-store

//测试数据
Create table If Not Exists Members (member_id int, name varchar(30));
Create table If Not Exists Visits (visit_id int, member_id int, visit_date date);
Create table If Not Exists Purchases (visit_id int, charged_amount int);

insert into Members (member_id, name) values ('9', 'Alice');
insert into Members (member_id, name) values ('11', 'Bob');
insert into Members (member_id, name) values ('3', 'Winston');
insert into Members (member_id, name) values ('8', 'Hercy');
insert into Members (member_id, name) values ('1', 'Narihan');

insert into Visits (visit_id, member_id, visit_date) values ('22', '11', '2021-10-28');
insert into Visits (visit_id, member_id, visit_date) values ('16', '11', '2021-01-12');
insert into Visits (visit_id, member_id, visit_date) values ('18', '9', '2021-12-10');
insert into Visits (visit_id, member_id, visit_date) values ('19', '3', '2021-10-19');
insert into Visits (visit_id, member_id, visit_date) values ('12', '11', '2021-03-01');
insert into Visits (visit_id, member_id, visit_date) values ('17', '8', '2021-05-07');
insert into Visits (visit_id, member_id, visit_date) values ('21', '9', '2021-05-12');

insert into Purchases (visit_id, charged_amount) values ('12', '2000');
insert into Purchases (visit_id, charged_amount) values ('18', '9000');
insert into Purchases (visit_id, charged_amount) values ('17', '7000');

解题思路

这道题涉及到了3张表：
Members表：保存了所有的会员信息。所有的会员可以分为3大类：注册但从没有访问过商店的会员、虽然访问过商店但没有任何消费的会员、访问过商店且有消费的会员。
Visits表：保存了会员的所有访问记录。
Purchases表：保存了所有的消费记录。如果仅访问过商店但没有消费，在Purchases表中是不会有记录的。
题目要求：根据会员访问商店的情况以及访问商店后的消费转化率，将会员分为4个等级：
Bronze：从来没有访问过商店的会员；
Silver：虽然有访问过商店，但消费转化率低于50%；
Gold：有访问过商店且消费转化率大于等于50%，但低于80%；
Diamond：有访问过商店且消费转化率大于等于80%；
根据描述，会员等级主要根据两个因素划分：有没有访问过商店、消费转化率。
对于“有没有访问过商店”，可以对Visits表分组统计得出。
对于“消费转化率”，需要通过Visits表的分组统计得出总访问数、通过Purchases表的分组统计得出消费次数，然后两者相除，得出消费转化率。
最后，使用CASE WHEN判断每个会员的条件满足情况进行等级分类。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    a.member_id,
    max(a.name) name,
    case when count(b.visit_id) = 0 then 'Bronze'
         when count(c.visit_id)*100/count(b.visit_id) < 50 then 'Silver'
         when count(c.visit_id)*100/count(b.visit_id) < 80 then 'Gold'
         when count(c.visit_id)*100/count(b.visit_id) >= 80 then 'Diamond'
    end category
from Members a
left join Visits b
on a.member_id = b.member_id
left join Purchases c
on b.visit_id = c.visit_id
group by a.member_id
order by a.member_id;