gistfile1.txt

----views between boosts
drop table lushpina.y_511_views_between_boosts;
create table lushpina.y_511_views_between_boosts as
select
dt1,
from_unixtime(unix_timestamp(date_created, 'yyyy-MM-dd hh:mm:ss.0'), 'EEEE') week_day_created,
y_511_tmp_lagged_boosts_w_all_dates.product_id,
seller_type,
floor((unix_timestamp(event_timestamp, 'yyyy-MM-dd hh:mm:ss.0')-unix_timestamp(boost, 'yyyy-MM-dd hh:mm:ss.0'))/60/60) diff_view_after_boost_hours,
floor((unix_timestamp(event_timestamp, 'yyyy-MM-dd hh:mm:ss.0')-unix_timestamp(date_created, 'yyyy-MM-dd hh:mm:ss.0'))/60/60/24) diff_view_after_create_days,
subcat_txt,
cat_txt,
subcategory_id,
case when boost_products.product_id is not NULL then 'boost' else 'no_boost' end boost_flag,
sum(case when event_timestamp>=boost and event_timestamp<boost_lag2 then coalesce(cast(is_prod_view as int),0) else null end) cnt_views

from
lushpina.y_511_tmp_lagged_boosts_w_all_dates
left join (select distinct product_id from y_511_boosts) boost_products
    on boost_products.product_id=y_511_tmp_lagged_boosts_w_all_dates.product_id
left join lushpina.y_511_views
    on y_511_views.product_id=y_511_tmp_lagged_boosts_w_all_dates.product_id 
where to_date(date_created) >='2017-07-08' and 
   cast(from_unixtime(unix_timestamp(y_511_views.event_timestamp, 'yyyy-MM-dd hh:mm:ss.0')) as timestamp) between boost and boost_lag2
group by 
dt1,
from_unixtime(unix_timestamp(date_created, 'yyyy-MM-dd hh:mm:ss.0'), 'EEEE'),
y_511_tmp_lagged_boosts_w_all_dates.product_id,
seller_type,
floor((unix_timestamp(event_timestamp, 'yyyy-MM-dd hh:mm:ss.0')-unix_timestamp(boost, 'yyyy-MM-dd hh:mm:ss.0'))/60/60)  ,
floor((unix_timestamp(event_timestamp, 'yyyy-MM-dd hh:mm:ss.0')-unix_timestamp(date_created, 'yyyy-MM-dd hh:mm:ss.0'))/60/60/24) ,
subcat_txt,
cat_txt,
subcategory_id,
case when boost_products.product_id is not NULL then 'boost' else 'no_boost' end