| ==== |
| ---- QUERY: TPCDS-Q6 |
| select a.ca_state state, count(*) cnt |
| from customer_address a, |
| customer c, |
| store_sales s, |
| date_dim d, |
| item i |
| where a.ca_address_sk = c.c_current_addr_sk |
| and c.c_customer_sk = s.ss_customer_sk |
| and s.ss_sold_date_sk = d.d_date_sk |
| and s.ss_item_sk = i.i_item_sk |
| and d.d_month_seq = |
| (select distinct (d_month_seq) |
| from date_dim |
| where d_year = 2001 |
| and d_moy = 1 |
| limit 1) |
| and i.i_current_price > 1.2 * |
| (select avg(j.i_current_price) |
| from item j |
| where j.i_category = i.i_category) |
| group by a.ca_state |
| having count(*) >= 10 |
| order by 2,1 |
| limit 100 |
| ---- RESULTS: VERIFY_IS_EQUAL_SORTED |
| 'VT',14 |
| 'ME',16 |
| 'NJ',18 |
| 'MD',19 |
| 'AK',21 |
| 'NH',21 |
| 'MA',24 |
| 'NV',26 |
| 'OR',27 |
| 'ND',28 |
| 'UT',31 |
| 'SD',35 |
| 'ID',36 |
| 'NM',36 |
| 'WV',45 |
| 'WA',46 |
| 'SC',47 |
| 'CO',48 |
| 'AL',49 |
| 'FL',49 |
| 'LA',49 |
| 'NY',52 |
| 'WI',53 |
| 'MT',55 |
| 'PA',56 |
| 'OK',57 |
| 'CA',60 |
| 'MS',69 |
| 'NULL',73 |
| 'AR',73 |
| 'TN',74 |
| 'IN',76 |
| 'KS',80 |
| 'MN',81 |
| 'MI',83 |
| 'MO',83 |
| 'NE',87 |
| 'IA',96 |
| 'OH',102 |
| 'KY',103 |
| 'IL',106 |
| 'NC',107 |
| 'VA',137 |
| 'GA',153 |
| 'TX',228 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |