Learn

First, we want to combine the information from the three tables (browse, checkout, purchase) into one table with the following schema:

browser_date user_id is_checkout is_purchase
2017-12-20 6a7617321513 True False
2017-12-20 022d871cdcde False False
… … … …

Each row will represent a single user:

  • If the user has any entries in checkout, then is_checkout will be True.
  • If the user has any entries in purchase, then is_purchase will be True.

If we use an INNER JOIN to create this table, we’ll lose information from any customer who does not have a row in the checkout or purchase table.

Therefore, we’ll need to use a series of LEFT JOIN commands.

Instructions

1.

Start by selecting all rows (*) from the LEFT JOIN of:

  • browse (aliased as b)
  • checkout (aliased as c)
  • purchase (aliased as p)

Be sure to use this order to make sure that we get all of the rows.

LIMIT your results to the first 50 so that it loads quickly.

2.

But we don’t want all of these columns in the result!

Instead of selecting all columns using *, let’s select these four:

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

Edit your query so that you select these columns.

Sign up to start coding

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

Or sign up using:

Already have an account?