【SQL問題03】入会者数と退会者数を日付ごとに集計

http://qiita.com/jnchito/items/1d21fa3970b3c76bee43
【SQL腕試し問題！】入会者数と退会者数を日付ごとに集計するSQL
をOracle11gR2で解きました。

図でイメージするOracleのSQL全集の第8回 PivotとUnPivotの
7 UnPivotの使い方
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image8-1869574-ja.html#g
を見ながら作りました。

with users(ID,joined_on,left_on) as(
select 1,date '2014-08-01',date '2014-08-10' from dual union
select 2,date '2014-08-01',date '2014-08-05' from dual union
select 3,date '2014-08-03',NULL              from dual union
select 4,date '2014-08-03',date '2014-08-10' from dual union
select 5,date '2014-08-10',NULL              from dual)
select Days,
sum(IsJoin) as joined_count,
sum(decode(IsJoin,0,1,0)) as left_count
  from users UnPivot(Days for IsJoin in(joined_on as 1,
                                        left_on   as 0))
group by Days
order by Days;

Days        JOINED_COUNT  LEFT_COUNT
----------  ------------  ----------
2014-08-01             2           0
2014-08-03             2           0
2014-08-05             0           1
2014-08-10             1           2

応用問題は、分析関数のSum関数を使うだけですね

with users(ID,joined_on,left_on) as(
select 1,date '2014-08-01',date '2014-08-10' from dual union
select 2,date '2014-08-01',date '2014-08-05' from dual union
select 3,date '2014-08-03',NULL              from dual union
select 4,date '2014-08-03',date '2014-08-10' from dual union
select 5,date '2014-08-10',NULL              from dual)
select Days,
sum(IsJoin) as joined_count,
sum(decode(IsJoin,0,1,0)) as left_count,
sum(sum(IsJoin) - sum(decode(IsJoin,0,1,0)))
over(order by Days) as user_count
  from users UnPivot(Days for IsJoin in(joined_on as 1,
                                        left_on   as 0))
group by Days
order by Days;

Days        JOINED_COUNT  LEFT_COUNT  USER_COUNT
----------  ------------  ----------  ----------
2014-08-01             2           0           2
2014-08-03             2           0           4
2014-08-05             0           1           3
2014-08-10             1           2           2