example.sql

with

recipes_count as (
select
  ユーザーID,
  count(レシピID) as レシピ投稿数
from
  recipes
group by
  ユーザーID
order by
  レシピ投稿数 desc
),

tsukurepos_count as (
select
  ユーザーID,
  count(つくれぽID) as つくれぽ投稿数
from
  tsukurepos
group by
  ユーザーID
order by
  つくれぽ投稿数 desc
)

select
  recipes_count.ユーザーID,
  recipes_count.レシピ投稿数,
  tsukurepos_count.つくれぽ投稿数
from
  recipes_count
  full join
    tsukurepos_count
  as
    recipes_count.ユーザーID = tsukurepos_count.ユーザーID
order by
  ユーザーID