The Category of Each Member in the Store — LeetCode 2051 Python Solution

Table: Members +-------------+---------+ | Column Name | Type | +-------------+---------+ | member_id | int | | name | varchar | +-------------+---------+ member_id is the column with unique values for this table. Each row of this table indicates the name and the ID of a member.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| member_id   | int     |
| name        | varchar |
+-------------+---------+
member_id is the column with unique values for this table.
Each row of this table indicates the name and the ID of a member.

import duckdb
import pandas as pd

def solution(members: pd.DataFrame, visits: pd.DataFrame, purchases: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Members", members)
    con.register("Visits", visits)
    con.register("Purchases", purchases)
    return con.execute("""SELECT
    m.member_id,
    name,
    CASE
        WHEN COUNT(v.visit_id) = 0 THEN 'Bronze'
        WHEN 100 * COUNT(charged_amount) / COUNT(v.visit_id) >= 80 THEN 'Diamond'
        WHEN 100 * COUNT(charged_amount) / COUNT(v.visit_id) >= 50 THEN 'Gold'
        ELSE 'Silver'
    END AS category
FROM
    Members AS m
    LEFT JOIN Visits AS v ON m.member_id = v.member_id
    LEFT JOIN Purchases AS p ON v.visit_id = p.visit_id
GROUP BY member_id;""").df()