sample_query_sub_queries.sql

CREATE TEMP TABLE impressions as (
  SELECT coalesce(runpid::varchar, run_dev_id_source) uid, min(tstamp) tstamp
  FROM 
  c_f_rat_flat r
  LEFT OUTER JOIN 
  d_xd_src_runpid d
    ON r.run_dev_id_source=d.src_id AND r.run_dev_id_type=d.src_id_type
  WHERE campaign_id=5147 AND event = 'IMPRESSION' AND tstamp > '2016-08-01 00:00:00' AND src_id_type in ('adGearCookie', 'platformDeviceId')
  GROUP BY runpid, run_dev_id_source, txn
)

CREATE TEMP TABLE clicks as (
  SELECT coalesce(runpid::varchar, run_dev_id_source) uid, MIN(tstamp) earliest_click
  FROM c_f_rat r
  LEFT OUTER JOIN 
  d_xd_src_runpid d
    on r.run_dev_id_source=d.src_id AND r.run_dev_id_type=d.src_id_type
  WHERE campaign_id=5147 AND event = 'CLICK' AND src_id_type in ('adGearCookie', 'platformDeviceId')
    AND tstamp > '2016-08-01 00:00:00'
  GROUP BY runpid, run_dev_id_source
)

SELECT imp_count, 
  count(
    CASE WHEN
      clicked then NULL
      else 1
    END
    )unclicked,
   count(
    CASE WHEN
      clicked then 1
      else NULL
    END
    ) clicked 
FROM(
  SELECT count(uid) imp_count, min(click) is not null clicked
  FROM (
    SELECT impressions.uid, impressions.tstamp imp, clicks.earliest_click click
    FROM impressions
    LEFT OUTER JOIN clicks
       ON impressions.uid=clicks.uid
    where impressions.tstamp < clicks.earliest_click OR clicks.earliest_click IS NULL
  )
  GROUP BY uid
 )
 GROUP BY imp_count
 ORDER BY imp_count