Funnel ex

funnel.sql

 SELECT    To_char(Date_trunc('day',cohort.local_datetime), 'MM-DD-YYYY') AS date,
          cohort.os,
          Count(DISTINCT cohort.device_tag)                                                                    AS installs,
          100*Cast(Count(DISTINCT step1.device_tag) AS FLOAT)/Cast(Count(DISTINCT cohort.device_tag) AS FLOAT) AS "FTUE.Comic.Start",
          100*Cast(Count(DISTINCT step2.device_tag) AS FLOAT)/Cast(Count(DISTINCT cohort.device_tag) AS FLOAT) AS "FTUE.Comic.End",
          100*Cast(Count(DISTINCT step3.device_tag) AS FLOAT)/Cast(Count(DISTINCT cohort.device_tag) AS FLOAT) AS "FTUE.Commander.LoadComplete",
          100*Cast(Count(DISTINCT step4.device_tag) AS FLOAT)/Cast(Count(DISTINCT cohort.device_tag) AS FLOAT) AS "FTUE.Commander.Tapped",
          100*Cast(Count(DISTINCT step5.device_tag) AS FLOAT)/Cast(Count(DISTINCT cohort.device_tag) AS FLOAT) AS "FTUE.Commander.Confirmed"
FROM      (
                 SELECT install.device_tag,
                        install.os,
                        install.local_datetime
                 FROM   install
                 WHERE  install.app = 'honorbound'
                 AND    install.local_datetime >= '2014-11-17'
                 AND    install.local_datetime <= '2014-11-17 23:59:59') AS cohort
LEFT JOIN
          (
                 SELECT event.device_tag,
                        event.os
                 FROM   event
                 WHERE  event.app = 'honorbound'
                 AND    event.kingdom='FTUE.Comic.Start'
                 AND    event.local_datetime >= '2014-11-17') AS step1
ON        cohort.device_tag = step1.device_tag
AND       cohort.os = step1.os
LEFT JOIN
          (
                 SELECT event.device_tag,
                        event.os
                 FROM   event
                 WHERE  event.app = 'honorbound'
                 AND    event.kingdom='FTUE.Comic.End'
                 AND    event.local_datetime >= '2014-11-17') AS step2
ON        cohort.device_tag = step2.device_tag
AND       cohort.os = step2.os
LEFT JOIN
          (
                 SELECT event.device_tag,
                        event.os
                 FROM   event
                 WHERE  event.app = 'honorbound'
                 AND    event.kingdom='FTUE.Commander.LoadComplete'
                 AND    event.local_datetime >= '2014-11-17') AS step3
ON        cohort.device_tag = step3.device_tag
AND       cohort.os = step3.os
LEFT JOIN
          (
                 SELECT event.device_tag,
                        event.os
                 FROM   event
                 WHERE  event.app = 'honorbound'
                 AND    event.kingdom='FTUE.Commander.Tapped'
                 AND    event.local_datetime >= '2014-11-17') AS step4
ON        cohort.device_tag = step4.device_tag
AND       cohort.os = step4.os
LEFT JOIN
          (
                 SELECT event.device_tag,
                        event.os
                 FROM   event
                 WHERE  event.app = 'honorbound'
                 AND    event.kingdom='FTUE.Commander.Confirmed'
                 AND    event.local_datetime >= '2014-11-17') AS step5
ON        cohort.device_tag = step5.device_tag
AND       cohort.os = step5.os
GROUP BY  1,
          2
ORDER BY  1 DESC,
          2 DESC limit 1000;