0001 SELECT
0002 'web' AS channel,
0003 web.item,
0004 web.return_ratio,
0005 web.return_rank,
0006 web.currency_rank
0007 FROM (
0008 SELECT
0009 item,
0010 return_ratio,
0011 currency_ratio,
0012 rank()
0013 OVER (
0014 ORDER BY return_ratio) AS return_rank,
0015 rank()
0016 OVER (
0017 ORDER BY currency_ratio) AS currency_rank
0018 FROM
0019 (SELECT
0020 ws.ws_item_sk AS item,
0021 (cast(sum(coalesce(wr.wr_return_quantity, 0)) AS DECIMAL(15, 4)) /
0022 cast(sum(coalesce(ws.ws_quantity, 0)) AS DECIMAL(15, 4))) AS return_ratio,
0023 (cast(sum(coalesce(wr.wr_return_amt, 0)) AS DECIMAL(15, 4)) /
0024 cast(sum(coalesce(ws.ws_net_paid, 0)) AS DECIMAL(15, 4))) AS currency_ratio
0025 FROM
0026 web_sales ws LEFT OUTER JOIN web_returns wr
0027 ON (ws.ws_order_number = wr.wr_order_number AND
0028 ws.ws_item_sk = wr.wr_item_sk)
0029 , date_dim
0030 WHERE
0031 wr.wr_return_amt > 10000
0032 AND ws.ws_net_profit > 1
0033 AND ws.ws_net_paid > 0
0034 AND ws.ws_quantity > 0
0035 AND ws_sold_date_sk = d_date_sk
0036 AND d_year = 2001
0037 AND d_moy = 12
0038 GROUP BY ws.ws_item_sk
0039 ) in_web
0040 ) web
0041 WHERE (web.return_rank <= 10 OR web.currency_rank <= 10)
0042 UNION
0043 SELECT
0044 'catalog' AS channel,
0045 catalog.item,
0046 catalog.return_ratio,
0047 catalog.return_rank,
0048 catalog.currency_rank
0049 FROM (
0050 SELECT
0051 item,
0052 return_ratio,
0053 currency_ratio,
0054 rank()
0055 OVER (
0056 ORDER BY return_ratio) AS return_rank,
0057 rank()
0058 OVER (
0059 ORDER BY currency_ratio) AS currency_rank
0060 FROM
0061 (SELECT
0062 cs.cs_item_sk AS item,
0063 (cast(sum(coalesce(cr.cr_return_quantity, 0)) AS DECIMAL(15, 4)) /
0064 cast(sum(coalesce(cs.cs_quantity, 0)) AS DECIMAL(15, 4))) AS return_ratio,
0065 (cast(sum(coalesce(cr.cr_return_amount, 0)) AS DECIMAL(15, 4)) /
0066 cast(sum(coalesce(cs.cs_net_paid, 0)) AS DECIMAL(15, 4))) AS currency_ratio
0067 FROM
0068 catalog_sales cs LEFT OUTER JOIN catalog_returns cr
0069 ON (cs.cs_order_number = cr.cr_order_number AND
0070 cs.cs_item_sk = cr.cr_item_sk)
0071 , date_dim
0072 WHERE
0073 cr.cr_return_amount > 10000
0074 AND cs.cs_net_profit > 1
0075 AND cs.cs_net_paid > 0
0076 AND cs.cs_quantity > 0
0077 AND cs_sold_date_sk = d_date_sk
0078 AND d_year = 2001
0079 AND d_moy = 12
0080 GROUP BY cs.cs_item_sk
0081 ) in_cat
0082 ) catalog
0083 WHERE (catalog.return_rank <= 10 OR catalog.currency_rank <= 10)
0084 UNION
0085 SELECT
0086 'store' AS channel,
0087 store.item,
0088 store.return_ratio,
0089 store.return_rank,
0090 store.currency_rank
0091 FROM (
0092 SELECT
0093 item,
0094 return_ratio,
0095 currency_ratio,
0096 rank()
0097 OVER (
0098 ORDER BY return_ratio) AS return_rank,
0099 rank()
0100 OVER (
0101 ORDER BY currency_ratio) AS currency_rank
0102 FROM
0103 (SELECT
0104 sts.ss_item_sk AS item,
0105 (cast(sum(coalesce(sr.sr_return_quantity, 0)) AS DECIMAL(15, 4)) /
0106 cast(sum(coalesce(sts.ss_quantity, 0)) AS DECIMAL(15, 4))) AS return_ratio,
0107 (cast(sum(coalesce(sr.sr_return_amt, 0)) AS DECIMAL(15, 4)) /
0108 cast(sum(coalesce(sts.ss_net_paid, 0)) AS DECIMAL(15, 4))) AS currency_ratio
0109 FROM
0110 store_sales sts LEFT OUTER JOIN store_returns sr
0111 ON (sts.ss_ticket_number = sr.sr_ticket_number AND sts.ss_item_sk = sr.sr_item_sk)
0112 , date_dim
0113 WHERE
0114 sr.sr_return_amt > 10000
0115 AND sts.ss_net_profit > 1
0116 AND sts.ss_net_paid > 0
0117 AND sts.ss_quantity > 0
0118 AND ss_sold_date_sk = d_date_sk
0119 AND d_year = 2001
0120 AND d_moy = 12
0121 GROUP BY sts.ss_item_sk
0122 ) in_store
0123 ) store
0124 WHERE (store.return_rank <= 10 OR store.currency_rank <= 10)
0125 ORDER BY 1, 4, 5
0126 LIMIT 100
