SQL: CONTENT_PV_DAILY

SELECT
    DAY_KEY,
    FULL_URI,
    MODULE_DESC,
    SUM(ALL_IMPRESSIONS) ALL_IMPRESSIONS,
    SUM(IMPRESSIONS) IMPRESSIONS,
    SUM(CLICKS) CLICKS,
    SUM(SPONSORED_CLICKS) SPONSORED_CLICKS,

    SUM(CLICK_POS_1) CLICK_POS_1,
    SUM(CLICK_POS_2) CLICK_POS_2,
    SUM(CLICK_POS_3) CLICK_POS_3,
    SUM(CLICK_POS_4) CLICK_POS_4,
    SUM(CLICK_POS_OTHER) CLICK_POS_OTHER
FROM
(
SELECT
    DAY_KEY,
    FULL_URI,
    MODULE_DESC,
    0 ALL_IMPRESSIONS,
    0 IMPRESSIONS,
    COUNT(*) CLICKS,
    SUM(CASE WHEN FULL_URI_CLICK NOT LIKE '%***.com%' THEN 1 ELSE 0 END) SPONSORED_CLICKS,

    SUM(CASE WHEN REGEXP_EXTRACT( FULL_URI_CLICK, '[0-9]+$' ) = 1 THEN 1 ELSE 0 END) CLICK_POS_1,
    SUM(CASE WHEN REGEXP_EXTRACT( FULL_URI_CLICK, '[0-9]+$' ) = 2 THEN 1 ELSE 0 END) CLICK_POS_2,
    SUM(CASE WHEN REGEXP_EXTRACT( FULL_URI_CLICK, '[0-9]+$' ) = 3 THEN 1 ELSE 0 END) CLICK_POS_3,
    SUM(CASE WHEN REGEXP_EXTRACT( FULL_URI_CLICK, '[0-9]+$' ) = 4 THEN 1 ELSE 0 END) CLICK_POS_4,
    SUM(CASE WHEN REGEXP_EXTRACT( FULL_URI_CLICK, '[0-9]+$' ) NOT IN (1, 2, 3, 4) THEN 1 ELSE 0 END) CLICK_POS_OTHER

FROM
    PRD_RPT..VW_FACT_OMNITURE_LINKIMPRESSION_CLICKS_SEARCH
WHERE
    DAY_KEY >= 20111020
AND
    SITE_KEY = 1290001
GROUP BY
    DAY_KEY,
    FULL_URI,
    MODULE_DESC

UNION ALL

SELECT
    DAY_KEY,
    FULL_URI,
    MODULE_DESC,
    COUNT(*) ALL_IMPRESSIONS,
    SUM(CASE WHEN RELATED_CONTENT_KEY != 21500001 THEN 1 ELSE 0 END) IMPRESSIONS,
    0 CLICKS,
    0,
    0,0,0,0,0
FROM
    PRD_RPT..VW_FACT_OMNITURE_LINKIMPRESSION_SEARCH
WHERE
    DAY_KEY >= 20111020
AND
    SITE_KEY = 1290001
GROUP BY
    DAY_KEY,
    FULL_URI,
    MODULE_DESC
) x
GROUP BY
    DAY_KEY,
    FULL_URI,
    MODULE_DESC