ceph/src/s3select/TPCDS/sample-queries-tpcds/query56.sql

   1 -- start query 1 in stream 0 using template query56.tpl and seed 1951559352
   2 with ss as (
   3  select i_item_id,sum(ss_ext_sales_price) total_sales
   4  from
   5         store_sales,
   6         date_dim,
   7          customer_address,
   8          item
   9  where i_item_id in (select
  10      i_item_id
  11 from item
  12 where i_color in ('chiffon','smoke','lace'))
  13  and     ss_item_sk              = i_item_sk
  14  and     ss_sold_date_sk         = d_date_sk
  15  and     d_year                  = 2001
  16  and     d_moy                   = 5
  17  and     ss_addr_sk              = ca_address_sk
  18  and     ca_gmt_offset           = -6
  19  group by i_item_id),
  20  cs as (
  21  select i_item_id,sum(cs_ext_sales_price) total_sales
  22  from
  23         catalog_sales,
  24         date_dim,
  25          customer_address,
  26          item
  27  where
  28          i_item_id               in (select
  29   i_item_id
  30 from item
  31 where i_color in ('chiffon','smoke','lace'))
  32  and     cs_item_sk              = i_item_sk
  33  and     cs_sold_date_sk         = d_date_sk
  34  and     d_year                  = 2001
  35  and     d_moy                   = 5
  36  and     cs_bill_addr_sk         = ca_address_sk
  37  and     ca_gmt_offset           = -6
  38  group by i_item_id),
  39  ws as (
  40  select i_item_id,sum(ws_ext_sales_price) total_sales
  41  from
  42         web_sales,
  43         date_dim,
  44          customer_address,
  45          item
  46  where
  47          i_item_id               in (select
  48   i_item_id
  49 from item
  50 where i_color in ('chiffon','smoke','lace'))
  51  and     ws_item_sk              = i_item_sk
  52  and     ws_sold_date_sk         = d_date_sk
  53  and     d_year                  = 2001
  54  and     d_moy                   = 5
  55  and     ws_bill_addr_sk         = ca_address_sk
  56  and     ca_gmt_offset           = -6
  57  group by i_item_id)
  58   select  i_item_id ,sum(total_sales) total_sales
  59  from  (select * from ss
  60         union all
  61         select * from cs
  62         union all
  63         select * from ws) tmp1
  64  group by i_item_id
  65  order by total_sales,
  66           i_item_id
  67  limit 100;
  68
  69 -- end query 1 in stream 0 using template query56.tpl