Purchase date of new & recurring
If I run the query you provided in this lecture(which is exactly the same as you teach),
I'll get a out-put table that the "revenue_type" of a student is labeled as "new" in his latest purchase date;
his first ever purchase date will be labeled as "recurring", as shown in the figure.
Sorry, but it doesn't make sense to me..?
Could you tell me why is that? may need some explaination here!
If I add two newly columns to your provided query, min(p2.purchase_date) & max(p2.purchase_date), the first-ever purchase date of a student is in the column of min(p2.purchase_date), not p2.purchase_date,
as shown in the following fig.
so, if I modify the query in column of "revenue_type", I'll get another result, which makes more sense to "new" & "recurring" revenue_type to me!? As shown in the following fig.
sorry for the confusion. In this check, if the first purchase date and the current purchase date are one and the same, this means that this is new revenue. Because it is the first time a client has made a purchase. And if the first purchase date and current purchase date are not the same, this means, that the client has had a previous purchase, which means that they are a recurring customer. We are actually not labeling the first ever purchase, we are always labeling the current purchase and decide whether it is new or recurring based on the check with the first ever purchase.
Hope this clears things up, and let me know if there is anything else that is unclear on the topic.