ambari-agent/src/examples/query_with3jobs.txt

SELECT 
   i_item_id,
   s_state, 
   avg(ss_quantity) agg1,
   avg(ss_list_price) agg2,
   avg(ss_coupon_amt) agg3,
   avg(ss_sales_price) agg4
FROM
   (SELECT /*+ MAPJOIN(item) */ i_item_id, s_state, ss_quantity, ss_list_price, ss_coupon_amt, ss_sales_price
   FROM item
   JOIN
      (SELECT /*+ MAPJOIN(customer_demographics) */ s_state, ss_quantity, ss_list_price, ss_coupon_amt, ss_sales_price, ss_item_sk
      FROM customer_demographics
      JOIN
         (SELECT /*+ MAPJOIN(store) */ s_state, ss_quantity, ss_list_price, ss_coupon_amt, ss_sales_price, ss_item_sk, ss_cdemo_sk
         FROM store
         JOIN
            (SELECT /*+ MAPJOIN(date_dim) */ ss_quantity, ss_list_price, ss_coupon_amt, ss_sales_price, ss_item_sk, ss_cdemo_sk, ss_store_sk
             FROM date_dim
             JOIN store_sales
	     ON (store_sales.ss_sold_date_sk = date_dim.d_date_sk) WHERE d_year = 2002) a
         ON (a.ss_store_sk = store.s_store_sk)
         WHERE s_state in ('TN', 'SD')) b
      ON (b.ss_cdemo_sk = customer_demographics.cd_demo_sk)
      WHERE
         cd_gender = 'M' and
         cd_marital_status = 'S' and
         cd_education_status = 'College') c
   ON (c.ss_item_sk = item.i_item_sk)) d
GROUP BY
   i_item_id,
   s_state
ORDER BY
   i_item_id,
   s_state
LIMIT 100;