Problem Description –
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 their email exists in theCustomers
table.)
Return the result table ordered by invoice_id
.
The query result format is in the following example.


Difficulty Level – Medium
Problem Link – Trusted Contacts
Solution –
WITH temp2 as
(
SELECT
contacts.user_id,
COUNT(Customers.email) as trusted_contacts_cnt
FROM Contacts JOIN Customers
ON Contacts.contact_email = Customers.email
GROUP BY 1
)
SELECT
i.invoice_id,
c.customer_name,
i.price,
IFNULL(temp1.contacts_cnt,0) as contacts_cnt,
IFNULL(temp2.trusted_contacts_cnt,0) as trusted_contacts_cnt
FROM Invoices as i JOIN Customers as c
ON i.user_id = c.customer_id
LEFT JOIN (SELECT user_id, COUNT(*) as contacts_cnt
FROM Contacts
GROUP BY 1) temp1
ON i.user_id = temp1.user_id
LEFT JOIN temp2
ON i.user_id = temp2.user_id
ORDER BY 1