widget_usage.sql

WITH
    data AS (SELECT 'BESTSELLERS,BILLBOARD,ICON_LINKS,PRODUCTS_CAROUSEL,W_BG_H,W_BRAND_H,W_BRAND_WEB_DISCL,W_BUBBLE_LINKS_2,W_BUBBLE_NAV,W_CAROUSEL,W_CAROUSELt,W_CAT_LIST,W_COLOR_BAND,W_HELPER_LINKS,W_MEDIA_SET,W_MEDIA_SET,W_MY_ORDERS,W_PIC_GAL,W_PICTURE_LINKS,W_REC_SHOP,W_SOCIAL_LINE,W_VIDEO,WIDGET_AUTH_DESCR_PROD,WIDGET_BUTTON_LINK,WIDGET_DELIMITER,WIDGET_DISCLAIMER,WIDGET_ENTRYPOINTS,WIDGET_HTML,WIDGET_IMAGE_LINK,WIDGET_NAV_TREE,WIDGET_NAVNODE_RECIPES,WIDGET_PRODUCTS_RAIN,WIDGET_SEARCH_AUTHORS,WIDGET_SPACER,WIDGET_TEXT' str
             FROM dual),
    widget_names AS (
      SELECT trim(regexp_substr(str, '[^,]+', 1, level)) name
      FROM data
      CONNECT BY instr(str, ',', 1, level - 1) > 0
  ),
    published_promo AS (
      SELECT *
      FROM nt_promo
      WHERE published IS NOT NULL AND unpublished IS NULL
  ),
    widget_usage AS (
      SELECT w.name, count(*) cnt
      FROM nt_promo_widget w
        JOIN published_promo pp ON pp.id = w.promo_id
      WHERE w.name IN (SELECT name
                       FROM widget_names)
      GROUP BY w.name
  )
  SELECT wn.name, nvl(usage.cnt, 0) FROM widget_names wn
  LEFT JOIN widget_usage usage ON usage.name = wn.name;