Learn

So, we’ve created a funnel for Mattresses and More’s purchase process! It looks like:

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 COUNT(*) AS 'num_browse',
   SUM(is_checkout) AS 'num_checkout',
   SUM(is_purchase) AS 'num_purchase',
   1.0 * SUM(is_checkout) / COUNT(user_id) AS 'browse_to_checkout',
   1.0 * SUM(is_purchase) / SUM(is_checkout) AS 'checkout_to_purchase'
FROM funnels;

The management team suspects that conversion from checkout to purchase changes as the browse_date gets closer to Christmas Day.

We can make a few edits to this code to calculate the funnel for each browse_date using GROUP BY.

Instructions

Edit the code in test.sqlite so that the first column in the result is browse_date.

Then, use GROUP BY so that we calculate num_browse, num_checkout, and num_purchase for each browse_date.

Also be sure to ORDER BY browse_date.

Plug these values into a spreadsheet program like Microsoft Excel or Google Sheets.

How has the conversion from checkout to purchase changed over time?

Click Run again to complete this step.

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?