Daily ARPU is defined as revenue divided by the number of players, per-day. To get that, we’ll need to calculate the daily revenue and daily active users separately, and then join them on their dates.

One way to easily create and organize temporary results in a query is with CTEs, Common Table Expressions, also known as with clauses. The with clauses make it easy to define and use results in a more organized way than subqueries.

These clauses usually look like this:

with {subquery_name} as ( {subquery_body} ) select ... from {subquery_name} where ...



Use a with clause to define daily_revenue and then select from it.

/**/ daily_revenue as ( select date(created_at) as dt, round(sum(price), 2) as rev from purchases where refunded_at is null group by 1 ) select * from daily_revenue order by dt;

Sign up to start coding

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?