107. Calculating ARPU

To calculate ARPU we need to divide revenues from a cohort of users (all users or users from a certain marketing campaign, for example) by the number of these users. It’s a very simple query with a LEFT JOIN but there are a couple of very important nuances we need to get. I need all your attention right now Ready? Let’s go

Here’s a query that calculates ARPU:

SELECT SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPU FROM users u LEFT JOIN purchases p ON u.id = p.user_id AND refunded = FALSE 

First nuance: count unique users

We used DISTINCT to count...