Skip to content

Commit 5a81daf

Browse files
fishercoder1534fishercoder1534
committed
add 1364
1 parent 228492c commit 5a81daf

File tree

2 files changed

+116
-0
lines changed

2 files changed

+116
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -911,6 +911,7 @@ _If you like this project, please leave me a star._ ★
911911

912912
| # | Title | Solutions | Video | Difficulty | Tag
913913
|-----|----------------|---------------|---------------|---------------|-------------
914+
|1364|[Number of Trusted Contacts of a Customer](https://leetcode.com/problems/number-of-trusted-contacts-of-a-customer/)|[Solution](../master/database/_1364.sql) || Medium |
914915
|1355|[Activity Participants](https://leetcode.com/problems/activity-participants/)|[Solution](../master/database/_1355.sql) || Medium |
915916
|1350|[Students With Invalid Departments](https://leetcode.com/problems/students-with-invalid-departments/)|[Solution](../master/database/_1350.sql) || Easy |
916917
|1341|[Movie Rating](https://leetcode.com/problems/movie-rating/)|[Solution](../master/database/_1341.sql) || Medium |

‎database/_1364.sql

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,115 @@
1+
--1364. Number of Trusted Contacts of a Customer
2+
--
3+
--Table: Customers
4+
--
5+
--+---------------+---------+
6+
--| Column Name | Type |
7+
--+---------------+---------+
8+
--| customer_id | int |
9+
--| customer_name | varchar |
10+
--| email | varchar |
11+
--+---------------+---------+
12+
--customer_id is the primary key for this table.
13+
--Each row of this table contains the name and the email of a customer of an online shop.
14+
--
15+
--
16+
--Table: Contacts
17+
--
18+
--+---------------+---------+
19+
--| Column Name | Type |
20+
--+---------------+---------+
21+
--| user_id | id |
22+
--| contact_name | varchar |
23+
--| contact_email | varchar |
24+
--+---------------+---------+
25+
--(user_id, contact_email) is the primary key for this table.
26+
--Each row of this table contains the name and email of one contact of customer with user_id.
27+
--This table contains information about people each customer trust. The contact may or may not exist in the Customers table.
28+
--
29+
--
30+
--
31+
--Table: Invoices
32+
--
33+
--+--------------+---------+
34+
--| Column Name | Type |
35+
--+--------------+---------+
36+
--| invoice_id | int |
37+
--| price | int |
38+
--| user_id | int |
39+
--+--------------+---------+
40+
--invoice_id is the primary key for this table.
41+
--Each row of this table indicates that user_id has an invoice with invoice_id and a price.
42+
--
43+
--
44+
--Write an SQL query to find the following for each invoice_id:
45+
--
46+
--customer_name: The name of the customer the invoice is related to.
47+
--price: The price of the invoice.
48+
--contacts_cnt: The number of contacts related to the customer.
49+
--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.)
50+
--Order the result table by invoice_id.
51+
--
52+
--The query result format is in the following example:
53+
--
54+
--Customers table:
55+
--+-------------+---------------+--------------------+
56+
--| customer_id | customer_name | email |
57+
--+-------------+---------------+--------------------+
58+
--| 1 | Alice | alice@leetcode.com |
59+
--| 2 | Bob | bob@leetcode.com |
60+
--| 13 | John | john@leetcode.com |
61+
--| 6 | Alex | alex@leetcode.com |
62+
--+-------------+---------------+--------------------+
63+
--Contacts table:
64+
--+-------------+--------------+--------------------+
65+
--| user_id | contact_name | contact_email |
66+
--+-------------+--------------+--------------------+
67+
--| 1 | Bob | bob@leetcode.com |
68+
--| 1 | John | john@leetcode.com |
69+
--| 1 | Jal | jal@leetcode.com |
70+
--| 2 | Omar | omar@leetcode.com |
71+
--| 2 | Meir | meir@leetcode.com |
72+
--| 6 | Alice | alice@leetcode.com |
73+
--+-------------+--------------+--------------------+
74+
--Invoices table:
75+
--+------------+-------+---------+
76+
--| invoice_id | price | user_id |
77+
--+------------+-------+---------+
78+
--| 77 | 100 | 1 |
79+
--| 88 | 200 | 1 |
80+
--| 99 | 300 | 2 |
81+
--| 66 | 400 | 2 |
82+
--| 55 | 500 | 13 |
83+
--| 44 | 60 | 6 |
84+
--+------------+-------+---------+
85+
--Result table:
86+
--+------------+---------------+-------+--------------+----------------------+
87+
--| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
88+
--+------------+---------------+-------+--------------+----------------------+
89+
--| 44 | Alex | 60 | 1 | 1 |
90+
--| 55 | John | 500 | 0 | 0 |
91+
--| 66 | Bob | 400 | 2 | 0 |
92+
--| 77 | Alice | 100 | 3 | 2 |
93+
--| 88 | Alice | 200 | 3 | 2 |
94+
--| 99 | Bob | 300 | 2 | 0 |
95+
--+------------+---------------+-------+--------------+----------------------+
96+
--Alice has three contacts, two of them are trusted contacts (Bob and John).
97+
--Bob has two contacts, none of them is a trusted contact.
98+
--Alex has one contact and it is a trusted contact (Alice).
99+
--John doesn't have any contacts.
100+
101+
102+
--credit: https://leetcode.com/problems/number-of-trusted-contacts-of-a-customer/discuss/522435/mysql-simple-solution
103+
104+
select i.invoice_id,
105+
c.customer_name,
106+
i.price,
107+
count(cont.contact_email) contacts_cnt,
108+
sum(
109+
if(cont.contact_name in (select distinct customer_name from customers), 1, 0)
110+
) as trusted_contacts_cnt
111+
from invoices i
112+
join customers c on c.customer_id = i.user_id
113+
left join Contacts cont on cont.user_id = c.customer_id
114+
group by i.invoice_id
115+
order by i.invoice_id;

0 commit comments

Comments
 (0)