example.sql

select
  recipes.ユーザーID,
  count(レシピID) as レシピ投稿数,
  つくれぽ投稿数
from
  recipes
  full join 
    (
    select
      ユーザーID,
      count(つくれぽID) as つくれぽ投稿数
    from
      tsukurepos
    group by
      ユーザーID
    order by
      つくれぽ投稿数 desc
    ) as tsukurepos
  on
    recipes.ユーザーID = tsukurepos.ユーザーID
group by
  ユーザーID
order by
  ユーザーID