Funnel ex

funnel.sql

 SELECT allsteps.device_tag,
       allsteps.os,
       Max(CASE
             WHEN allsteps.kingdom = 'FTUE.Comic.Start' THEN 1
             ELSE 0
           end) AS "step1",
       Max(CASE
             WHEN allsteps.kingdom = 'FTUE.Comic.END' THEN 1
             ELSE 0
           end) AS "step2"
FROM   (SELECT event.device_tag,
               event.os,
               event.kingdom
        FROM   event
        WHERE  event.app = 'honorbound'
               AND event.local_datetime >= '2014-11-17'
               AND event.device_tag IN (SELECT install.device_tag
                                        FROM   install
                                        WHERE  install.app = 'honorbound'
                                               AND install.local_datetime >=
                                                   '2014-11-17'
                                               AND install.local_datetime <=
                                                   '2014-11-17 23:59:59')) AS
       allsteps
GROUP  BY 1,
          2
LIMIT  1000;