0001 WITH customer_total_return AS
0002 ( SELECT
0003 sr_customer_sk AS ctr_customer_sk,
0004 sr_store_sk AS ctr_store_sk,
0005 sum(sr_return_amt) AS ctr_total_return
0006 FROM store_returns, date_dim
0007 WHERE sr_returned_date_sk = d_date_sk AND d_year = 2000
0008 GROUP BY sr_customer_sk, sr_store_sk)
0009 SELECT c_customer_id
0010 FROM customer_total_return ctr1, store, customer
0011 WHERE ctr1.ctr_total_return >
0012 (SELECT avg(ctr_total_return) * 1.2
0013 FROM customer_total_return ctr2
0014 WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
0015 AND s_store_sk = ctr1.ctr_store_sk
0016 AND s_state = 'TN'
0017 AND ctr1.ctr_customer_sk = c_customer_sk
0018 ORDER BY c_customer_id
0019 LIMIT 100
