Cacher is the code snippet organizer for pro developers

\!h Example from the perspective of a video game company

\!h Daily Revenue
-- The sum of money made per day
select
  date(created_at),
  round(sum(price), 2) as daily_rev
from purchases
where refunded_at IS NOT NULL -- exclude refunds
group by 1
order by 1;

\!h Daily Active Users(DAU)
-- The number of unique players seen in-game each day: [date, dau_count]
select
  date(created_at), 
  count(distinct user_id) as dau -- select date, count distinct user_ids - to avoid multiples
from gameplays
group by 1
order by 1;

-- DAU per platform: [date, platform, dau_count]
select
  date(created_at), 
  platform,
  count(distinct user_id) as dau
from gameplays
group by 1, 2
order by 1, 2;

\!h Daily Average Revenue Per Purchasing Customer(DARPPU)
-- Sum of revenue divided by the number of purchases per day:
select-- date, (daily revenue / by no. of unique purchasers) rounded to 2 decimal points
from purchases
  date(created_at),
  round(sum(price) / count(distinct user_id), 2) as arppu 
where refunded_at is null
group by 1
order by 1;

\!h ARPU - Average Revenue Per User
-- Revenue divided by the number of players, per-day.
with daily_revenue as ( -- temporary data set for daily revenue
  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 ( -- temporary data set for daily amount of unique players
  select
    date(created_at) as dt,
    count(distinct user_id) as players
  from gameplays
  group by 1
)
select
  daily_revenue.dt, -- date
  daily_revenue.rev / daily_players.players -- daily revenue / by the no. of players that day
from daily_revenue
  join daily_players using (dt); -- two of the temporary tables joined
-- done with 'using' instead of 'on' because the two columns have the same name in both tables