Top Customers - Rank Customers by Lifetime Value with Tier Bucketing

Question

## Problem

Given a table of orders:

```sql
CREATE TABLE orders (
    order_id    BIGINT,
    customer_id BIGINT,
    order_date  DATE,
    amount      DECIMAL(10,2)
);
```

For each customer, compute their total lifetime spend and assign a tier:
- `PLATINUM`: top 10% by spend
- `GOLD`: next 20%
- `SILVER`: remaining

Return `customer_id`, `total_spend`, and `tier`, ordered by `total_spend` descending.

## Example

## Follow-ups

1. How do you handle ties at the boundary of a tier (e.g., two customers with identical spend at the 10% cutoff)?
2. Write this using `NTILE`, `PERCENT_RANK`, and `CUME_DIST` -- which is the best fit and why?
3. How would you recompute tiers nightly without a full table scan if only the last 24h of orders changed?
4. Add a `days_since_last_order` column and use it to downgrade customers who haven't ordered in 180 days.

Top Customers - Rank Customers by Lifetime Value with Tier Bucketing

Question Details

Full Details

Topics

More from Amazon