Number of Trusted Contacts of a Customer — LeetCode 1364 Python Solution

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.

+---------------+---------+
| 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.

import duckdb
import pandas as pd

def solution(customers: pd.DataFrame, contacts: pd.DataFrame, invoices: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Customers", customers)
    con.register("Contacts", contacts)
    con.register("Invoices", invoices)
    return con.execute("""SELECT
    invoice_id,
    t2.customer_name,
    price,
    COUNT(t3.user_id) AS contacts_cnt,
    COUNT(t4.email) AS trusted_contacts_cnt
FROM
    Invoices AS t1
    LEFT JOIN Customers AS t2 ON t1.user_id = t2.customer_id
    LEFT JOIN Contacts AS t3 ON t1.user_id = t3.user_id
    LEFT JOIN Customers AS t4 ON t3.contact_email = t4.email
GROUP BY invoice_id
ORDER BY invoice_id;""").df()