diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query89.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query89.sql | 28 |
1 files changed, 28 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query89.sql b/src/s3select/TPCDS/sample-queries-tpcds/query89.sql new file mode 100644 index 000000000..3a275d4e0 --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query89.sql @@ -0,0 +1,28 @@ +-- start query 1 in stream 0 using template query89.tpl and seed 1719819282 +select * +from( +select i_category, i_class, i_brand, + s_store_name, s_company_name, + d_moy, + sum(ss_sales_price) sum_sales, + avg(sum(ss_sales_price)) over + (partition by i_category, i_brand, s_store_name, s_company_name) + avg_monthly_sales +from item, store_sales, date_dim, store +where ss_item_sk = i_item_sk and + ss_sold_date_sk = d_date_sk and + ss_store_sk = s_store_sk and + d_year in (2000) and + ((i_category in ('Home','Music','Books') and + i_class in ('glassware','classical','fiction') + ) + or (i_category in ('Jewelry','Sports','Women') and + i_class in ('semi-precious','baseball','dresses') + )) +group by i_category, i_class, i_brand, + s_store_name, s_company_name, d_moy) tmp1 +where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1 +order by sum_sales - avg_monthly_sales, s_store_name +limit 100; + +-- end query 1 in stream 0 using template query89.tpl |