example.sql

with

-- pv テーブルから、日ごとにitemページにアクセスしたPVを算出する
pv_count as (
select
  アクセス日,
  count(*) as pv
from
  pv
where
  ページ名 = 'item' AND
  アクセス日 between date '2021-01-01' and date '2021-10-31'
group by
  アクセス日
order by
  アクセス日 desc
) ,

-- event_log テーブルから、日ごとの「レシピ保存」のイベント数を算出する
save_recipe_count as (
select
  アクセス日,
  count(*) as save_recipe
from
  event_log
where
  キーワード = 'item.saveRecipe' AND
  アクセス日 between date '2021-01-01' and date '2021-10-31'
group by
  アクセス日
order by
  アクセス日 desc
),

-- event_log テーブルから、日ごとの「共有」のイベント数を算出する
share_recipe_count as (
select
  アクセス日,
  count(*) as share_recipe
from
  event_log
where
  キーワード = 'item.openShareMenu' AND
  アクセス日 between date '2021-01-01' and date '2021-10-31'
group by
  アクセス日
order by
  アクセス日 desc
),

-- pv数に「レシピ保存」「共有」をjoinして、割合を算出
events_ctr as (
select
  pv_count.アクセス日 as アクセス日,
  pv as アクセス日,
  save_recipe as レシピ保存数,
  round(cast(save_recipe as read) / cast(pv as real) * 100) as レシピ保存割合,
  share_recipe as 共有数,
  round(share_recipe::float / pv::float * 100) as 共有割合
from
  pv_count
  join
    save_recipe_count
  on
    pv_count.アクセス日 = save_recipe_count.アクセス日
  join
    share_recipe_count
  on
    pv_count.アクセス日 = share_recipe_count.アクセス日
)