-- start query 53 in stream 0 using template query29.tpl
select   
     i_item_id
    ,i_item_desc
    ,s_store_id
    ,s_store_name
    ,min(ss_quantity)        as store_sales_quantity
    ,min(sr_return_quantity) as store_returns_quantity
    ,min(cs_quantity)        as catalog_sales_quantity
 from
    bigdata_public_dataset.tpcds_1g.store_sales
   ,bigdata_public_dataset.tpcds_1g.store_returns
   ,bigdata_public_dataset.tpcds_1g.catalog_sales
   ,bigdata_public_dataset.tpcds_1g.date_dim             d1
   ,bigdata_public_dataset.tpcds_1g.date_dim             d2
   ,bigdata_public_dataset.tpcds_1g.date_dim             d3
   ,bigdata_public_dataset.tpcds_1g.store
   ,bigdata_public_dataset.tpcds_1g.item
 where
     d1.d_moy               = 4 
 and d1.d_year              = 1998
 and d1.d_date_sk           = ss_sold_date_sk
 and i_item_sk              = ss_item_sk
 and s_store_sk             = ss_store_sk
 and ss_customer_sk         = sr_customer_sk
 and ss_item_sk             = sr_item_sk
 and ss_ticket_number       = sr_ticket_number
 and sr_returned_date_sk    = d2.d_date_sk
 and d2.d_moy               between 4 and  4 + 3 
 and d2.d_year              = 1998
 and sr_customer_sk         = cs_bill_customer_sk
 and sr_item_sk             = cs_item_sk
 and cs_sold_date_sk        = d3.d_date_sk     
 and d3.d_year              in (1998,1998+1,1998+2)
 group by
    i_item_id
   ,i_item_desc
   ,s_store_id
   ,s_store_name
 order by
    i_item_id 
   ,i_item_desc
   ,s_store_id
   ,s_store_name
 limit 100;

-- end query 53 in stream 0 using template query29.tpl
