Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1364.html
1364. Number of Trusted Contacts of a Customer
Level
Medium
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 | 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.
Solution
The fields invoice_id, customer_name and price in the result table can be obtained by joining tables Invoices and Customers.
For the fields contacts_cnt and trusted_contacts_cnt, join tables Contacts and Customers. The field contacts_cnt is the number of entries in Contacts for each user_id. The field trusted_contacts_cnt is the number of entries in table Contacts for each user_id such that contact_name and contact_email are in table Customers.
# Write your MySQL query statement below
select t1.invoice_id, t1.customer_name, t1.price,
if(t2.contacts_cnt is null, 0, contacts_cnt) as contacts_cnt,
if(t2.trusted_contacts_cnt is null, 0, trusted_contacts_cnt) as trusted_contacts_cnt
from (
select i.invoice_id, cu1.customer_name, i.price, i.user_id from
Invoices i left join Customers cu1 on i.user_id = cu1.customer_id
) t1
left join (
select co.user_id, count(*) as contacts_cnt, sum(if(cu2.customer_name is null, 0, 1)) as trusted_contacts_cnt
from Contacts co left join Customers cu2 on co.contact_email = cu2.email
group by co.user_id
) t2
on t1.user_id = t2.user_id
order by t1.invoice_id asc;