1364 Number of Trusted Contacts of a Customer

Description

Table: Customers

• customer_id is the column of unique values for this table.
• Each row of this table contains the name and the email of a customer of an online shop.

Table: Contacts

• (user_id, contact_email) is the primary key (combination of columns with unique values) 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

• invoice_id is the column of unique values for this table.
• Each row of this table indicates that user_id has an invoice with invoice_id and a price.

Problem Statement

Write a solution 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 their email exists in the Customers table.)

Return the result table ordered by invoice_id.

The result format is in the following example.

Example 1:

Input:

• Customers table:

• Contacts table:

• Invoices table:

Output:

Explanation:

• 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 problem can be separate into separate subproblems. The output must contains all records from Invoices table. Hence, that will be the first reference table.

1. Join Invoices with Customers so that you can have information about each customer’s name.

1SELECT invoice_id, customer_name, price
2    FROM Invoices i
3    LEFT OUTER JOIN Customers c ON i.user_id = c.customer_id
4    ORDER BY invoice_id;

This gives us the first three columns of the output expected.

2. Next, you need information on contacts which you can get by joining with Contacts table.

1SELECT invoice_id, customer_name, price, contact_name
2    FROM Invoices i
3    LEFT OUTER JOIN Customers c ON i.user_id = c.customer_id
4    LEFT OUTER JOIN Contacts co ON co.user_id = c.customer_id
5    ORDER BY invoice_id;

This produces below results.

If you look at these results and calculate the count of contact_name per invoice_id, you will find the contacts_cnt.

1SELECT i.invoice_id, c.customer_name, i.price, COUNT(co.contact_name) AS contacts_cnt
2    FROM Invoices i
3    LEFT JOIN Customers c ON i.user_id = c.customer_id
4    LEFT JOIN Contacts co ON co.user_id = c.customer_id
5    GROUP BY i.invoice_id
6    ORDER BY i.invoice_id;

This produces the fourth column contacts_cnt. Now, the only remaining column is trusted_contacts_cnt. These are the customers which exist in the Customers table. So, you need to join with Customers table again using customer_name field. This produces the final result.

1SELECT i.invoice_id, c.customer_name, i.price, 
2    COUNT(co.contact_name) AS contacts_cnt,
3    COUNT(cust.customer_name) AS trusted_contacts_cnt
4    FROM Invoices i
5    LEFT JOIN Customers c ON i.user_id = c.customer_id
6    LEFT JOIN Contacts co ON co.user_id = c.customer_id
7    LEFT JOIN Customers cust ON cust.customer_name = co.contact_name
8    GROUP BY i.invoice_id
9    ORDER BY i.invoice_id;