q77_modified.sql

*** q77.sql	2016-03-25 14:10:21.910553746 +0100
--- q77_modified.sql	2016-03-25 14:10:21.910553746 +0100
***************
*** 4,63 ****
            sum(ss_net_profit) AS profit
     FROM "${database}"."${schema}".store_sales,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".store
     WHERE ss_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
       AND ss_store_sk = s_store_sk
     GROUP BY s_store_sk) ,
       sr AS
    (SELECT s_store_sk,
            sum(sr_return_amt) AS RETURNS,
            sum(sr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".store_returns,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".store
     WHERE sr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
       AND sr_store_sk = s_store_sk
     GROUP BY s_store_sk),
       cs AS
    (SELECT cs_call_center_sk,
            sum(cs_ext_sales_price) AS sales,
            sum(cs_net_profit) AS profit
     FROM "${database}"."${schema}".catalog_sales,
          "${database}"."${schema}".date_dim
     WHERE cs_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
     GROUP BY cs_call_center_sk),
       cr AS
    (SELECT sum(cr_return_amount) AS RETURNS,
            sum(cr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".catalog_returns,
          "${database}"."${schema}".date_dim
     WHERE cr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days) ),
       ws AS
    (SELECT wp_web_page_sk,
            sum(ws_ext_sales_price) AS sales,
            sum(ws_net_profit) AS profit
     FROM "${database}"."${schema}".web_sales,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".web_page
     WHERE ws_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
       AND ws_web_page_sk = wp_web_page_sk
     GROUP BY wp_web_page_sk),
       wr AS
    (SELECT wp_web_page_sk,
            sum(wr_return_amt) AS RETURNS,
            sum(wr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".web_returns,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".web_page
     WHERE wr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
       AND wr_web_page_sk = wp_web_page_sk
     GROUP BY wp_web_page_sk)
  SELECT channel ,
         id ,
         sum(sales) AS sales ,
--- 4,63 ----
            sum(ss_net_profit) AS profit
     FROM "${database}"."${schema}".store_sales,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".store
     WHERE ss_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY)
       AND ss_store_sk = s_store_sk
     GROUP BY s_store_sk) ,
       sr AS
    (SELECT s_store_sk,
            sum(sr_return_amt) AS RETURNS,
            sum(sr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".store_returns,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".store
     WHERE sr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY)
       AND sr_store_sk = s_store_sk
     GROUP BY s_store_sk),
       cs AS
    (SELECT cs_call_center_sk,
            sum(cs_ext_sales_price) AS sales,
            sum(cs_net_profit) AS profit
     FROM "${database}"."${schema}".catalog_sales,
          "${database}"."${schema}".date_dim
     WHERE cs_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY)
     GROUP BY cs_call_center_sk),
       cr AS
    (SELECT sum(cr_return_amount) AS RETURNS,
            sum(cr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".catalog_returns,
          "${database}"."${schema}".date_dim
     WHERE cr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY) ),
       ws AS
    (SELECT wp_web_page_sk,
            sum(ws_ext_sales_price) AS sales,
            sum(ws_net_profit) AS profit
     FROM "${database}"."${schema}".web_sales,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".web_page
     WHERE ws_sold_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY)
       AND ws_web_page_sk = wp_web_page_sk
     GROUP BY wp_web_page_sk),
       wr AS
    (SELECT wp_web_page_sk,
            sum(wr_return_amt) AS RETURNS,
            sum(wr_net_loss) AS profit_loss
     FROM "${database}"."${schema}".web_returns,
          "${database}"."${schema}".date_dim,
          "${database}"."${schema}".web_page
     WHERE wr_returned_date_sk = d_date_sk
!      AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + INTERVAL '30' DAY)
       AND wr_web_page_sk = wp_web_page_sk
     GROUP BY wp_web_page_sk)
  SELECT channel ,
         id ,
         sum(sales) AS sales ,