iOS Retention - BigQuery - (Tableau)

iOS Retention - BigQuery - (Tableau)

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 -60 DAY) THEN b.fullVisitorId ELSE NULL END) AS day60,
  COUNT(DISTINCT CASE WHEN a.date = date_add(b.date, INTERVAL -90 DAY) THEN b.fullVisitorId ELSE NULL END) AS day90
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 date_add(CURRENT_DATE(), INTERVAL -120 DAY) 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 date_add(CURRENT_DATE(), INTERVAL -120 DAY) and CURRENT_DATE() 
  GROUP BY
    1,
    2) b
ON
  (a.fullVisitorId=b.fullVisitorId)
GROUP BY
  1,
  2
ORDER BY
  1,
  2 ASC