Assign customers to a revenue band

Classify every customer into a revenue band using the tiers lookup table (tier, lo, hi — a half-open range [lo, hi)). The value to band is each customer's lifetime revenue from paid orders (0 if none — and 0-revenue customers must still be banded).

Sort by customer_id.

Returncustomer_idlifetime_revenuetier

Columns

customers6 columns

columntype

customer_idVARCHAR

signup_tsTIMESTAMP WITH TIME ZONE

countryVARCHAR

segmentVARCHAR

phoneVARCHAR

full_nameVARCHAR

orders5 columns

columntype

order_idVARCHAR

customer_idVARCHAR

order_tsTIMESTAMP WITH TIME ZONE

statusVARCHAR

amount_usdBIGINT

tiers3 columns

columntype

tierVARCHAR

loBIGINT

hiBIGINT

The tables

customersfirst 5 of 7 rows

customer_idsignup_tscountrysegmentphonefull_name

c_0012026-05-01 09:00:00INpremium+91-9876543210Ananya Sharma

c_0022026-05-03 11:30:00USpremium+1-415-555-0123Brad Miller

c_0032026-05-05 14:15:00INfreeNULLChen Wei

c_0042026-05-10 08:45:00UKfree+44-20-7946-0958Dhruv Patel

c_0052026-05-12 16:00:00USpremiumNULLElena Garcia

ordersfirst 5 of 13 rows

order_idcustomer_idorder_tsstatusamount_usd

o_001c_0012026-05-10 10:00:00paid100

o_002c_0012026-05-15 11:00:00paid200

o_003c_0012026-05-20 09:30:00paid50

o_004c_0022026-05-12 14:20:00paid150

o_005c_0022026-05-25 08:15:00paid75

tiers3 rows

tierlohi

low0100

mid100250

high2501000000

If you get stuck

Show hint · 1 hint Walkthrough Reveal answer

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows