| ==== |
| ---- QUERY: TPCDS-Q31 |
| |
| with ss as |
| (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales |
| from store_sales,date_dim,customer_address |
| where ss_sold_date_sk = d_date_sk |
| and ss_addr_sk=ca_address_sk |
| group by ca_county,d_qoy, d_year), |
| ws as |
| (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales |
| from web_sales,date_dim,customer_address |
| where ws_sold_date_sk = d_date_sk |
| and ws_bill_addr_sk=ca_address_sk |
| group by ca_county,d_qoy, d_year) |
| select |
| ss1.ca_county |
| ,ss1.d_year |
| ,ws2.web_sales/ws1.web_sales web_q1_q2_increase |
| ,ss2.store_sales/ss1.store_sales store_q1_q2_increase |
| ,ws3.web_sales/ws2.web_sales web_q2_q3_increase |
| ,ss3.store_sales/ss2.store_sales store_q2_q3_increase |
| from |
| ss ss1 |
| ,ss ss2 |
| ,ss ss3 |
| ,ws ws1 |
| ,ws ws2 |
| ,ws ws3 |
| where |
| ss1.d_qoy = 1 |
| and ss1.d_year = 2000 |
| and ss1.ca_county = ss2.ca_county |
| and ss2.d_qoy = 2 |
| and ss2.d_year = 2000 |
| and ss2.ca_county = ss3.ca_county |
| and ss3.d_qoy = 3 |
| and ss3.d_year = 2000 |
| and ss1.ca_county = ws1.ca_county |
| and ws1.d_qoy = 1 |
| and ws1.d_year = 2000 |
| and ws1.ca_county = ws2.ca_county |
| and ws2.d_qoy = 2 |
| and ws2.d_year = 2000 |
| and ws1.ca_county = ws3.ca_county |
| and ws3.d_qoy = 3 |
| and ws3.d_year =2000 |
| and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end |
| > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end |
| and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end |
| > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end |
| order by ss1.ca_county; |
| ---- RESULTS |
| 'Atchison County',2000,0.800221,0.242964,11.944560,3.162954 |
| 'Bacon County',2000,1.168826,0.394038,0.968800,0.510743 |
| 'Bourbon County',2000,1.913115,0.981928,3.364802,1.380123 |
| 'Boyd County',2000,1.086324,0.811507,1.168972,0.742057 |
| 'Bradley County',2000,1.489632,0.575785,1.344390,0.998919 |
| 'Buchanan County',2000,1.195667,0.746049,3.332904,2.239788 |
| 'Carter County',2000,3.953739,1.151033,2.119285,1.844491 |
| 'Cass County',2000,2.398720,1.190582,2.257169,0.847801 |
| 'Corson County',2000,0.560309,0.175081,4.807409,3.227141 |
| 'Crockett County',2000,1.637106,0.360468,2.134042,1.832451 |
| 'Culpeper County',2000,0.661751,0.619014,1.659248,1.224169 |
| 'Edmonson County',2000,0.732442,0.299753,1.602897,1.491246 |
| 'Ferry County',2000,0.701373,0.341010,4.002919,2.603464 |
| 'Fillmore County',2000,0.507769,0.349921,2.443162,1.301136 |
| 'Forest County',2000,0.644745,0.342367,5.771027,1.881074 |
| 'Gaston County',2000,0.763774,0.455496,3.955611,2.141572 |
| 'Grant County',2000,0.693336,0.622870,1.788644,1.722197 |
| 'Green County',2000,0.763667,0.321499,4.694152,4.207034 |
| 'Harlan County',2000,1.670355,1.590115,2.471959,2.107294 |
| 'Harris County',2000,2.337569,0.333126,2.417058,1.025794 |
| 'Heard County',2000,4.102554,1.266947,3.500228,1.127804 |
| 'Houston County',2000,2.045326,1.038983,1.965051,1.421193 |
| 'Ingham County',2000,0.574390,0.384114,1.306517,0.992061 |
| 'Lake County',2000,1.255077,0.745928,1.515147,1.265662 |
| 'Lamar County',2000,0.749358,0.745617,4.269036,2.029584 |
| 'Lincoln County',2000,1.019137,0.944845,2.335960,1.776928 |
| 'Marion County',2000,1.158999,0.916511,2.445040,1.851073 |
| 'Mercer County',2000,0.738342,0.601678,3.012450,2.724470 |
| 'Meriwether County',2000,0.365748,0.300417,2.772270,0.787769 |
| 'Miller County',2000,2.575745,1.318273,2.191968,0.982218 |
| 'Mitchell County',2000,4.439233,1.161335,1.394036,1.256074 |
| 'Mora County',2000,1.183261,0.635654,2.513120,0.918567 |
| 'Nantucket County',2000,1.437897,0.722692,1.175488,0.962304 |
| 'New Kent County',2000,0.602616,0.399062,2.869386,2.625895 |
| 'Nicholas County',2000,2.165116,2.056274,6.021300,1.262577 |
| 'Otero County',2000,2.754655,1.246299,2.976111,2.245835 |
| 'Oxford County',2000,0.973143,0.757240,4.012687,1.640751 |
| 'Perry County',2000,1.580780,0.764453,2.153370,1.802410 |
| 'Prince William County',2000,3.376373,0.630787,1.706967,0.934324 |
| 'Refugio County',2000,1.812976,0.586732,1.301984,1.269604 |
| 'Rice County',2000,1.134698,0.733018,2.378149,1.986402 |
| 'Richmond County',2000,1.571665,1.294015,2.309592,1.778016 |
| 'Sheridan County',2000,1.386030,1.250657,1.575936,0.537886 |
| 'Smith County',2000,0.636936,0.427882,5.744845,4.477958 |
| 'Stark County',2000,7.338220,1.417589,1.863840,1.227366 |
| 'Steele County',2000,1.377413,0.766513,1.247973,0.931195 |
| 'Stone County',2000,1.900042,0.811920,3.699361,1.521662 |
| 'Tooele County',2000,6.590303,0.768910,1.788637,0.340067 |
| 'Vernon County',2000,0.974454,0.915946,1.368804,1.041712 |
| 'Williamson County',2000,2.985102,0.391418,5.805965,4.396699 |
| 'Wright County',2000,5.029335,1.970810,4.076529,1.966472 |
| ---- TYPES |
| STRING, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL |
| ==== |
