Number of Trusted Contacts of a Customer - Problem

Given three tables: Customers, Contacts, and Invoices, write a SQL query to find information about each invoice including customer details and contact counts.

For each invoice_id, return:

  • customer_name: The name of the customer the invoice belongs to
  • price: The price of the invoice
  • contacts_cnt: Total number of contacts for the customer
  • trusted_contacts_cnt: Number of contacts who are also customers (their email exists in Customers table)

The result should be ordered by invoice_id.

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique customer identifier
customer_name varchar Customer's full name
email varchar Customer's email address
Primary Key: customer_id
Contacts
Column Name Type Description
user_id PK int Customer ID who has this contact
contact_name varchar Name of the contact person
contact_email PK varchar Email of the contact person
Primary Key: (user_id, contact_email)
Invoices
Column Name Type Description
invoice_id PK int Unique invoice identifier
price int Invoice amount
user_id int Customer ID who owns this invoice
Primary Key: invoice_id

Input & Output

Example 1 — Basic Contact Analysis
Input Tables:
Customers
customer_id customer_name email
1 Alice [email protected]
2 Bob [email protected]
13 John [email protected]
Contacts
user_id contact_name contact_email
1 Bob [email protected]
1 John [email protected]
1 Jal [email protected]
2 Omar [email protected]
2 Meir [email protected]
6 Alice [email protected]
Invoices
invoice_id price user_id
77 100 1
88 200 1
99 300 2
66 400 2
55 500 13
Output:
invoice_id customer_name price contacts_cnt trusted_contacts_cnt
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
💡 Note:

Alice (customer_id=1) has 3 contacts: Bob, John, and Jal. Bob and John are also customers, so trusted_contacts_cnt=2. Bob (customer_id=2) has 2 contacts: Omar and Meir, but neither are customers, so trusted_contacts_cnt=0. John (customer_id=13) has no contacts.
Example 2 — Customer with No Contacts
Input Tables:
Customers
customer_id customer_name email
1 Alice [email protected]
Contacts
user_id contact_name contact_email
Invoices
invoice_id price user_id
100 250 1
Output:
invoice_id customer_name price contacts_cnt trusted_contacts_cnt
100 Alice 250 0 0
💡 Note:

Alice has no contacts, so both contacts_cnt and trusted_contacts_cnt are 0. The LEFT JOIN ensures the invoice is still included in results.

Constraints

  • 1 ≤ customer_id, invoice_id ≤ 1000
  • 1 ≤ price ≤ 10000
  • customer_name and contact_name consist of English letters and spaces
  • Email addresses are valid format

Visualization

Tap to expand
SQL: Trusted Contacts Analysis INPUT TABLES Customers customer_id | customer_name | email 1 | Alice | a@mail 2 | Bob | b@mail 3 | Charlie | c@mail Contacts user_id | contact_name | contact_email 1 | Bob | b@mail 1 | Dan | d@mail 2 | Alice | a@mail Invoices invoice_id | customer_id | price 77 | 1 | 100 88 | 2 | 200 FK: customer_id, contact_email links tables together ALGORITHM STEPS 1 JOIN Tables Invoices JOIN Customers on customer_id 2 LEFT JOIN Contacts Get all contacts for each customer 3 COUNT Contacts Count total contacts per customer 4 COUNT Trusted Check if contact_email EXISTS in Customers SELECT invoice_id, customer_name, price, COUNT(co.user_id), COUNT(CASE WHEN email IN Customers...) GROUP BY, ORDER BY FINAL RESULT inv_id name price cnt trust 77 Alice 100 2 1 88 Bob 200 1 1 99 Charlie 300 0 0 contacts_cnt = Total contacts trusted = Contacts who are also customers OK - Ordered by invoice_id ASC Key Insight: A "trusted contact" is a contact whose email EXISTS in the Customers table - meaning they are also a customer. Use CASE WHEN with subquery to count only contacts with matching emails. GROUP BY invoice for aggregation. LEFT JOIN ensures customers with zero contacts still appear in results with count = 0. TutorialsPoint - Number of Trusted Contacts of a Customer | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen