Created
March 29, 2016 07:31
-
-
Save kokosing/0d6c85037db109c65c4e to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [1;31m*** q49.sql 2016-03-25 14:10:21.906553746 +0100[0;0m | |
| [1;34m--- q49_modified.sql 2016-03-25 14:10:21.906553746 +0100[0;0m | |
| [1;35m***************[0;0m | |
| [1;35m*** 10,20 ****[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;31m! (SELECT ws.ws_item_sk AS item ,(cast(sum(coalesce(wr.wr_return_quantity,0)) AS dec(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS dec(15,4))) AS return_ratio ,(cast(sum(coalesce(wr.wr_return_amt,0)) AS dec(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS dec(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".web_sales ws[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".web_returns wr ON (ws.ws_order_number = wr.wr_order_number[0;0m | |
| [0;0m AND ws.ws_item_sk = wr.wr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE wr.wr_return_amt > 10000[0;0m | |
| [0;0m AND ws.ws_net_profit > 1[0;0m | |
| [1;35m--- 10,20 ----[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;34m! (SELECT ws.ws_item_sk AS item ,(cast(sum(coalesce(wr.wr_return_quantity,0)) AS DECIMAL(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS DECIMAL(15,4))) AS return_ratio ,(cast(sum(coalesce(wr.wr_return_amt,0)) AS DECIMAL(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS DECIMAL(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".web_sales ws[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".web_returns wr ON (ws.ws_order_number = wr.wr_order_number[0;0m | |
| [0;0m AND ws.ws_item_sk = wr.wr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE wr.wr_return_amt > 10000[0;0m | |
| [0;0m AND ws.ws_net_profit > 1[0;0m | |
| [1;35m***************[0;0m | |
| [1;35m*** 39,49 ****[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;31m! (SELECT cs.cs_item_sk AS item ,(cast(sum(coalesce(cr.cr_return_quantity,0)) AS dec(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS dec(15,4))) AS return_ratio ,(cast(sum(coalesce(cr.cr_return_amount,0)) AS dec(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS dec(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".catalog_sales cs[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number[0;0m | |
| [0;0m AND cs.cs_item_sk = cr.cr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE cr.cr_return_amount > 10000[0;0m | |
| [0;0m AND cs.cs_net_profit > 1[0;0m | |
| [1;35m--- 39,49 ----[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;34m! (SELECT cs.cs_item_sk AS item ,(cast(sum(coalesce(cr.cr_return_quantity,0)) AS DECIMAL(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS DECIMAL(15,4))) AS return_ratio ,(cast(sum(coalesce(cr.cr_return_amount,0)) AS DECIMAL(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS DECIMAL(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".catalog_sales cs[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number[0;0m | |
| [0;0m AND cs.cs_item_sk = cr.cr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE cr.cr_return_amount > 10000[0;0m | |
| [0;0m AND cs.cs_net_profit > 1[0;0m | |
| [1;35m***************[0;0m | |
| [1;35m*** 68,78 ****[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;31m! (SELECT sts.ss_item_sk AS item ,(cast(sum(coalesce(sr.sr_return_quantity,0)) AS dec(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) AS dec(15,4))) AS return_ratio ,(cast(sum(coalesce(sr.sr_return_amt,0)) AS dec(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) AS dec(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".store_sales sts[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number[0;0m | |
| [0;0m AND sts.ss_item_sk = sr.sr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE sr.sr_return_amt > 10000[0;0m | |
| [0;0m AND sts.ss_net_profit > 1[0;0m | |
| [1;35m--- 68,78 ----[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY return_ratio) AS return_rank,[0;0m | |
| [0;0m rank() over ([0;0m | |
| [0;0m ORDER BY currency_ratio) AS currency_rank[0;0m | |
| [0;0m FROM[0;0m | |
| [1;34m! (SELECT sts.ss_item_sk AS item ,(cast(sum(coalesce(sr.sr_return_quantity,0)) AS DECIMAL(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) AS DECIMAL(15,4))) AS return_ratio ,(cast(sum(coalesce(sr.sr_return_amt,0)) AS DECIMAL(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) AS DECIMAL(15,4))) AS currency_ratio[0;0m | |
| [0;0m FROM "${database}"."${schema}".store_sales sts[0;0m | |
| [0;0m LEFT OUTER JOIN "${database}"."${schema}".store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number[0;0m | |
| [0;0m AND sts.ss_item_sk = sr.sr_item_sk) ,"${database}"."${schema}".date_dim[0;0m | |
| [0;0m WHERE sr.sr_return_amt > 10000[0;0m | |
| [0;0m AND sts.ss_net_profit > 1[0;0m |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment