tpcds.query50.sql Maven / Gradle / Ivy

Go to download
select s_store_name
     , s_company_id
     , s_street_number
     , s_street_name
     , s_street_type
     , s_suite_number
     , s_city
     , s_county
     , s_state
     , s_zip
     , sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30) then 1 else 0 end) as "30_days"
     , sum(case
               when (sr_returned_date_sk - ss_sold_date_sk > 30) and
                    (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1
               else 0 end)                                                            as "31_60_days"
     , sum(case
               when (sr_returned_date_sk - ss_sold_date_sk > 60) and
                    (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1
               else 0 end)                                                            as "61_90_days"
     , sum(case
               when (sr_returned_date_sk - ss_sold_date_sk > 90) and
                    (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1
               else 0 end)                                                            as "91_120_days"
     , sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as above120_days
from store_sales
   , store_returns
   , store
   , date_dim d1
   , date_dim d2
where d2.d_year = 2001
  and d2.d_moy = 8
  and ss_ticket_number = sr_ticket_number
  and ss_item_sk = sr_item_sk
  and ss_sold_date_sk = d1.d_date_sk
  and sr_returned_date_sk = d2.d_date_sk
  and ss_customer_sk = sr_customer_sk
  and ss_store_sk = s_store_sk
group by s_store_name
       , s_company_id
       , s_street_number
       , s_street_name
       , s_street_type
       , s_suite_number
       , s_city
       , s_county
       , s_state
       , s_zip
order by s_store_name
       , s_company_id
       , s_street_number
       , s_street_name
       , s_street_type
       , s_suite_number
       , s_city
       , s_county
       , s_state
       , s_zip limit 100