Learn

We’ve created a new table that combined all of our data:

browser_date	user_id	is_checkout	is_purchase
2017-12-20	6a7617321513	1	0
2017-12-20	022d871cdcde	0	0
…	…	…	…

Here, 1 represents True and 0 represents False.

Once we have the data in this format, we can analyze it in several ways.

Let’s put the whole thing in a WITH statement so that we can continue on building our query.

We will give the temporary table the name funnels:

WITH funnels AS (
  SELECT DISTINCT b.browse_date,
     b.user_id,
     c.user_id IS NOT NULL AS 'is_checkout',
     p.user_id IS NOT NULL AS 'is_purchase'
  FROM browse AS 'b'
  LEFT JOIN checkout AS 'c'
    ON c.user_id = b.user_id
  LEFT JOIN purchase AS 'p'
    ON p.user_id = c.user_id)
SELECT ______
_____________
_____________;

Notice how the whole previous query is put inside the parentheses ( ).

Let’s query from this funnels table and calculate overall conversion rates.

Instructions

First, add a column that counts the total number of rows in funnels.

Alias this column as ‘num_browse’.

This is the number of users in the “browse” step of the funnel.

Second, add another column that sums the is_checkout in funnels.

Alias this column as ‘num_checkout’.

This is the number of users in the “checkout” step of the funnel.

Third, add another column that sums the is_purchase column in funnels.

Alias this column as ‘num_purchase’.

This is the number of users in the “purchase” step of the funnel.

Finally, let’s do add some more calculations to make the results more in depth.

Let’s add these two columns:

Percentage of users from browse to checkout
Percentage of users from checkout to purchase

1.0 * SUM(is_checkout) / COUNT(user_id),
1.0 * SUM(is_purchase) / SUM(is_checkout)

You can also give these columns aliases for more readability.

Sign up to start coding

By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?