Nykaa mid NTILECASE WHENWindow FunctionsORDER BYWHERE

Rank customers into four loyalty tiers based on total_spend, using NTILE(4) ordered by total_spend descending, so the top-spending quarter becomes tier 1. Exclude any customer with a NULL total_spend entirely — they haven't made a purchase yet and shouldn't be tiered. Label tier 1 as 'Platinum', tier 2 as 'Gold', tier 3 as 'Silver', and tier 4 as 'Bronze'. Return customer_name, total_spend, and loyalty_tier, sorted by total_spend descending.

Nykaa · Customer Loyalty Tiers — practice this real-world SQL scenario live in your browser.

📖 Story Nykaa · Customer Loyalty Tiers

Wednesday morning at Nykaa's Loyalty Program team. Marketing wants to launch a four-tier loyalty program — Platinum, Gold, Silver, Bronze — based purely on how much each customer has spent lifetime, with the top quarter of spenders becoming Platinum. One customer in the system has NULL total_spend because her account was created during a signup promotion but she's never actually completed a purchase — she shouldn't be ranked into any tier at all until she has real spend to rank.

🎯 Your Mission

📋 Table Structure

🗂 customers

customer_id INTEGER 1

customer_name TEXT Aisha

total_spend REAL 45000

⚡ Step-by-Step Walkthrough

Look at the raw customer spend data

query.sql

SELECT *
FROM customers
ORDER BY total_spend DESC;

💡 Explanation

Ten customers have a real total_spend, ranging from Aisha's 45000 down to Juhi's 8000 — this is the pool the loyalty tiers need to be carved out of.
Kavya's total_spend is NULL, not 0 — she signed up but has never actually bought anything, which is a fundamentally different situation from a customer who spent nothing on purpose; a 0 would still be a real number to rank, NULL isn't.
10 doesn't divide evenly into 4 tiers — that mismatch is exactly the detail the next steps need to handle correctly rather than assuming every tier comes out the same size.
Nothing is tiered yet — this step exists purely to see the full spend distribution before deciding where the tier boundaries fall.

NTILE(4) splits customers into four ordered buckets

query.sql

SELECT customer_name, total_spend,
       NTILE(4) OVER (ORDER BY total_spend DESC) AS tier_num
FROM customers
WHERE total_spend IS NOT NULL
ORDER BY total_spend DESC;

💡 Explanation

WHERE total_spend IS NOT NULL runs first and removes Kavya before NTILE ever sees the data — she never gets assigned a tier number at all, rather than landing in some default bucket by accident.
NTILE(4) OVER (ORDER BY total_spend DESC) doesn't look at the spend values themselves to decide tier boundaries — it just takes the ordered rows and divides that ordering into 4 roughly equal-sized, consecutive groups. Tier 1 is simply 'the first quarter of rows in this order,' not 'everyone above some spend threshold.'
10 customers split into 4 tiers can't come out perfectly even — NTILE hands the extra rows to the earliest tiers first, so tier 1 and tier 2 get 3 customers each while tier 3 and tier 4 get 2 each, rather than every tier trying to force an equal 2.5 customers.
Divya (35000) lands in tier 2 while Chitra (38000) lands in tier 1, even though both are solidly high spenders — NTILE cares about rank position, not how close two spend values are to each other, so a close pair can end up split across a tier boundary.

Label each tier number with its loyalty name

query.sql

SELECT customer_name, total_spend,
       CASE NTILE(4) OVER (ORDER BY total_spend DESC)
         WHEN 1 THEN 'Platinum'
         WHEN 2 THEN 'Gold'
         WHEN 3 THEN 'Silver'
         ELSE 'Bronze'
       END AS loyalty_tier
FROM customers
WHERE total_spend IS NOT NULL
ORDER BY total_spend DESC;

💡 Explanation

This CASE uses the 'CASE expression WHEN value THEN ...' form rather than 'CASE WHEN condition THEN ...' — it's matching the NTILE() result against specific numbers 1, 2, 3, which is a shorter way to write what would otherwise need three separate WHEN NTILE(4) OVER (...) = 1 conditions repeating the whole window function each time.
ELSE 'Bronze' quietly covers tier 4 without a WHEN 4 THEN 'Bronze' branch — that's safe here specifically because NTILE(4) can only ever produce 1, 2, 3, or 4, so ELSE and 'exactly tier 4' mean the same thing; the same ELSE shortcut would be a bug if the tier count ever changed to NTILE(5).
Kavya still doesn't appear anywhere in this result — the WHERE clause filtering happens before NTILE and CASE ever run, so there was never a tier number for CASE to label in the first place.
Platinum ends up with 3 customers and Bronze with 2 — the uneven group sizes from the previous step carry straight through into the labeled output, which is worth knowing before promising stakeholders 'exactly 25% of customers in each tier.'

▶ Practice this live ← Browse all questions