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

1.

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.

2.

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.

3.

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.

4.

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

• 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.