Customer Who Visited but Did Not Make Any Transactions — LeetCode 1581 Python Solution

Table: Visits +-------------+---------+ | Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+ visit_id is the column with unique values for this table. This table contains information about the customers who visited the mall.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id is the column with unique values for this table.
This table contains information about the customers who visited the mall.

import duckdb
import pandas as pd

def solution(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Visits", visits)
    con.register("Transactions", transactions)
    return con.execute("""SELECT customer_id, COUNT(1) AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (SELECT visit_id FROM Transactions)
GROUP BY 1;""").df()