0001 SELECT
0002 c_last_name,
0003 c_first_name,
0004 substr(s_city, 1, 30),
0005 ss_ticket_number,
0006 amt,
0007 profit
0008 FROM
0009 (SELECT
0010 ss_ticket_number,
0011 ss_customer_sk,
0012 store.s_city,
0013 sum(ss_coupon_amt) amt,
0014 sum(ss_net_profit) profit
0015 FROM store_sales, date_dim, store, household_demographics
0016 WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
0017 AND store_sales.ss_store_sk = store.s_store_sk
0018 AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
0019 AND (household_demographics.hd_dep_count = 6 OR
0020 household_demographics.hd_vehicle_count > 2)
0021 AND date_dim.d_dow = 1
0022 AND date_dim.d_year IN (1999, 1999 + 1, 1999 + 2)
0023 AND store.s_number_employees BETWEEN 200 AND 295
0024 GROUP BY ss_ticket_number, ss_customer_sk, ss_addr_sk, store.s_city) ms, customer
0025 WHERE ss_customer_sk = c_customer_sk
0026 ORDER BY c_last_name, c_first_name, substr(s_city, 1, 30), profit
0027 LIMIT 100
