WITH

period AS
(SELECT "20180401" as start, "20180431" as finish),

ses AS (
SELECT clientId, geoNetwork.country
FROM `dokdbroker.OWOXBI_Streaming.session_streaming_*`
WHERE _TABLE_SUFFIX BETWEEN (SELECT start FROM period) AND (SELECT finish FROM period)
  AND clientId IS NOT null
)

SELECT
  *,
  (SELECT LPAD(start, 6) FROM period) as month
FROM (
  SELECT
    * EXCEPT(session),
    COUNT (DISTINCT session) numSession
  FROM (
    SELECT
      * EXCEPT (maslo, akkum),
      MAX (maslo) isMaslo,
      MAX (akkum) isAkkum
    FROM (
      SELECT
        (SELECT value FROM UNNEST(customDimensions) WHERE index = 2) AS session,
        (SELECT value FROM UNNEST(customDimensions) WHERE index = 3) AS funnelPhase,
        IF((SELECT value FROM UNNEST(customGroups) WHERE index = 3) IN ("20","501","521"),1,0) AS maslo,
        IF((SELECT value FROM UNNEST(customGroups) WHERE index = 3) = "398",1,0) AS akkum,
        IF (clientId IN (SELECT clientId FROM ses WHERE country = "Ukraine")
          OR clientId NOT IN (SELECT clientId FROM ses), "Ukraine or NA", "abroad") AS country
      FROM
        `dokdbroker.OWOXBI_Streaming.streaming_*`
      WHERE
        _TABLE_SUFFIX BETWEEN (SELECT start FROM period) AND (SELECT finish FROM period)
    )
    WHERE true
      AND funnelPhase IS NOT NULL
      AND funnelPhase <> "NA"
      AND session <> "0"
    GROUP BY
      funnelPhase, country, session
  )
  GROUP BY
    funnelPhase, country, isMaslo, isAkkum

  UNION ALL
  SELECT
    "Accepted" AS funnelPhase,
    "Ukraine or NA" AS country,
    null isMaslo, null isAkkum,
    COUNT(*) numSession
  FROM `dokdbroker.OWOXBI_Streaming.streaming_*`
  WHERE _TABLE_SUFFIX BETWEEN (SELECT start FROM period) AND (SELECT finish FROM period)
    AND (SELECT value FROM UNNEST(customDimensions) WHERE index = 2) = "0"
)
WHERE NOT EXISTS
  (SELECT 1 FROM download.Funnel_KPI
   WHERE month = (SELECT LPAD(start, 6) FROM period))