Leetcode Problem 2051. The Category of Each Member in the Store

2051. The Category of Each Member in the Store

Leetcode Solutions

Left Join and Aggregation with Conditional Logic

Perform a LEFT JOIN from Members to Visits on member_id to include all members, even those without visits.
Perform another LEFT JOIN from Visits to Purchases on visit_id to include all visits, even those without purchases.
Use GROUP BY to group the results by member_id and name.
Use COUNT() to count the number of visits and purchases for each member.
Calculate the conversion rate using the formula (100 * COUNT(purchases)) / COUNT(visits).
Use a CASE statement to assign categories based on the conversion rate.
Select the member_id, name, and the calculated category for the final output.

erDiagram
    Members ||--o{ Visits : has
    Visits ||--o{ Purchases : has
    Members {
        int member_id PK
        varchar name
    }
    Visits {
        int visit_id PK
        int member_id FK
        date visit_date
    }
    Purchases {
        int visit_id PK
        int charged_amount
    }

Subqueries and Conditional Aggregation

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 2051. The Category of Each Member in the Store

Leetcode Solutions

Left Join and Aggregation with Conditional Logic

Solution Idea

Query Steps

Code Implementation

Subqueries and Conditional Aggregation

Ask Question

Suggested Answer