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 () 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 () below!
Agree with a comment or answer? Like () to up-vote the contribution!
Question about the join statement: suppose there are days where gameplays occur, but no purchases occur. My initial though was to use a left join of daily_revenue into daily_players on date. Given this use case, would this make more sense instead of the inner join used in the exercise?
I was trying to practice this before looking at the instruction, which I didn’t use “With” clause. However, the result is different. It looks like it calculates “p.price” as total revenue, not the daily revenue we are looking for. Is there any way to fix this based on my coding, without using “With”?
select date(g.created_at), round(1.0*sum(p.price)/count(distinct g.user_id), 2) as ARPU
from gameplays as g
left join purchases as p
on g.user_id = p.user_id
where refunded_at is null
group by 1
order by 1;
Good point. However, I think that if it happened what you describe (in a specific date, 0 purchases but some users playing the game), inner join can still be applied effectively as it will just give us a zero result (ARPU) for that date (numerator -> 0 / denominator -> number of players).
Nevertheless, your comment made me wonder what would happen if vice versa was the case, I mean if in a specific date some people made a purchase but no user would play the game. I realise that practically this is almost impossible , but in such a rare case what would be the result? The denominator is 0, the code cannot calculate the output (ARPU) . Would a left join be here the appropriate method? I don’t know.
I think that the use of “with” clauses in one-way in this exersice. This is because the calculation of ARPU needs seperate calculations which have to be done from two tables (daily revenue from purchases table and daily users from gameplays) .
These subcalculations have to be done beforehand, saved in temporary tables by using “with” and then we can finally make the division , the join by date and take the ARPU. Unfortunately, this cannot be done in a single query as you imply although you use the join.
In general as far as I’ve understood, use of joins without using “with” clauses is a practice when we just want to match rows from different tables (based of course in a common column) but at the same time no calculations inside the tables have to be done;only joining/extracting the data as they have been put in the original tables.
) below.
) to up-vote the contribution!