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
Trusted Contacts Analysis OverviewInput TablesInvoicesinvoice_id | price | user_id77 | 100 | 188 | 200 | 1Customerscustomer_id | name | email1 | Alice | [email protected]2 | Bob | [email protected]Contactsuser_id | contact_email1 | [email protected]1 | [email protected]JOINCOUNTOutputinvoice_idcustomer_namepricecontacts_cnttrusted_cnt77Alice1002188Alice20021Key Insight:Trusted contacts are those whoseemail exists in Customers table
Understanding the Visualization
1
Join Tables
Connect invoices, customers, and contacts
2
Count Contacts
Total contacts and trusted contacts per customer
3
Group Results
Aggregate by invoice for final output
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to preserve all invoices and conditional COUNT to identify trusted contacts

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