text: --
  --Copyright (C) 2016 Transaction Processing Performance Council (TPC) and/or
  --its contributors.
  --
  --This file is part of a software package distributed by the TPC.
  --
  --The contents of this file have been developed by the TPC, and/or have been
  --licensed to the TPC under one or more contributor license agreements.
  --
  -- This file is subject to the terms and conditions outlined in the End-User
  -- License Agreement (EULA) which can be found in this distribution (EULA.txt)
  -- and is available at the following URL:
  -- http://www.tpc.org/TPC_Documents_Current_Versions/txt/EULA.txt
  --
  --Unless required by applicable law or agreed to in writing, this software
  --is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF
  --ANY KIND, either express or implied, and the user bears the entire risk as
  --to quality and performance as well as the entire cost of service or repair
  --in case of defect.  See the EULA for more details.
  --
  
  --
  --Copyright 2015 Intel Corporation All Rights Reserved.
  --
  --The source code contained or described herein and all documents related to the source code ("Material") are owned by Intel Corporation or its suppliers or licensors. Title to the Material remains with Intel Corporation or its suppliers and licensors. The Material contains trade secrets and proprietary and confidential information of Intel or its suppliers and licensors. The Material is protected by worldwide copyright and trade secret laws and treaty provisions. No part of the Material may be used, copied, reproduced, modified, published, uploaded, posted, transmitted, distributed, or disclosed in any way without Intel's prior express written permission.
  --
  --No license under any patent, copyright, trade secret or other intellectual property right is granted to or conferred upon you by disclosure or delivery of the Materials, either expressly, by implication, inducement, estoppel or otherwise. Any license under such intellectual property rights must be express and approved by Intel in writing.
  
  
  -- Find all customers who viewed items of a given category on the web
  -- in a given month and year that was followed by an in-store purchase of an item from the same category in the three
  -- consecutive months.
  
  -- Resources
  --Result  --------------------------------------------------------------------
  --keep result human readable
  set hive.exec.compress.output=false;
  set hive.exec.compress.output;
  
  -- This query requires parallel order by for fast and deterministic global ordering of final result
  set hive.optimize.sampling.orderby=${hiveconf:bigbench.hive.optimize.sampling.orderby};
  set hive.optimize.sampling.orderby.number=${hiveconf:bigbench.hive.optimize.sampling.orderby.number};
  set hive.optimize.sampling.orderby.percent=${hiveconf:bigbench.hive.optimize.sampling.orderby.percent};
  --debug print
  set hive.optimize.sampling.orderby;
  set hive.optimize.sampling.orderby.number;
  set hive.optimize.sampling.orderby.percent;
  
  DROP TABLE IF EXISTS ${hiveconf:RESULT_TABLE};
  CREATE TABLE ${hiveconf:RESULT_TABLE} (
    u_id BIGINT
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
  STORED AS ${env:BIG_BENCH_hive_default_fileformat_result_table} LOCATION '${hiveconf:RESULT_DIR}';
  
  INSERT INTO TABLE ${hiveconf:RESULT_TABLE}
  SELECT DISTINCT wcs_user_sk -- Find all customers
  -- TODO check if 37134 is first day of the month
  FROM
  ( -- web_clicks viewed items in date range with items from specified categories
    SELECT
      wcs_user_sk,
      wcs_click_date_sk
    FROM web_clickstreams, item
    WHERE wcs_click_date_sk BETWEEN 37134 AND (37134 + 30) -- in a given month and year
    AND i_category IN (${hiveconf:q12_i_category_IN}) -- filter given category
    AND wcs_item_sk = i_item_sk
    AND wcs_user_sk IS NOT NULL
    AND wcs_sales_sk IS NULL --only views, not purchases
  ) webInRange,
  (  -- store sales in date range with items from specified categories
    SELECT
      ss_customer_sk,
      ss_sold_date_sk
    FROM store_sales, item
    WHERE ss_sold_date_sk BETWEEN 37134 AND (37134 + 90) -- in the three consecutive months.
    AND i_category IN (${hiveconf:q12_i_category_IN}) -- filter given category 
    AND ss_item_sk = i_item_sk
    AND ss_customer_sk IS NOT NULL
  ) storeInRange
  -- join web and store
  WHERE wcs_user_sk = ss_customer_sk
  AND wcs_click_date_sk < ss_sold_date_sk -- buy AFTER viewed on website
  ORDER BY wcs_user_sk
  --CLUSTER BY instead of ORDER BY does not work to achieve global ordering. e.g. 2 reducers: first reducer will write keys 0,2,4,6.. into file 000000_0 and reducer 2 will write keys 1,3,5,7,.. into file 000000_1.concatenating these files does not produces a deterministic result if number of reducer changes.
  --Solution: parallel "order by" as non parallel version only uses a single reducer and we cant use "limit"
  ;
  