Untitled

WITH COMPETITOR_MARKETPLACE_MAP AS
(
    SELECT 'www.cinemanow.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.gamehouse.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.virginmega.fr' AS COMPETITOR_SITE_URL,5 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'download.mediamarkt.de' AS COMPETITOR_SITE_URL,4 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'store.steampowered.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.bestbuy.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www2.saturn.de' AS COMPETITOR_SITE_URL,4 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'itunes.apple.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.iwin.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.fnac.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.musicload.de' AS COMPETITOR_SITE_URL,4 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'gamefly.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'play.google.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'store.origin.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'magazines.barnesandnoble.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL UNION ALL
    SELECT 'www.7digital.com' AS COMPETITOR_SITE_URL,1 AS MARKETPLACE_ID FROM DUAL
),

ASIN_COMPETITOR_MONITORED AS (

    SELECT
        ASIN,
        A.COMPETITOR_SITE_URL,
        PRICE_VALUE
    FROM
        DIG_SMT_DDL.SPARK_LISTING_INFO A
    INNER JOIN
        COMPETITOR_MARKETPLACE_MAP B
    ON A.COMPETITOR_SITE_URL = B.COMPETITOR_SITE_URL
    WHERE
        SNAPSHOT_DAY = TO_DATE('18/06/2012','DD/MM/YYYY')
    AND MARKETPLACE_ID = 1
),

ASIN_GVC AS (

    SELECT
        ASIN,
        (GLANCE_VIEW_COUNT/
        ( SELECT SUM(GLANCE_VIEW_COUNT) FROM  D_DAILY_ASIN_GV_METRICS WHERE SNAPSHOT_DAY = TO_DATE('18/06/2012','DD/MM/YYYY') AND GL_PRODUCT_GROUP = 340 AND MARKETPLACE_ID = 1 )) AS GVC_RATIO
    FROM
        D_DAILY_ASIN_GV_METRICS
    WHERE
        GL_PRODUCT_GROUP = 340
    AND SNAPSHOT_DAY = TO_DATE('18/06/2012','DD/MM/YYYY')
    AND MARKETPLACE_ID = 1
), ASIN_COMPETITOR_GVC AS (

    SELECT
        A.ASIN,
        A.COMPETITOR_SITE_URL,
        A.PRICE_VALUE,
        B.GVC_RATIO
    FROM
        ASIN_COMPETITOR_MONITORED A
    INNER JOIN
        ASIN_GVC B
    ON A.ASIN = B.ASIN
)

SELECT * FROM

    (
      SELECT
          COMPETITOR_SITE_URL,
          SUM(GVC_RATIO) AS UNIQUE_COVERAGE
      FROM
          ( SELECT ASIN FROM ASIN_COMPETITOR_GVC GROUP BY ASIN HAVING COUNT(*) = 1 ) UNIQUE_ASINS, ASIN_COMPETITOR_GVC
      WHERE
          ASIN_COMPETITOR_GVC.ASIN=UNIQUE_ASINS.ASIN
      GROUP BY
          ASIN_COMPETITOR_GVC.COMPETITOR_SITE_URL
    ) UNIQUE_LISTING

    NATURAL FULL OUTER JOIN
    (
      WITH MORECOMP AS (
          SELECT  t.asin,
                  MIN(t.price_VALUE) AS PRICE_VALUE
          FROM
            ( SELECT asin FROM ASIN_COMPETITOR_GVC GROUP BY asin HAVING COUNT(*) >1 ) p,
            ASIN_COMPETITOR_GVC t
          WHERE t.asin=p.asin
          GROUP BY t.asin
      )
      SELECT  t.competitor_site_url,
            SUM(GVC_RATIO)  as ONLY_LOWEST_COVERAGE
            FROM
              ( SELECT x.asin AS asin, x.PRICE_VALUE
                FROM morecomp, ASIN_COMPETITOR_GVC x
                WHERE
                  x.asin = morecomp.asin  AND
                  x.PRICE_VALUE=morecomP.PRICE_VALUE
                GROUP BY x.asin, x.PRICE_VALUE
                HAVING COUNT(*)=1
              ) p ,  ASIN_COMPETITOR_GVC t
      WHERE
        t.PRICE_VALUE = p.PRICE_VALUE  AND
        t.asin             = p.asin
      GROUP BY t.competitor_site_url
    )  LOWEST_LISTING

    NATURAL FULL OUTER JOIN

              (
                SELECT  competitor_site_url,
                        sum(GVC_RATIO) as ALSO_LOWEST_COVERAGE
                FROM  (
                    SELECT  ASIN_COMPETITOR_GVC.asin       AS asin,
                            ASIN_COMPETITOR_GVC.price_VALUE AS price
                    FROM (
                          SELECT asin, MIN(price_VALUE) AS low FROM ASIN_COMPETITOR_GVC GROUP BY asin
                        ) p ,  ASIN_COMPETITOR_GVC
                    WHERE
                        p.asin = ASIN_COMPETITOR_GVC.asin AND
                        p.low    = ASIN_COMPETITOR_GVC.price_VALUE
                    GROUP BY
                        ASIN_COMPETITOR_GVC.asin,
                        ASIN_COMPETITOR_GVC.price_VALUE
                    HAVING COUNT(*) >1
                  ) j1, ASIN_COMPETITOR_GVC x
                WHERE
                    x.asin  =j1.asin  AND
                    x.price_VALUE=price
                GROUP BY x.competitor_site_url
              ) ALSO_LOWEST

NATURAL  FULL OUTER JOIN

              (
                SELECT competitor_site_url,
                 SUM (GVC_RATIO) as NOT_LOWEST_COVERAGE
                FROM  (
                    SELECT DISTINCT t.competitor_site_url, t.asin,t.GVC_RATIO
                    FROM
                      ( SELECT
                                DISTINCT  ASIN_COMPETITOR_GVC.asin,
                                MIN( ASIN_COMPETITOR_GVC.price_VALUE) AS low
                        FROM  ASIN_COMPETITOR_GVC
                        GROUP BY  ASIN_COMPETITOR_GVC.asin
                      ) p ,  ASIN_COMPETITOR_GVC t
                    WHERE
                      p.asin = t.asin  AND
                      p.low   < t.PRICE_VALUE
                  )
                GROUP BY competitor_site_url
              ) NOT_LO0WEST

NATURAL FULL OUTER JOIN

              (

                SELECT
                    COMPETITOR_SITE_URL,
                    SUM (GVC_RATIO) AS COVERAGE
                FROM
                    ASIN_COMPETITOR_GVC
                GROUP BY
                    COMPETITOR_SITE_URL
              ) COVERAGE