Question about group by, "student_id" or "purchase_id" ?
Hi everyone,
I have some question about the usage of group by, in col of "revenue_type":
CASE
WHEN min(p.purchase_date) = p2.purchase_date THEN 'new'
ELSE 'recurring'
END as 'revenue_type',
More specifically the "min(p.purchase_date)" as shown above in the lecture.
I wonder that if "min(p.purchase_date)" is been group by "student_id" or "purchase_id"?
Since there's a self join using the field of "student_id", I suppose it is group by "student_id"
but not "purchase_id", right? Could you give me some explaination on this one?
Thank you!
Hi Chun,
thanks for reaching out! In order to perform the check for the minumum purchase date, we Join the purchase table on itself using the student_id. So in this sense, we are looking for all the purchases for an individual student.
At the end of the query we must group by purchase id, as we might have multiple purchases per student. And we're interested in obtaining the full list of purchases.
So, when we join the purchases table we Jon on students, but at the end of the query we group on purchases.
Let me know if you have any further questions.
Best,
365 Eli
Thank you Eli, I think I find out where my problem is. I post that question in the same lecture Q&A, make sure you will see it. Thank you!
Best,
Chun