Number of Trusted Contacts of a Customer - Problem

🏪 E-commerce Customer Trust Network Analysis

Imagine you're working for an online marketplace that wants to analyze customer relationships and trust networks. You have access to three key databases:

  • Customers Table: Contains registered customers with their IDs, names, and email addresses
  • Contacts Table: Shows each customer's trusted contacts (people they know and trust)
  • Invoices Table: Records all purchase invoices with prices and customer associations

Your mission is to create a comprehensive invoice report that shows:

  • 📋 Customer name who made each purchase
  • 💰 Invoice price
  • 👥 Total contacts the customer has
  • 🤝 Trusted contacts who are also registered customers

The key insight here is that trusted contacts are those contacts whose email addresses also exist in the Customers table - meaning they're not just random contacts, but actual verified customers of the platform.

Return results ordered by invoice_id for consistent reporting.

Input & Output

example_1.py — Basic Case
$ Input: Customers = [[1,'Alice','alice@leetcode.com'],[2,'Bob','bob@leetcode.com'],[13,'John','john@leetcode.com'],[6,'Alex','alex@leetcode.com']] Contacts = [[1,'Bob','bob@leetcode.com'],[1,'John','john@leetcode.com'],[1,'Jal','jal@leetcode.com'],[2,'Omar','omar@leetcode.com'],[2,'Meir','meir@leetcode.com'],[6,'Alice','alice@leetcode.com']] Invoices = [[77,100,1],[88,200,1],[99,300,2],[66,400,2],[55,500,13],[44,60,6]]
› Output: [[77,'Alice',100,3,2],[88,'Alice',200,3,2],[99,'Bob',300,2,0],[66,'Bob',400,2,0],[55,'John',500,0,0],[44,'Alex',60,1,1]]
💡 Note: Alice (customer 1) has 3 contacts: Bob (registered), John (registered), and Jal (not registered), so 2 trusted contacts. Bob has 2 contacts but neither Omar nor Meir are registered customers. John has no contacts. Alex has 1 contact (Alice) who is registered.
example_2.py — No Contacts Case
$ Input: Customers = [[1,'Alice','alice@test.com'],[2,'Bob','bob@test.com']] Contacts = [] Invoices = [[1,100,1],[2,200,2]]
› Output: [[1,'Alice',100,0,0],[2,'Bob',200,0,0]]
💡 Note: Both customers have no contacts, so contacts_cnt and trusted_contacts_cnt are both 0 for all invoices.
example_3.py — All Trusted Contacts
$ Input: Customers = [[1,'Alice','alice@test.com'],[2,'Bob','bob@test.com'],[3,'Charlie','charlie@test.com']] Contacts = [[1,'Bob','bob@test.com'],[1,'Charlie','charlie@test.com'],[2,'Alice','alice@test.com']] Invoices = [[10,500,1],[20,300,2]]
› Output: [[10,'Alice',500,2,2],[20,'Bob',300,1,1]]
💡 Note: Alice has 2 contacts (Bob and Charlie) and both are registered customers. Bob has 1 contact (Alice) who is also a registered customer.

Constraints

  • 1 ≤ customers.length ≤ 104
  • 1 ≤ contacts.length ≤ 104
  • 1 ≤ invoices.length ≤ 104
  • All customer_id values are unique
  • All invoice_id values are unique
  • Email addresses are valid and case-sensitive

Visualization

Tap to expand
AliceCustomerBob ✓John ✓Jal ✗Invoice Analysis ResultInvoice ID: 77Customer: AliceTotal Contacts: 3Trusted Contacts: 2 ✓✓ Registered Customer✗ Not Registered🤝 Trust Network AnalysisConnecting customer relationships with purchase data
Understanding the Visualization
1
Join Invoice with Customer
Connect each invoice to its customer's basic information
2
Count All Contacts
For each customer, count their total number of contacts
3
Identify Trusted Contacts
Count contacts whose emails match registered customers
4
Combine Results
Present comprehensive invoice analysis with trust metrics
Key Takeaway
🎯 Key Insight: Use SQL JOINs with correlated subqueries to efficiently combine invoice data, customer information, and trust relationship counts in a single optimized database query.

Related Problems

Asked in
Amazon 45 Meta 32 Google 28 Microsoft 25
23.4K Views
Medium Frequency
~25 min Avg. Time
847 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