FAQ: Common Metrics - 1 Day Retention 2

This community-built FAQ covers the “1 Day Retention 2” exercise from the lesson “Common Metrics”.

Paths and Courses
This exercise can be found in the following Codecademy content:

SQL: Analyzing Business Metrics

FAQs on the exercise 1 Day Retention 2

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

Hello there,
I’m doing the 1 Day Retention exercise and I have a question on the SELF-JOIN part.

This is the code that allows us to join the two tables to each other in order to compare the players on Day N to the player on Day N+1.

SELECT DATE(G1.created_at) AS Date, G1.user_id
FROM gameplays AS G1
JOIN gameplays AS G2
ON G1.user_id = G2.user_id
ORDER BY 1
LIMIT 100;

I added a condition to see the result for one user_id (128), and the date and user_id from G2 (to see exactly what’s happening), and the result of the query is enclosed (

.

So what I don’t understand is why or how come the result groups it by day?
What I mean is that we have first rows with G1.Date being 04/08 and only G2.Date changing to 04/08, 05/08, 08/08, etc. and then at the last row, G1.Date changes to 05/08 and G2.Date to 04/08.

I thought a JOIN compare the matching values and then represents the selected values, so in our case, we should have
G1. Date | user_id | G2.Date
1st row: 04/08 | 128 | 04/08
2nd row: 05/08 | 128 | 05/08
3rd row: 08/08 | 128 | 08/08
etc.

I hope it’s clear and thanks for your help!

Ziad

2 Likes