| ==== |
| ---- QUERY: TPCDS-Q1 |
| with customer_total_return as |
| (select sr_customer_sk as ctr_customer_sk, |
| sr_store_sk as ctr_store_sk, |
| sum(SR_RETURN_AMT) as ctr_total_return |
| from store_returns, |
| date_dim |
| where sr_returned_date_sk = d_date_sk |
| and d_year = 2000 |
| group by sr_customer_sk, |
| sr_store_sk) |
| select c_customer_id |
| from customer_total_return ctr1, |
| store, |
| customer |
| where ctr1.ctr_total_return > |
| (select avg(ctr_total_return)*1.2 |
| from customer_total_return ctr2 |
| where ctr1.ctr_store_sk = ctr2.ctr_store_sk) |
| and s_store_sk = ctr1.ctr_store_sk |
| and s_state = 'TN' |
| and ctr1.ctr_customer_sk = c_customer_sk |
| order by c_customer_id |
| limit 100; |
| ---- RESULTS |
| 'AAAAAAAAAAABBAAA' |
| 'AAAAAAAAAAADBAAA' |
| 'AAAAAAAAAAADBAAA' |
| 'AAAAAAAAAAAKAAAA' |
| 'AAAAAAAAAABDAAAA' |
| 'AAAAAAAAAABHBAAA' |
| 'AAAAAAAAAABLAAAA' |
| 'AAAAAAAAAABMAAAA' |
| 'AAAAAAAAAACHAAAA' |
| 'AAAAAAAAAACMAAAA' |
| 'AAAAAAAAAADDAAAA' |
| 'AAAAAAAAAADGAAAA' |
| 'AAAAAAAAAADGBAAA' |
| 'AAAAAAAAAADGBAAA' |
| 'AAAAAAAAAADPAAAA' |
| 'AAAAAAAAAAEBAAAA' |
| 'AAAAAAAAAAEFBAAA' |
| 'AAAAAAAAAAEGBAAA' |
| 'AAAAAAAAAAEIAAAA' |
| 'AAAAAAAAAAEMAAAA' |
| 'AAAAAAAAAAFAAAAA' |
| 'AAAAAAAAAAFPAAAA' |
| 'AAAAAAAAAAGGBAAA' |
| 'AAAAAAAAAAGHBAAA' |
| 'AAAAAAAAAAGJAAAA' |
| 'AAAAAAAAAAGMAAAA' |
| 'AAAAAAAAAAHEBAAA' |
| 'AAAAAAAAAAHFBAAA' |
| 'AAAAAAAAAAIEBAAA' |
| 'AAAAAAAAAAJGBAAA' |
| 'AAAAAAAAAAJHBAAA' |
| 'AAAAAAAAAAKCAAAA' |
| 'AAAAAAAAAAKCAAAA' |
| 'AAAAAAAAAAKJAAAA' |
| 'AAAAAAAAAAKMAAAA' |
| 'AAAAAAAAAAKMAAAA' |
| 'AAAAAAAAAALAAAAA' |
| 'AAAAAAAAAALABAAA' |
| 'AAAAAAAAAALGAAAA' |
| 'AAAAAAAAAALHBAAA' |
| 'AAAAAAAAAALJAAAA' |
| 'AAAAAAAAAANHAAAA' |
| 'AAAAAAAAAANHBAAA' |
| 'AAAAAAAAAANJAAAA' |
| 'AAAAAAAAAANMAAAA' |
| 'AAAAAAAAAANMAAAA' |
| 'AAAAAAAAAANNAAAA' |
| 'AAAAAAAAAAOBBAAA' |
| 'AAAAAAAAAAODBAAA' |
| 'AAAAAAAAAAOLAAAA' |
| 'AAAAAAAAAAPGBAAA' |
| 'AAAAAAAAABAAAAAA' |
| 'AAAAAAAAABAEAAAA' |
| 'AAAAAAAAABAEBAAA' |
| 'AAAAAAAAABAFBAAA' |
| 'AAAAAAAAABAIAAAA' |
| 'AAAAAAAAABAOAAAA' |
| 'AAAAAAAAABBDBAAA' |
| 'AAAAAAAAABCFAAAA' |
| 'AAAAAAAAABCHBAAA' |
| 'AAAAAAAAABDHAAAA' |
| 'AAAAAAAAABENAAAA' |
| 'AAAAAAAAABFEBAAA' |
| 'AAAAAAAAABFGAAAA' |
| 'AAAAAAAAABFMAAAA' |
| 'AAAAAAAAABFPAAAA' |
| 'AAAAAAAAABGFAAAA' |
| 'AAAAAAAAABGFBAAA' |
| 'AAAAAAAAABGJAAAA' |
| 'AAAAAAAAABIBBAAA' |
| 'AAAAAAAAABICBAAA' |
| 'AAAAAAAAABIIAAAA' |
| 'AAAAAAAAABJNAAAA' |
| 'AAAAAAAAABKGBAAA' |
| 'AAAAAAAAABLOAAAA' |
| 'AAAAAAAAABLPAAAA' |
| 'AAAAAAAAABMABAAA' |
| 'AAAAAAAAABMPAAAA' |
| 'AAAAAAAAABNAAAAA' |
| 'AAAAAAAAABNCBAAA' |
| 'AAAAAAAAABNEBAAA' |
| 'AAAAAAAAABNLAAAA' |
| 'AAAAAAAAABNOAAAA' |
| 'AAAAAAAAABNPAAAA' |
| 'AAAAAAAAABOAAAAA' |
| 'AAAAAAAAABOFBAAA' |
| 'AAAAAAAAABOOAAAA' |
| 'AAAAAAAAABOPAAAA' |
| 'AAAAAAAAABPEAAAA' |
| 'AAAAAAAAACADAAAA' |
| 'AAAAAAAAACAFAAAA' |
| 'AAAAAAAAACAFAAAA' |
| 'AAAAAAAAACAHBAAA' |
| 'AAAAAAAAACAJAAAA' |
| 'AAAAAAAAACBDAAAA' |
| 'AAAAAAAAACBDAAAA' |
| 'AAAAAAAAACBEBAAA' |
| 'AAAAAAAAACBNAAAA' |
| 'AAAAAAAAACBPAAAA' |
| 'AAAAAAAAACCHAAAA' |
| ---- TYPES |
| STRING |
| ==== |