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