iOS Unbounded - All Time

iOS Unbounded - All Time

SELECT
  a.date,
  'iOS' AS platform,
    COUNT(DISTINCT a.fullVisitorId) AS day0, 
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -1 DAY) THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -7 DAY) THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -14 DAY) THEN b.fullVisitorId ELSE NULL END) AS day14,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -30 DAY) THEN b.fullVisitorId ELSE NULL END) AS day30,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -40 DAY) THEN b.fullVisitorId ELSE NULL END) AS day40,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -50 DAY) THEN b.fullVisitorId ELSE NULL END) AS day50,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -60 DAY) THEN b.fullVisitorId ELSE NULL END) AS day60,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -70 DAY) THEN b.fullVisitorId ELSE NULL END) AS day70,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -80 DAY) THEN b.fullVisitorId ELSE NULL END) AS day80,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -90 DAY) THEN b.fullVisitorId ELSE NULL END) AS day90,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -100 DAY) THEN b.fullVisitorId ELSE NULL END) AS day100,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -110 DAY) THEN b.fullVisitorId ELSE NULL END) AS day110,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -120 DAY) THEN b.fullVisitorId ELSE NULL END) AS day120,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -130 DAY) THEN b.fullVisitorId ELSE NULL END) AS day130,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -140 DAY) THEN b.fullVisitorId ELSE NULL END) AS day140,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -150 DAY) THEN b.fullVisitorId ELSE NULL END) AS day150,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -160 DAY) THEN b.fullVisitorId ELSE NULL END) AS day160,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -170 DAY) THEN b.fullVisitorId ELSE NULL END) AS day170,  
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -180 DAY) THEN b.fullVisitorId ELSE NULL END) AS day180,  
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -190 DAY) THEN b.fullVisitorId ELSE NULL END) AS day190,  
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -200 DAY) THEN b.fullVisitorId ELSE NULL END) AS day200  
  
FROM (
  SELECT
    fullVisitorId,
    date(TIMESTAMP_seconds(visitStartTime)) as date
  FROM `big-query-project-1050.75392247.*`
  WHERE
    device.operatingSystem = 'iOS'
    and date(TIMESTAMP_seconds(visitStartTime)) between '2015-05-01' and CURRENT_DATE() 
  GROUP BY
    1,
    2) a
LEFT JOIN (
  SELECT
    fullVisitorId,
    date(TIMESTAMP_seconds(visitStartTime)) as date
  FROM `big-query-project-1050.75392247.*`
  WHERE
    device.operatingSystem = 'iOS'
       and date(TIMESTAMP_seconds(visitStartTime)) between '2015-05-01' and CURRENT_DATE() 
  GROUP BY
    1,
    2) b
ON
  (a.fullVisitorId=b.fullVisitorId)
GROUP BY
  1,
  2
ORDER BY
  1,
  2 ASC