Share of the sessions in which goods was purchased on landing page

As a result of the query you receive a share of the sessions, during which the user was landed on the product card and then made an order in the composition of which was goods from landing page.

Dynamic parameters

• startDate - start date of the analyzed period
• endDate - end date of the analyzed period
• dataSetName - name of dataset, where stored session streaming table
• tableName - name of the table, where stored session Streaming data
• categoriesNumber - number of TOP-categories to be analyzed

SQL-query

SELECT
  landingProductCategory,
  shareOfSessions FROM
  (
  SELECT
    all.landingProductCategory AS landingProductCategory,
    same.totalSessions,
    all.totalSessions,
    ROUND(same.totalSessions/all.totalSessions, 2) AS shareOfSessions FROM
    (
    SELECT
      landingProductCategory,
      COUNT(*) AS totalSessions FROM
      (
      SELECT
        sessionId,
        hits.product.productSku AS sku,
        hits.product.productCategory AS landingProductCategory
      FROM
        FLATTEN ( (
          SELECT
            sessionId,
            hits.product.productCategory,
            hits.eCommerceActionType,
            hits.isEntrance,
            hits.product.productSku
          FROM
            TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
      WHERE
        hits.isEntrance = 1
        AND hits.eCommerceActionType = 'detail'
        AND NOT hits.product.productCategory IN ('(not set)',
          '')
      GROUP BY
        sessionId,
        sku,
        landingProductCategory) t1
    JOIN EACH
      (
      SELECT
        sessionId,
        hits.product.productSku AS sku
      FROM
        FLATTEN ( (
          SELECT
            sessionId,
            hits.eCommerceActionType,
            hits.product.productSku
          FROM
            TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
      WHERE
        hits.eCommerceActionType = 'purchase'
      GROUP BY
        sessionId,
        sku) t2
    ON
      t1.sessionId = t2.sessionId
    GROUP BY
      landingProductCategory
    ORDER BY
      totalSessions DESC
    LIMIT
      {categoriesNumber default='15' values='5,10,15,20,25,30' type='select'} --оставляем TOP категорий
      ) AS all
  LEFT JOIN EACH
    (
    SELECT
      landingProductCategory,
      COUNT(*) AS totalSessions FROM
      (
      SELECT
        sessionId,
        hits.product.productSku AS sku,
        hits.product.productCategory AS landingProductCategory
      FROM
        FLATTEN ( (
          SELECT
            sessionId,
            hits.product.productCategory,
            hits.eCommerceActionType,
            hits.isEntrance,
            hits.product.productSku
          FROM
            TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
      WHERE
        hits.isEntrance = 1
        AND hits.eCommerceActionType = 'detail'
        AND NOT hits.product.productCategory IN ('(not set)', '')
      GROUP BY
        sessionId,
        sku,
        landingProductCategory) t1
    JOIN EACH
      (
      SELECT
        sessionId,
        hits.product.productSku AS sku
      FROM
        FLATTEN ( (
          SELECT
            sessionId,
            hits.eCommerceActionType,
            hits.product.productSku
          FROM
            TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
      WHERE
        hits.eCommerceActionType = 'purchase'
      GROUP BY
        sessionId,
        sku) t2
    ON
      t1.sessionId = t2.sessionId
    WHERE
      t1.sku = t2.sku
    GROUP BY
      landingProductCategory) AS same
  ON
    (same.landingProductCategory = all.landingProductCategory)
  ORDER BY
    all.totalSessions DESC)