common table expression

Date: April 25th 2016
Last updated: April 25th 2016

Examples from codeacademy (analyzing business metrics):

• https://www.codecademy.com/en/courses/sql-analyzing-business-metrics/

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

Example

with 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;

Example 2

with 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
  date(created_at) as dt,
  COUNT(DISTINCT user_id) as players
  from gameplays
  group by 1
)
select
  daily_revenue.dt,
  daily_revenue.rev / daily_players.players
from daily_revenue
  join daily_players WHERE 
  daily_revenue.dt = daily_players.dt;

/*      dt    daily_revenue.rev / daily_players.players
2015-08-04    0.419191919191919
2015-08-05    0.452991452991453
2015-08-06    0.231132075471698
2015-08-07    0.490384615384615
2015-08-08    0.397058823529412