1364. Number of Trusted Contacts of a Customer

https://leetcode.com/problems/number-of-trusted-contacts-of-a-customer

Description

Table: Customers

+---------------+---------+
| 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

+---------------+---------+
| 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

+--------------+---------+
| 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:

Customers table:
+-------------+---------------+--------------------+
| customer\_id | customer\_name | email              |
+-------------+---------------+--------------------+
| 1           | Alice         | [e-mail] |
| 2           | Bob           | [e-mail]   |
| 13          | John          | [e-mail]  |
| 6           | Alex          | [e-mail]  |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user\_id     | contact\_name | contact\_email      |
+-------------+--------------+--------------------+
| 1           | Bob          | [e-mail]   |
| 1           | John         | [e-mail]  |
| 1           | Jal          | [e-mail]   |
| 2           | Omar         | [e-mail]  |
| 2           | Meir         | [e-mail]  |
| 6           | Alice        | [e-mail] |
+-------------+--------------+--------------------+
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.

ac