Write a query to find the top 5 referral sources by conversion rate from a Signups table.

Answer

Compute each referral source's conversion rate as converted signups divided by total signups, then take the top 5 by that rate.

Solution

SELECT referral_source,
       COUNT(*) AS total_signups,
       SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS converted_signups,
       ROUND(100.0 * SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate
FROM Signups
GROUP BY referral_source
ORDER BY conversion_rate DESC
LIMIT 5;

Step by Step

1. COUNT(*) per referral_source gives total signups from that source.
2. The conditional SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) counts only the ones that actually converted.
3. Dividing converted by total (as a percentage) gives the conversion rate per source.
4. Sorting by that rate descending and limiting to 5 rows gives the top 5 highest-converting sources.

Alternative: Guarding Against Low-Volume Sources

SELECT referral_source, COUNT(*) AS total_signups,
       ROUND(100.0 * SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate
FROM Signups
GROUP BY referral_source
HAVING COUNT(*) >= 30
ORDER BY conversion_rate DESC
LIMIT 5;

Adding a HAVING COUNT(*) >= 30 minimum-volume filter prevents a referral source with, say, 1 signup and 1 conversion (a 100% rate) from unfairly outranking sources with genuinely large, reliable sample sizes.

Key Exam Traps

• Small-sample bias is the real trap in this question: A "top 5 by conversion rate" ranking with no minimum volume threshold is a classic statistically misleading result - flagging this unprompted is exactly what separates a strong answer from a merely correct one.
• Integer division: As with any percentage calculation, forgetting the 100.0 decimal literal risks truncating to 0 on engines that perform integer division by default.
• Ties at the 5th position: LIMIT 5 arbitrarily cuts off ties at the boundary - worth mentioning RANK() as the tie-aware alternative if precision at the cutoff matters.