SELECT a.id, testgroup, testtime, user_age, overall_payments, partial_payments, avg_session_length, avg_num_sessions, num_actdays, crystals_spend, overall_complexity_covered, sum(coalesce(complexity,0)) as partial_complexity_covered
FROM ( SELECT a.id, testgroup, testtime, user_age, overall_payments, partial_payments, avg_session_length, avg_num_sessions, num_actdays, sum(coalesce(buy_value,0)) as crystals_spend
FROM ( SELECT a.id, testgroup, testtime, user_age, overall_payments, partial_payments, coalesce(avg(session_length/1.0),0) as avg_session_length, coalesce(cast(count(b.id) as real))/(case when count(distinct actdate)=0 or count(distinct actdate) is null then 1 else count(distinct actdate) end) as avg_num_sessions,
coalesce(count(distinct actdate),0) as num_actdays
FROM ( SELECT a.id, testgroup, testtime, testdate-regdate as user_age, coalesce(overall_payments,0) as overall_payments, sum(coalesce(real_value,0))/100.0 as partial_payments
FROM ( SELECT id, app_version as testgroup, utc_timestamp as testtime, date(TIMESTAMP 'epoch' + utc_timestamp * INTERVAL '1 second') as testdate
FROM ext_master.mghost_mcpu a WHERE app_version = '2.36' and socnet='2ios' and utc_timestamp > 1513122772 - 1800 and utc_timestamp < 1513122824 + 1800
and id not in (SELECT id FROM temp.mghost_ab_versions_371_36_payers) ) a
join ( SELECT *, date(TIMESTAMP 'epoch' + utc_timestamp * INTERVAL '1 second') as regdate FROM master.mghost_personal_info_new WHERE socnet='2ios' ) b on a.id = b.id
LEFT OUTER JOIN ( SELECT id, sum(real_value/100.0) as overall_payments
FROM master.mghost_payments WHERE payment_method not in ('offer_wall', 'fb_promotion') and socnet='2ios' GROUP BY 1 ) c on a.id = c.id
LEFT OUTER JOIN ( SELECT id, real_value, utc_timestamp as buytime FROM master.mghost_payments WHERE payment_method not in ('offer_wall', 'fb_promotion') and socnet='2ios' ) d on a.id = d.id and testtime-coalesce(buytime, 0)>=0 and testtime-coalesce(buytime, 100000000000000)<=86400*14 GROUP BY 1,2,3,4,5 ) a
LEFT OUTER JOIN ( SELECT id, session_length, session_start_time, actdate FROM master.mghost_sessions WHERE current_date-actdate<=14 and socnet='2ios' ) b on a.id = b.id and testtime-coalesce(session_start_time, 0)>=0 and testtime-coalesce(session_start_time, 100000000000000)<=86400*14 WHERE overall_payments > 0 GROUP BY 1,2,3,4,5,6 ) a
LEFT OUTER JOIN ( SELECT a.id, utc_timestamp as crtime, buy_value FROM ( SELECT id, item_count, md5(id+socnet+utc_timestamp+coalesce(source,'')+coalesce(subsource,'')+coalesce(subject,'')+coalesce(subsubject,'')+coalesce(balance,0)+item_count) as row_hash
FROM ext_master.mghost_hard_currency
WHERE current_date-actdate<=100 and event='spend' and item_count>0 and socnet='2ios' ) a j
oin ( SELECT * FROM ghosts_mobile.hard_currency_real_attr ) b on a.row_hash = b.row_hash ) b on a.id = b.id and testtime-coalesce(crtime, 0)>=0 and testtime-coalesce(crtime, 100000000000000)<=86400*14 GROUP BY 1,2,3,4,5,6,7,8,9 ) a
LEFT OUTER JOIN ( SELECT a.id, complexity, utc_timestamp as mtime FROM ext_master.mghost_content_conversion a
join ( SELECT * FROM ghosts_mobile.mghost_clusters WHERE monetization_flag=1) b on a.content_name=b.source WHERE event='finish' and current_date-actdate<=30 and socnet='2ios' ) b on a.id = b.id and testtime-coalesce(mtime, 0)>=0 and testtime-coalesce(mtime, 100000000000000)<=86400*14
LEFT OUTER JOIN ( SELECT a.id, sum(complexity) as overall_complexity_covered FROM ext_master.mghost_content_conversion a
join ( SELECT * FROM ghosts_mobile.mghost_clusters WHERE monetization_flag=1) b on a.content_name=b.source WHERE event='finish' and socnet='2ios' GROUP BY a.id ) c on a.id = c.id GROUP BY 1,2,3,4,5,6,7,8,9,10,11