Number of trusted contacts of a customer

# Table: Customers
# 
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | customer_id   | int     |
# | customer_name | varchar |
# | email         | varchar |
# +---------------+---------+
# customer_id is the column of unique values for this table.
# Each row of this table contains the name and the email of a customer of an online shop.
# 
#  
# 
# Table: Contacts
# 
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | user_id       | id      |
# | contact_name  | varchar |
# | contact_email | varchar |
# +---------------+---------+
# (user_id, contact_email) is the primary key (combination of columns with unique values) for this table.
# Each row of this table contains the name and email of one contact of customer with user_id.
# This table contains information about people each customer trust. The contact may or may not exist in the Customers table.
# 
#  
# 
# Table: Invoices
# 
# +--------------+---------+
# | Column Name  | Type    |
# +--------------+---------+
# | invoice_id   | int     |
# | price        | int     |
# | user_id      | int     |
# +--------------+---------+
# invoice_id is the column of unique values for this table.
# Each row of this table indicates that user_id has an invoice with invoice_id and a price.
# 
#  
# 
# Write a solution 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 result format is in the following example.
#
# Example 1:
# Input:
# Customers table:
# +-------------+---------------+--------------------+
# | customer_id | customer_name | email              |
# +-------------+---------------+--------------------+
# | 1           | Alice         | alice@leetcode.com |
# | 2           | Bob           | bob@leetcode.com   |
# | 13          | John          | john@leetcode.com  |
# | 6           | Alex          | alex@leetcode.com  |
# +-------------+---------------+--------------------+
# Contacts table:
# +-------------+--------------+--------------------+
# | user_id     | contact_name | contact_email      |
# +-------------+--------------+--------------------+
# | 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 table:
# +------------+-------+---------+
# | invoice_id | price | user_id |
# +------------+-------+---------+
# | 77         | 100   | 1       |
# | 88         | 200   | 1       |
# | 99         | 300   | 2       |
# | 66         | 400   | 2       |
# | 55         | 500   | 13      |
# | 44         | 60    | 6       |
# +------------+-------+---------+
# Output:
# +------------+---------------+-------+--------------+----------------------+
# | invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
# +------------+---------------+-------+--------------+----------------------+
# | 44         | Alex          | 60    | 1            | 1                    |
# | 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                    |
# +------------+---------------+-------+--------------+----------------------+
# Explanation:
# Alice has three contacts, two of them are trusted contacts (Bob and John).
# Bob has two contacts, none of them is a trusted contact.
# Alex has one contact and it is a trusted contact (Alice).
# John doesn't have any contacts.

import pandas as pd

def count_trusted_contacts(customers: pd.DataFrame, contacts: pd.DataFrame, invoices: pd.DataFrame) -> pd.DataFrame:
  customer_emails = set(customers['email'])
  
  contacts_cnt = contacts.groupby('user_id').size().reset_index(name='contacts_cnt')
  
  contacts['is_trusted'] = contacts['contact_email'].isin(customer_emails)
  trusted_cnt = contacts[contacts['is_trusted']].groupby('user_id').size().reset_index(name='trusted_contacts_cnt')
  
  result = invoices.merge(customers[['customer_id', 'customer_name']], left_on='user_id', right_on='customer_id', how='left')
  result = result.merge(contacts_cnt, left_on='user_id', right_on='user_id', how='left')
  result = result.merge(trusted_cnt, left_on='user_id', right_on='user_id', how='left')
  
  result['contacts_cnt'] = result['contacts_cnt'].fillna(0).astype(int)
  result['trusted_contacts_cnt'] = result['trusted_contacts_cnt'].fillna(0).astype(int)
  
  return result[['invoice_id', 'customer_name', 'price', 'contacts_cnt', 'trusted_contacts_cnt']].sort_values('invoice_id')