0001 SELECT
0002 a.ca_state state,
0003 count(*) cnt
0004 FROM
0005 customer_address a, customer c, store_sales s, date_dim d, item i
0006 WHERE a.ca_address_sk = c.c_current_addr_sk
0007 AND c.c_customer_sk = s.ss_customer_sk
0008 AND s.ss_sold_date_sk = d.d_date_sk
0009 AND s.ss_item_sk = i.i_item_sk
0010 AND d.d_month_seq =
0011 (SELECT DISTINCT (d_month_seq)
0012 FROM date_dim
0013 WHERE d_year = 2000 AND d_moy = 1)
0014 AND i.i_current_price > 1.2 *
0015 (SELECT avg(j.i_current_price)
0016 FROM item j
0017 WHERE j.i_category = i.i_category)
0018 GROUP BY a.ca_state
0019 HAVING count(*) >= 10
0020 ORDER BY cnt
0021 LIMIT 100