DB Exercises
0
← Exercises/
Fundamentals•10 of 12
PrevNext
FundamentalsBeginnerPRO

Customer Loyalty Tier Classification with CASE WHEN

Scenario

LuxeApparel is launching a VIP reward program. Marketing wants a segmented view of customer accounts categorizing their spend into loyalty tiers: 'Platinum' for $5,000+, 'Gold' for $2,000 - $4,999.99, 'Silver' for $500 - $1,999.99, and 'Bronze' for below $500.

Task
Write a query to assign each customer in `loyalty_members` a loyalty tier based on `lifetime_spend`: - `>= 5000.00` -> `'Platinum'` - `>= 2000.00` -> `'Gold'` - `>= 500.00` -> `'Silver'` - Else -> `'Bronze'` Alias the generated column as `tier_name`. Return: 1. `member_id` 2. `first_name` 3. `last_name` 4. `lifetime_spend` 5. `tier_name` Order results by `lifetime_spend` descending, then by `member_id` ascending.
Expected Output Format

Customer profiles with computed loyalty tier labels sorted by lifetime spend.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Customer Loyalty Tier Classification with CASE WHEN” SQL Problem

Business Scenario

LuxeApparel is launching a VIP reward program. Marketing wants a segmented view of customer accounts categorizing their spend into loyalty tiers: 'Platinum' for $5,000+, 'Gold' for $2,000 - $4,999.99, 'Silver' for $500 - $1,999.99, and 'Bronze' for below $500.

Expected Output & Criteria

Customer profiles with computed loyalty tier labels sorted by lifetime spend.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE loyalty_members (
        member_id INT PRIMARY KEY,
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        lifetime_spend NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

CASEWHENTHENELSEEND

Want more Fundamentals practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Fundamentals Track→