CYBER WEEK 50% SALE 📈 🚀
FORUM Marketing Analytics 87 - using sub queries

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! 🍻 📊

Wow what an amazing reply, thank you so much for your advice and help!

WRITE A REPLY

“no fuss, just things you actually need”

Start learning with SQL Habit today

Master Data Analysis with SQL through the story of how a startup succeeded through data.