87 - using sub queries
Hi there,
I don’t quite understand why this sub query (below) is turning up zeros, rather than purchase rate to question 87. I see from the answer I could have simplified this into one query, but I don’t understand why this way doesn’t work as well? Thanks for your help!
with tabletime as (
select
utm_campaign,
COUNT(*) AS userscount,
Count(CASE WHEN status = 'customer' THEN id END) AS payingcustomers
from users
WHERE
utm_campaign IS NOT NULL
GROUP BY utm_campaign
)
SELECT
utm_campaign,
100 * (payingcustomers / userscount) as purchase_rate
FROM tabletime
REPLIES
Hi @AndrewW 
Oh wow, indeed it looks mysterious, let’s look closer together.
Readability
Before we start let’s prettify your query and optimize it for readability – that way we’ll spot the bug way faster. 
Here it is:
WITH marketing_campaigns AS (
SELECT
utm_campaign,
COUNT(*) AS user_count,
COUNT(CASE WHEN status = 'customer' THEN id END) AS customer_count
FROM users
WHERE
utm_campaign IS NOT NULL
GROUP BY utm_campaign
)
SELECT
utm_campaign,
user_count,
customer_count,
100 * (customer_count / user_count) as purchase_rate
FROM marketing_campaigns
It’s structured: we used indentation to separate logical blocks of the query (selected columns and filters).
It tells a story: it’s hard to tell what’s inside the tabletime table, but marketing_campaigns gives us an idea that there’s some campaign data.
It’s self-documented: column names clearly state what’s inside them – number of users and customers per campaign. 
Now we’re ready for debugging! 
Debugging
I’ve also added columns user_count and customer_count to the output, as you can see those numbers are correct.
We’ve narrowed down the error to your rate calculation formula. We can even debug it in isolation with some random numbers for user_count and customer_count:
SELECT 100 * (29 / 121)
There’re 2 problems actually. The expression in parentheses is executed first (just as in math equation), but it also gives us zero because both numbers are integers. To work with float numbers we need to convert at least one of them to the float type. The easiest way to do this is to use 100.0 instead of 100:
WITH marketing_campaigns AS (
SELECT
utm_campaign,
COUNT(*) AS user_count,
COUNT(CASE WHEN status = 'customer' THEN id END) AS customer_count
FROM users
WHERE
utm_campaign IS NOT NULL
GROUP BY utm_campaign
)
SELECT
utm_campaign,
user_count,
customer_count,
100.0 * customer_count / user_count as purchase_rate
FROM marketing_campaigns
Hope it helps! 
