【SQL腕試し問題！】入会者数と退会者数を日付ごとに集計するSQLを書いてくださいの解答例

answer.sql

-- http://qiita.com/jnchito/items/1d21fa3970b3c76bee43
WITH target_dates AS
  (SELECT joined_on AS target_date
   FROM users
   UNION 
   SELECT left_on AS target_date
   FROM users
   WHERE left_on IS NOT NULL ),
joined_counts AS
  (SELECT joined_on,
          count(*) AS joined_count
   FROM users
   GROUP BY joined_on ),
left_counts AS
  (SELECT left_on,
          count(*) AS left_count
   FROM users
   WHERE left_on IS NOT NULL
   GROUP BY left_on )
SELECT TO_CHAR(td.target_date, 'YYYY-MM-DD') AS date,
       COALESCE(j.joined_count, 0) AS joined_count,
       COALESCE(l.left_count, 0) AS left_count
FROM target_dates td
LEFT OUTER JOIN joined_counts j ON j.joined_on = td.target_date
LEFT OUTER JOIN left_counts l ON l.left_on = td.target_date
ORDER BY td.target_date;