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

   1 -- start query 1 in stream 0 using template query50.tpl and seed 1819994127
   2 select
   3    s_store_name
   4   ,s_company_id
   5   ,s_street_number
   6   ,s_street_name
   7   ,s_street_type
   8   ,s_suite_number
   9   ,s_city
  10   ,s_county
  11   ,s_state
  12   ,s_zip
  13   ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"
  14   ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
  15                  (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"
  16   ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
  17                  (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end)  as "61-90 days"
  18   ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
  19                  (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"
  20   ,sum(case when (sr_returned_date_sk - ss_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"
  21 from
  22    store_sales
  23   ,store_returns
  24   ,store
  25   ,date_dim d1
  26   ,date_dim d2
  27 where
  28     d2.d_year = 1998
  29 and d2.d_moy  = 9
  30 and ss_ticket_number = sr_ticket_number
  31 and ss_item_sk = sr_item_sk
  32 and ss_sold_date_sk   = d1.d_date_sk
  33 and sr_returned_date_sk   = d2.d_date_sk
  34 and ss_customer_sk = sr_customer_sk
  35 and ss_store_sk = s_store_sk
  36 group by
  37    s_store_name
  38   ,s_company_id
  39   ,s_street_number
  40   ,s_street_name
  41   ,s_street_type
  42   ,s_suite_number
  43   ,s_city
  44   ,s_county
  45   ,s_state
  46   ,s_zip
  47 order by s_store_name
  48         ,s_company_id
  49         ,s_street_number
  50         ,s_street_name
  51         ,s_street_type
  52         ,s_suite_number
  53         ,s_city
  54         ,s_county
  55         ,s_state
  56         ,s_zip
  57 limit 100;
  58
  59 -- end query 1 in stream 0 using template query50.tpl