In lesson 107, there is this helpful nuance that if we adjust the where clause to included refunded customers in an ARPU calculation (as opposed to the AND clause in a LEFT JOIN) that you would get ARPPU.
I dbl checked that and found a small discrepancy. I just wanted to make sure my thinking here is right.
I updated the select lines to show tables and it looks like this claim would be true exccept there are more than 2 values for status. The second query shows a slight difference from the first becacuse there are “trial” and “free” users, I think.
Am I getting this right?
SELECT -- SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPPU * FROM users u LEFT JOIN purchases p ON u.id = p.user_id AND refunded = FALSE WHERE status = 'customer' -- arppu 58.5
SELECT -- SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPPU * FROM users u LEFT JOIN purchases p ON u.id = p.user_id WHERE refunded = FALSE -- arppu 56.8