level 5 query

Potential.sql

SELECT * FROM
(
SELECT * FROM
(
SELECT
CASE 
   WHEN (0 = AllFacetValue) THEN 0
   ELSE ( round( ( AverageRelevence - ItemRelevance ) * ItemVisits, 2 ) * 100.0 / AllFacetValue )
END AS Particle,

FacetId, FacetName, 
AllFacetValue, AverageRelevence,
ItemValue, ItemVisits, ItemRelevance

FROM
(
SELECT SUM( Value )                       AS AllFacetValue,
       1.0 * SUM( Value ) / SUM( Visits ) AS AverageRelevence
FROM CampaignCategoriesOverview_lang_en_site_website
WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
)
LEFT JOIN
(
       SELECT trim( trim( FacetId ), '"' )                        AS FacetId,
       '' || trim( trim( [FacetId] ), '"' )                        AS FacetName,
       1.0 * SUM( Value ) / SUM( Visits )             AS ItemRelevance,
       SUM( Visits )                                  AS ItemVisits,
       SUM( Value  )                                  AS ItemValue
       FROM CampaignReferringSites_lang_en_site_website
       WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
       GROUP BY FacetId
)
ON (1 = 1)

WHERE ( Particle >= 0 )
ORDER BY Particle DESC
LIMIT 10
)

UNION ALL


SELECT
CASE 
   WHEN (0 = AllFacetValue) THEN 0
   ELSE ( round( ( AverageRelevence - ItemRelevance ) * ItemVisits, 2 ) * 100.0 / AllFacetValue )
END AS Particle,

'other_id' AS FacetId,
'(other)' AS FacetName,
AllFacetValue, AverageRelevence,
SUM( ItemValue ) AS ItemValue,
SUM( ItemVisits) AS ItemVisits,
SUM( ItemValue ) / SUM( ItemVisits) AS ItemRelevance

FROM
(
    SELECT SUM( Value )                       AS AllFacetValue,
           1.0 * SUM( Value ) / SUM( Visits ) AS AverageRelevence
    FROM CampaignCategoriesOverview_lang_en_site_website
    WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
)
LEFT JOIN
(
       SELECT trim( trim( FacetId ), '"' )                        AS FacetId,
       '' || trim( trim( [FacetId] ), '"' )                        AS FacetName,
       1.0 * SUM( Value ) / SUM( Visits )             AS ItemRelevance,
       SUM( Visits )                                  AS ItemVisits,
       SUM( Value  )                                  AS ItemValue
       FROM CampaignReferringSites_lang_en_site_website
       WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
       GROUP BY FacetId
)
ON (1 = 1)
WHERE FacetId NOT IN
(
    SELECT DISTINCT FacetId
    FROM
    (
        SELECT
        CASE 
           WHEN (0 = AllFacetValue) THEN 0
           ELSE ( round( ( AverageRelevence - ItemRelevance ) * ItemVisits, 2 ) * 100.0 / AllFacetValue )
        END AS Particle,

        FacetId, FacetName, 
        AllFacetValue, AverageRelevence,
        ItemValue, ItemVisits, ItemRelevance

        FROM
        (
            SELECT SUM( Value )                       AS AllFacetValue,
                   1.0 * SUM( Value ) / SUM( Visits ) AS AverageRelevence
            FROM CampaignCategoriesOverview_lang_en_site_website
            WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
        )
        LEFT JOIN
        (
               SELECT trim( trim( FacetId ), '"' )                        AS FacetId,
               '' || trim( trim( [FacetId] ), '"' )                        AS FacetName,
               1.0 * SUM( Value ) / SUM( Visits )             AS ItemRelevance,
               SUM( Visits )                                  AS ItemVisits,
               SUM( Value  )                                  AS ItemValue
               FROM CampaignReferringSites_lang_en_site_website
               WHERE Date BETWEEN '2012-01-01' AND '2012-03-31'
               GROUP BY FacetId
        )
        ON (1 = 1)

        WHERE ( Particle >= 0 )
        ORDER BY Particle DESC
        LIMIT 10
    )
)


)
ORDER BY Particle;