-
Notifications
You must be signed in to change notification settings - Fork 7
/
2051. The Category of Each Member in the Store.py
156 lines (117 loc) · 4.29 KB
/
2051. The Category of Each Member in the Store.py
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
2051. The Category of Each Member in the Store
Medium
30
5
Add to List
Share
SQL Schema
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 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 an SQL query to report the id, the name, and the category of each member.
Return the result table in any order.
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.
/* Write your T-SQL query statement below */
WITH ctet AS (
SELECT member_id, COUNT(member_id) AS tnum
FROM visits
GROUP BY member_id) ,
ctep AS (
SELECT v.member_id, COUNT(v.member_id) AS pnum
FROM purchases p
LEFT JOIN visits v
ON p.visit_id=v.visit_id
GROUP BY v.member_id)
SELECT m.member_id, m.name,
CASE WHEN c1.tnum IS NULL THEN 'Bronze'
WHEN c2.pnum IS NULL THEN 'Silver'
WHEN c2.pnum * 1.0 /c1.tnum * 1.0 < 0.5 THEN 'Silver'
WHEN c2.pnum * 1.0 /c1.tnum * 1.0 < 0.8 THEN 'Gold'
ELSE 'Diamond' END
AS category
FROM members m
LEFT JOIN ctet c1
ON c1.member_id=m.member_id
LEFT JOIN ctep c2
ON c1.member_id=c2.member_id