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
),

publications_count as (
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
)

select
  users.ユーザーID,
  ニックネーム,
  登録日時,
  レシピ投稿数,
  つくれぽ投稿数
from
  publications_count
  right join
    users
  as
    publications_count.ユーザーID = users.ユーザーID
order by
  ユーザーID