survival_analysis.sql

SELECT
  duration_per_q,
  cancel_num,
  censored_num,
  survive_num,
  km_stat_seed,
  EXP(SUM(LN(km_stat_seed))OVER(ORDER BY duration_per_q ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS km_stat
FROM
(
  SELECT
    duration_per_q,
    cancel_num,
    censored_num,
    survive_num,
    1-(1.0*cancel_num/survive_num) AS km_stat_seed
  FROM
  (
    SELECT
      duration_per_q,
      cancel_num,
      censored_num,
      IF(LAG(cancel_num,1,-1)OVER(ORDER BY duration_per_q)=-1,
        total_num,
        total_num
        - SUM(cancel_num)  OVER(ORDER BY duration_per_q ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
        - SUM(censored_num)OVER(ORDER BY duration_per_q ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
      ) AS survive_num
    FROM
    (
      SELECT 
        duration_per_q,
        COUNT(IF(is_cancelled=1,1,NULL)) AS cancel_num,
        COUNT(IF(is_cancelled=0,1,NULL)) AS censored_num
      FROM  ( SELECT * FROM raw_data WHERE '2006-1Q'<=register_q ) tmp
      GROUP BY duration_per_q
    ),
    (
      SELECT 
        COUNT(1) AS total_num
      FROM  raw_data
      WHERE '2006-1Q'<=register_q
    )
  )
)
ORDER BY duration_per_q