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

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