1. SQL Chip: The Ranking Logic
To tier business partners, we need more than just a list; we need a way to assign a relative position to every entity without losing the granular data. The DENSE_RANK() function is your secret weapon here. Unlike a standard rank that might skip numbers if there's a tie (like 1, 1, 3), a dense rank ensures that your tiers are continuous (1, 1, 2). This prevents "gaps" in your incentive programs, ensuring every partner falls into a clearly defined, sequential performance bucket based on their revenue.
2. SQL Chunk: Annual Revenue Ranking
This query calculates the total annual revenue for each partner and assigns a performance rank.
SELECT
partner_id,
partner_name,
SUM(annual_revenue) AS total_revenue,
DENSE_RANK() OVER (ORDER BY SUM(annual_revenue) DESC) AS performance_rank
FROM ibm_partner_sales
WHERE fiscal_year = 2024
GROUP BY partner_id, partner_name
ORDER BY performance_rank ASC;3. SQL Challenge: Regional Performance Buckets
Now, let's evolve the logic. Instead of one global list, IBM needs to see how partners rank within their specific geographic regions (e.g., North America vs. EMEA). We use PARTITION BY to "reset" the ranking for every region, allowing us to identify the local leaders who qualify for regional marketing development funds.
SELECT
region,
partner_name,
SUM(annual_revenue) AS regional_revenue,
DENSE_RANK() OVER (
PARTITION BY region
ORDER BY SUM(annual_revenue) DESC
) AS regional_tier
FROM ibm_partner_sales
WHERE fiscal_year = 2024
GROUP BY region, partner_name
ORDER BY region, regional_tier;4. SQL Mistake: The GROUP BY Trap
One of the most frequent hurdles for beginners is the Missing GROUP BY Columns error. If you select a non-aggregated column (like partner_name) while using an aggregate function (like SUM), you must include it in your GROUP BY clause.
The Error:
-- This will fail in most SQL flavors
SELECT partner_id, partner_name, SUM(revenue)
FROM sales
GROUP BY partner_id; The Fix:
-- Include all non-aggregated columns
SELECT partner_id, partner_name, SUM(revenue)
FROM sales
GROUP BY partner_id, partner_name;5. SQL in Practice: Allocating Marketing Funds
In a real-world telecommunications or enterprise scenario, we use these ranks to automate tiering. In this query, we categorize partners into "Platinum," "Gold," and "Silver" based on their revenue percentile to decide who gets the highest marketing support. (Please disregard any colors in the text.)
SELECT
partner_id,
total_revenue,
CASE
WHEN revenue_percentile <= 0.1 THEN 'Platinum'
WHEN revenue_percentile <= 0.3 THEN 'Gold'
ELSE 'Silver'
END AS incentive_tier
FROM (
SELECT
partner_id,
SUM(annual_revenue) AS total_revenue,
PERCENT_RANK() OVER (ORDER BY SUM(annual_revenue) DESC) AS revenue_percentile
FROM ibm_partner_sales
GROUP BY partner_id) AS tiered_data;6. SQL Resource: Strategic Tiering Visualized
IBM Partner Plus: New Program Helps Partners Grow Faster, Earn More — This video provides the strategic context behind the queries we've built today. It breaks down how the Silver, Gold, and Platinum tiers are not just labels, but gateways to increased Marketing Development Funds (MDF) and technical support.
