0001 WITH ssales AS
0002 (SELECT
0003 c_last_name,
0004 c_first_name,
0005 s_store_name,
0006 ca_state,
0007 s_state,
0008 i_color,
0009 i_current_price,
0010 i_manager_id,
0011 i_units,
0012 i_size,
0013 sum(ss_net_paid) netpaid
0014 FROM store_sales, store_returns, store, item, customer, customer_address
0015 WHERE ss_ticket_number = sr_ticket_number
0016 AND ss_item_sk = sr_item_sk
0017 AND ss_customer_sk = c_customer_sk
0018 AND ss_item_sk = i_item_sk
0019 AND ss_store_sk = s_store_sk
0020 AND c_birth_country = upper(ca_country)
0021 AND s_zip = ca_zip
0022 AND s_market_id = 8
0023 GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state, i_color,
0024 i_current_price, i_manager_id, i_units, i_size)
0025 SELECT
0026 c_last_name,
0027 c_first_name,
0028 s_store_name,
0029 sum(netpaid) paid
0030 FROM ssales
0031 WHERE i_color = 'pale'
0032 GROUP BY c_last_name, c_first_name, s_store_name
0033 HAVING sum(netpaid) > (SELECT 0.05 * avg(netpaid)
0034 FROM ssales)
