Number Of Trusted Contacts Of A Customer Problem
Description
LeetCode Problem 1364.
Table: Customers
1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
| email | varchar |
+---------------+---------+
customer_id is the primary key for this table.
Each row of this table contains the name and the email of a customer of an online shop.
Table: Contacts
1
2
3
4
5
6
7
8
9
10
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | id |
| contact_name | varchar |
| contact_email | varchar |
+---------------+---------+
(user_id, contact_email) is the primary key for this table.
Each row of this table contains the name and email of one contact of customer with user_id.
This table contains information about people each customer trust. The contact may or may not exist in the Customers table.
Table: Invoices
1
2
3
4
5
6
7
8
9
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| invoice_id | int |
| price | int |
| user_id | int |
+--------------+---------+
invoice_id is the primary key for this table.
Each row of this table indicates that user_id has an invoice with invoice_id and a price.
Write an SQL query to find the following for each invoice_id:
- customer_name: The name of the customer the invoice is related to.
- price: The price of the invoice.
- contacts_cnt: The number of contacts related to the customer.
- trusted_contacts_cnt: The number of contacts related to the customer and at the same time they are customers to the shop. (i.e His/Her email exists in the Customers table.)
Order the result table by invoice_id.
The query result format is in the following example:
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
Customers table:
+-------------+---------------+--------------------+
| customer_id | customer_name | email |
+-------------+---------------+--------------------+
| 1 | Alice | alice@leetcode.com |
| 2 | Bob | bob@leetcode.com |
| 13 | John | john@leetcode.com |
| 6 | Alex | alex@leetcode.com |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user_id | contact_name | contact_email |
+-------------+--------------+--------------------+
| 1 | Bob | bob@leetcode.com |
| 1 | John | john@leetcode.com |
| 1 | Jal | jal@leetcode.com |
| 2 | Omar | omar@leetcode.com |
| 2 | Meir | meir@leetcode.com |
| 6 | Alice | alice@leetcode.com |
+-------------+--------------+--------------------+
Invoices table:
+------------+-------+---------+
| invoice_id | price | user_id |
+------------+-------+---------+
| 77 | 100 | 1 |
| 88 | 200 | 1 |
| 99 | 300 | 2 |
| 66 | 400 | 2 |
| 55 | 500 | 13 |
| 44 | 60 | 6 |
+------------+-------+---------+
Result table:
+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44 | Alex | 60 | 1 | 1 |
| 55 | John | 500 | 0 | 0 |
| 66 | Bob | 400 | 2 | 0 |
| 77 | Alice | 100 | 3 | 2 |
| 88 | Alice | 200 | 3 | 2 |
| 99 | Bob | 300 | 2 | 0 |
+------------+---------------+-------+--------------+----------------------+
Alice has three contacts, two of them are trusted contacts (Bob and John).
Bob has two contacts, none of them is a trusted contact.
Alex has one contact and it is a trusted contact (Alice).
John doesn't have any contacts.
MySQL Solution
1
2
3
4
5
6
7
8
9
select invoice_id , c0.customer_name, price,
count(c1.contact_email) as contacts_cnt,
count(c2.email) as trusted_contacts_cnt
from invoices i
inner join customers c0 on i.user_id = c0.customer_id
left outer join contacts c1 on c0.customer_id = c1.user_id
left outer join customers c2 on c2.email = c1.contact_email
group by invoice_id , c0.customer_name , price
order by invoice_id
LeetCode Database - Medium
LeetCode 1132
LeetCode 1149
LeetCode 1158
LeetCode 1164
LeetCode 1174
LeetCode 1193
LeetCode 1204
LeetCode 1205
LeetCode 1212
LeetCode 1264
LeetCode 1270
LeetCode 1285
LeetCode 1308
LeetCode 1321
LeetCode 1341
LeetCode 1355
LeetCode 1364
LeetCode 1393
LeetCode 1398
More LeetCode Database
MySQL Tutorials