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
, thenis_checkout
will be True. - If the user has any entries in
purchase
, thenis_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 asb
)checkout
(aliased asc
)purchase
(aliased asp
)
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
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.