divisor-by-zero.sql

divisor-by-zero.sql

SELECT '<span class=''xl green''>' ||
        COUNT_SHARE ||
        '%' ||
        '</span>' ||
        ' of lost opportunities are of ' ||
        '<span class=''xl blue''>' ||
        COMPLEXITY  ||
        '</span>' ||
        ' complexity, representing ' ||
        '<span class=''xl red''>' ||
        VALUE_SHARE ||
        '%' ||
        '</span>' ||
        ' of the value.' as summary
FROM
  (SELECT COMPLEXITY,
          VALUE,
          COUNT,
          ISO_CODE,
       CAST(CASE WHEN VALUE=0 THEN 0 ELSE 100*VALUE/SUM(VALUE) OVER (PARTITION BY 1) END AS INTEGER) AS VALUE_SHARE,
       CAST(CASE WHEN COUNT=0 THEN 0 ELSE 100*COUNT/SUM(COUNT) OVER (PARTITION BY 1) END AS INTEGER) AS COUNT_SHARE,
       CAST(CASE WHEN VALUE=0 THEN 0 ELSE -100*VALUE/SUM(VALUE) OVER (PARTITION BY 1) END AS INTEGER) AS VALUE_SHARE_CHART
   FROM
     (SELECT COMPLEXITY,
             CAST(SUM(CONVERSION_RATE*VALUE) AS INTEGER) AS VALUE,
             CAST(COUNT(OPPORTUNITY_ID) AS INTEGER) AS COUNT,
             ISO_CODE
      FROM
        (SELECT OPPORTUNITY_ID,
                SUM(NVL(sov,0)*MULTIPLIER) AS VALUE,
                COMPLEXITY
         FROM DATA_ONE_SF_LOOKUP
         INNER JOIN DATA_CORE_ONE_SF ON "DATA_ONE_SF_LOOKUP"."LINE_ITEM_ID" = DATA_CORE_ONE_SF."LINE_ITEM_ID"
         AND "DATA_ONE_SF_LOOKUP"."EBU" = DATA_CORE_ONE_SF."EBU"
         WHERE (("DATA_CORE_ONE_SF"."MONTH" >= DATE '2017-04-01') AND ("DATA_CORE_ONE_SF"."MONTH" <= DATE '2017-12-11') AND ("DATA_CORE_ONE_SF"."MARKET" IN ('OpCo', '_', 'Partner', 'Off-Footprint')) AND ("DATA_CORE_ONE_SF"."COUNTRY_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."ACCOUNT_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."BILLING_TYPE" IN ('Centrally Billed', '_', 'Locally Billed', 'Non_Vodafone Billed')) AND ("DATA_CORE_ONE_SF"."BUSINESS_TYPE" IN ('New', '_', 'Renewal')) AND ("DATA_CORE_ONE_SF"."PRODUCT_CATEGORY" IN ('Fixed', '_', 'Other')))
           AND LOST = 1
           AND ASIS = 0
           AND EMPLOYEE_ID = '005b0000001xcJxAAI'
         GROUP BY OPPORTUNITY_ID,
                  COMPLEXITY)
      CROSS JOIN
        (SELECT conversion_rate,
                iso AS iso_code
         FROM EXCHANGE_RATES
         WHERE sysdate BETWEEN start_date AND next_start_date
           AND iso = 'EUR' )
      GROUP BY ISO_CODE,
               COMPLEXITY)
   ORDER BY CAST(100*VALUE/SUM(VALUE) OVER (PARTITION BY 1) AS INTEGER) DESC )
WHERE ROWNUM = 1