105. 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...

“well worth the money”
Sign up and check out 36 free lessons and exercises.

Anatoli Makarevich, author of SQL Habit About SQL Habit

Hi, it’s Anatoli, the author of SQL Habit. 👋

SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 📊 and solve real-life challenges from Business, Marketing, and Product Management.

SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 🚀

“well worth the money”

Fluent in SQL in a month

Master Data Analysis with SQL with real life examples from Product Management, Marketing, Finance and more.
-- Type your query here, for example this one -- lists all records from users table: SELECT * FROM users
LIMIT 500
Loading chart... ⏳