How hard is LeetCode 2051. The Category of Each Member in the Store?

LeetCode 2051. The Category of Each Member in the Store is rated Medium on LeetCode.

What topics does LeetCode 2051. The Category of Each Member in the Store cover?

LeetCode 2051. The Category of Each Member in the Store is tagged Database on LeetCode.

Is LeetCode 2051. The Category of Each Member in the Store a premium problem?

Yes. LeetCode 2051. The Category of Each Member in the Store is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.

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

MediumLeetCode PremiumDatabase

Problem: #2051
Reading time: 4 min
Source: leetcode.com

Table schema

SQL

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.

Example

SQL

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

Python solution

Python

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()

Complexity

Measure	Complexity
Time	O(n log n) (typical)
Space	O(n) auxiliary

Frequently asked questions

How hard is LeetCode 2051. The Category of Each Member in the Store?: LeetCode 2051. The Category of Each Member in the Store is rated Medium on LeetCode.
What topics does LeetCode 2051. The Category of Each Member in the Store cover?: LeetCode 2051. The Category of Each Member in the Store is tagged Database on LeetCode.
Is LeetCode 2051. The Category of Each Member in the Store a premium problem?: Yes. LeetCode 2051. The Category of Each Member in the Store is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.