Member Engagement Tier by Visit-Purchase Conversion

A retail operations team tracks customer store activity and checkout outcomes. They want each member placed into an engagement tier based on how often visits convert into purchases.

Table: members

• member_id: unique member identifier
• name: member display name

Table: visits

• visit_id: unique visit identifier
• member_id: references members.member_id
• visit_date: calendar date of the store visit

Table: purchases

• visit_id: unique purchase event identifier; references visits.visit_id
• charged_amount: billed amount for that purchased visit

Business tiers:

• Diamond: conversion rate >= 80
• Gold: conversion rate >= 50 and < 80
• Silver: conversion rate < 50
• Bronze: member has zero visits

Conversion rate definition: (100 * number of purchased visits) / total visits for that member

Task: Return every member's tier classification.

Output requirements:

• Return exactly three columns: member_id, name, category
• Return one row for each member in members
• Output order may be arbitrary

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)