q49_modified.sql.diff

*** q49.sql	2016-03-25 14:10:21.906553746 +0100
--- q49_modified.sql	2016-03-25 14:10:21.906553746 +0100
***************
*** 10,20 ****
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
                                                   rank() over (
                                                                ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".web_sales ws
        LEFT OUTER JOIN "${database}"."${schema}".web_returns wr ON (ws.ws_order_number = wr.wr_order_number
                                           AND ws.ws_item_sk = wr.wr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE wr.wr_return_amt > 10000
          AND ws.ws_net_profit > 1
--- 10,20 ----
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
                                                   rank() over (
                                                                ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".web_sales ws
        LEFT OUTER JOIN "${database}"."${schema}".web_returns wr ON (ws.ws_order_number = wr.wr_order_number
                                           AND ws.ws_item_sk = wr.wr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE wr.wr_return_amt > 10000
          AND ws.ws_net_profit > 1
***************
*** 39,49 ****
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
            rank() over (
                         ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".catalog_sales cs
        LEFT OUTER JOIN "${database}"."${schema}".catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number
                                               AND cs.cs_item_sk = cr.cr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE cr.cr_return_amount > 10000
          AND cs.cs_net_profit > 1
--- 39,49 ----
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
            rank() over (
                         ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".catalog_sales cs
        LEFT OUTER JOIN "${database}"."${schema}".catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number
                                               AND cs.cs_item_sk = cr.cr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE cr.cr_return_amount > 10000
          AND cs.cs_net_profit > 1
***************
*** 68,78 ****
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
                                                   rank() over (
                                                                ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".store_sales sts
        LEFT OUTER JOIN "${database}"."${schema}".store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number
                                             AND sts.ss_item_sk = sr.sr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE sr.sr_return_amt > 10000
          AND sts.ss_net_profit > 1
--- 68,78 ----
            rank() over (
                         ORDER BY return_ratio) AS return_rank,
                                                   rank() over (
                                                                ORDER BY currency_ratio) AS currency_rank
     FROM
!      (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
        FROM "${database}"."${schema}".store_sales sts
        LEFT OUTER JOIN "${database}"."${schema}".store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number
                                             AND sts.ss_item_sk = sr.sr_item_sk) ,"${database}"."${schema}".date_dim
        WHERE sr.sr_return_amt > 10000
          AND sts.ss_net_profit > 1