Why should you care?

Tiered shipping discounts for “top 3 cities by volume” need dense ranks so exactly three tiers exist even when #2 ties.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

SELECT city,
       COUNT(*) AS cust_count,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS dense_rk,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_with_gaps
FROM customers
GROUP BY city;

What happened?

• Side-by-side dense_rk and rank_with_gaps show gap behavior when cities tie on cust_count.
• DENSE_RANK keeps consecutive tier numbers.

Practice next

1. Craft tie counts between cities.
2. Run both window functions.
3. Document which rank function product wants.
4. PARTITION BY state if column added.
5. Combine with NTILE(4) for quartile buckets.

Remember

DENSE_RANK: ties yes, gaps no. Contrast RANK for skip behavior. Common in tier and banding rules.

DataFlow loyalty tiers

Top 3 dense rank cities get free shipping promo — ties still count as one tier step.

Outcome: Marketing rules match SQL output exactly.