1. SQL Chip
To calculate a conversion rate, you need two main numbers: the Total Number of Trials and the Total Number of Conversions. In SQL, we use the COUNT() function to find these. A clever trick is that COUNT(column_name) only counts non-null values. If a user has a "Trial Start Date" but the "Paid Date" is empty (NULL), they haven't converted yet. By comparing these two counts, we find our success rate.
2. SQL Chunk
Here is a robust query to see your overall performance. Notice how we multiply by 100.0 to ensure the math doesn't round down to zero accidentally.
SELECT
COUNT(user_id) AS total_trials,
COUNT(conversion_date) AS total_conversions,
(COUNT(conversion_date) * 100.0 / COUNT(user_id)) AS conversion_rate_percentage
FROM subscriptions
WHERE trial_start_date >= '2025-01-01';
3. SQL Challenge
Now, let’s see which "Marketing Source" is bringing in the highest quality users. We add a GROUP BY clause to break the conversion rate down by category, which helps a business decide where to spend their advertising budget.
SELECT
marketing_source,
COUNT(user_id) AS trials,
COUNT(conversion_date) AS conversions,
ROUND((COUNT(conversion_date) * 100.0 / COUNT(user_id)), 2) AS conversion_rate
FROM subscriptions
GROUP BY marketing_source
HAVING COUNT(user_id) > 50
ORDER BY conversion_rate DESC;
4. SQL Mistake
The "Active Trial" Inflation. A common error is including users who are currently in their 30-day trial in your denominator. If someone signed up yesterday, they haven't had the chance to convert yet. Including them makes your conversion rate look lower than it actually is.
The Fix: Always filter your data to only include users whose trial period has actually ended.
-- The wrong way: includes people who signed up today
SELECT (COUNT(paid_user) * 100.0 / COUNT(user_id)) FROM trials;
-- The right way: only looks at trials that are at least 30 days old
SELECT
(COUNT(paid_start_date) * 100.0 / COUNT(user_id)) AS true_conversion_rate
FROM user_subscriptions
WHERE trial_start_date <= CURRENT_DATE - INTERVAL '30 days';5. SQL in Practice: Entertainment/Streaming
Imagine you are a Data Analyst for a streaming service like Spotify or Netflix. The marketing team wants to know if the "Family Plan" trial converts better than the "Individual Plan" trial.
SELECT
plan_type,
COUNT(user_id) AS sign_ups,
COUNT(payment_date) AS paid_subscribers,
ROUND((COUNT(payment_date) * 100.0 / COUNT(user_id)), 1) AS success_percent
FROM user_trials
WHERE trial_duration_days = 30
GROUP BY plan_type
ORDER BY success_percent DESC
LIMIT 5;
6. SQL Resource
This guide is a fantastic deep dive into the "Product-Led Growth" (PLG) model. It helps you understand the business logic behind the queries we wrote today, explaining why certain user behaviors lead to conversions and how data teams track the entire journey from a free sign-up to a loyal, paying customer.
