insert overwrite directory "/mapredOutput/UserYesterdayInterest/${hiveconf:day}"
row format delimited fields terminated by "\t"   
select 
s.userid,
round(sum((s.totaloninvestedshare * i.yield/100 + s.addshare * s.addyield/100)/365),2) as yesterdayInterest,
unix_timestamp() as day
from 
zx_standard_statistics s 
left join zx_accounts a 
on s.userid = a.userid
left join zx_standard_informations i 
on s.bidno = i.bidno
where 
a.totaloninvest > 0.00 and   
s.totaloninvestedshare > 0 and
i.bidtype <> 'GREEN' and
i.startdate < unix_timestamp() 
group by s.userid
;