]>
git.proxmox.com Git - ceph.git/blob - ceph/src/s3select/TPCDS/sample-queries-tpcds/query72.sql
1 -- start query 1 in stream 0 using template query72.tpl and seed 2031708268
5 ,sum(case when p_promo_sk
is null then 1 else 0 end) no_promo
6 ,sum(case when p_promo_sk
is not null then 1 else 0 end) promo
9 join inventory
on (cs_item_sk
= inv_item_sk
)
10 join warehouse
on (w_warehouse_sk
=inv_warehouse_sk
)
11 join item
on (i_item_sk
= cs_item_sk
)
12 join customer_demographics
on (cs_bill_cdemo_sk
= cd_demo_sk
)
13 join household_demographics
on (cs_bill_hdemo_sk
= hd_demo_sk
)
14 join date_dim d1
on (cs_sold_date_sk
= d1.d_date_sk
)
15 join date_dim d2
on (inv_date_sk
= d2.d_date_sk
)
16 join date_dim d3
on (cs_ship_date_sk
= d3.d_date_sk
)
17 left outer join promotion
on (cs_promo_sk
=p_promo_sk
)
18 left outer join catalog_returns
on (cr_item_sk
= cs_item_sk
and cr_order_number
= cs_order_number
)
19 where d1.d_week_seq
= d2.d_week_seq
20 and inv_quantity_on_hand
< cs_quantity
21 and d3.d_date
> d1.d_date
+ INTERVAL '5' DAY
22 and hd_buy_potential
= '1001-5000'
24 and cd_marital_status
= 'D'
25 group by i_item_desc
,w_warehouse_name
,d1.d_week_seq
26 order by total_cnt
desc, i_item_desc
, w_warehouse_name
, d_week_seq
29 -- end query 1 in stream 0 using template query72.tpl