diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query72.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query72.sql | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query72.sql b/src/s3select/TPCDS/sample-queries-tpcds/query72.sql new file mode 100644 index 000000000..9ac928d60 --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query72.sql @@ -0,0 +1,29 @@ +-- start query 1 in stream 0 using template query72.tpl and seed 2031708268 +select i_item_desc + ,w_warehouse_name + ,d1.d_week_seq + ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo + ,sum(case when p_promo_sk is not null then 1 else 0 end) promo + ,count(*) total_cnt +from catalog_sales +join inventory on (cs_item_sk = inv_item_sk) +join warehouse on (w_warehouse_sk=inv_warehouse_sk) +join item on (i_item_sk = cs_item_sk) +join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) +join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) +join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) +join date_dim d2 on (inv_date_sk = d2.d_date_sk) +join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) +left outer join promotion on (cs_promo_sk=p_promo_sk) +left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) +where d1.d_week_seq = d2.d_week_seq + and inv_quantity_on_hand < cs_quantity + and d3.d_date > d1.d_date + INTERVAL '5' DAY + and hd_buy_potential = '1001-5000' + and d1.d_year = 2000 + and cd_marital_status = 'D' +group by i_item_desc,w_warehouse_name,d1.d_week_seq +order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq +limit 100; + +-- end query 1 in stream 0 using template query72.tpl |