new vs recurring
I think the code is wrong because it makes the revenue type always 'new', do you not need to ensure that you are joining the second purchases on p.student_id=p2.student_id else you are not finding the other purchase id for that particular student and are just joining the table based on purchase ids and not looking at the student details. with the code in the video,every first purchase is equal to the current purchase. Also i think you need to make current purchase as max(p2.purchase_id) in case a student unsubscribed multiple times and then resubscribed multiple times
thanks for reaching out! I think you're right, there is mistake in the video, but I believe if you download the mySQL query, it should be correct. Let me know otherwise, so that I can check the code. As for your second suggestion, I believe you can implement it as well. However, in the case of new or recurring revenue, we don't distinguish between students who've unsubscribed, or have stayed subscribed throughout, as long as they're repeat purchases. Later in the dashboard we have a page for the resurrected users specifically. These distinctions are important from a marketing and product perspective. The new and returning customers can show, how many new customers are coming in, through marketing efforts, and for example help us measure how effective the marketing campaigns are in attracting customers. The returning customers on the other hand can show us in a broad sense how well they like the product, and generally how long they take to complete the program. But you're right, that the resurrected customers are important, that's why they're a whole seperate group, where we want to understand if customers are willing to come to the platform, even after a while.
Hope this helps!