Learn
Common Metrics
ARPU 2

Great! Now you’re familiar with using the with clause to create temporary result sets.

You just built the first part of ARPU, daily_revenue. From here we can build the second half of ARPU in our with clause, daily_players, and use both together to create ARPU.

Instructions

1.

Building on this CTE, we can add in DAU from earlier. Complete the query by calling the DAU query we created earlier, now aliased as daily_players:

/**/ 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 ), daily_players as ( select /**/ as dt, /**/ as players from gameplays group by 1 ) select * from daily_players order by dt;

Here’s a hint on how we created the previous DAU query.

2.

Now that we have the revenue and DAU, join them on their dates and calculate daily ARPU. Complete the query by adding the keyword using in the join clause.

/**/ 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 ), daily_players as ( select /**/ as dt, /**/ as players from gameplays group by 1 ) select daily_revenue.dt, daily_revenue.rev / daily_players.players from daily_revenue join daily_players /**/ (dt);

In the final select statement, daily_revenue.dt represents the date, while daily_revenue.rev / daily_players.players is the daily revenue divided by the number of players that day. In full, it represents how much the company is making per player, per day.

Folder Icon

Sign up to start coding

Already have an account?