resources/tpcds-v2.7.0/q6.sql

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 list of q6 in TPCDS v1.4 is below:
0021 -- order by cnt
0022 order by cnt, a.ca_state
0023 LIMIT 100