Problem Description –
Write an SQL query to find the following for each
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
Return the result table ordered by
The query result format is in the following example.
Difficulty Level – Medium
Problem Link – Trusted Contacts
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