SQL Interview Questions – Number of Trusted Contacts of a Customer

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 the Customers 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