Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table ghosts_mobile.versions_comparison as select
- a.id,
- a.socnet,
- coalesce(traffic_group,
- 'unknown') as traffic_group,
- coalesce(country_group,
- 'other') as country_group,
- coalesce(monetary_type,
- '(0) not payer') as monetary_type,
- a.version,
- days_since_move,
- a.is_new,
- a.comparison_type,
- timestamp 'epoch' + move_time * interval '1 second' as move_ts,
- a.move_date,
- ram,
- cpu,
- screen_resolution,
- payments,
- offer_bank_payments,
- offer_sale_payments,
- transactions,
- offer_bank_transactions,
- offer_sale_transactions,
- mquests_finish,
- complexity_covered,
- grzone_entries,
- sessions_num,
- sessions_time,
- session_1_length,
- session_2_flag,
- session_3_flag,
- ret_1d_flag,
- ret_2d_flag,
- ret_5d_flag,
- ret_7d_flag,
- rare_crafts,
- bank_open_flag,
- bank_accrual_flag,
- ad_views,
- click_on_fay_flag,
- fay_arrived_flag,
- ml_starts,
- sq_starts,
- tcsq_starts,
- sq_finishes,
- sq_prolongs,
- sq_forgets,
- sq_retries,
- sq_restarts,
- load_1_time,
- load_2_time,
- load_other_time,
- offer_bank_starts,
- offer_sale_starts,
- last_house_start,
- gfl_receive_flag,
- gfl_use_flag,
- first_map_load_time
- from
- ( select
- id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date,
- is_new,
- version,
- comparison_type,
- sum(real_value)/100.0 as payments,
- sum(case when payment_type='offer' and (offer_name like '%mult_%' or offer_name like '%nopayer%') then real_value else 0 end)/100.0 as offer_bank_payments,
- sum(case when payment_type='offer' and (offer_name like '%sale_%' or offer_name like '%event%') then real_value else 0 end)/100.0 as offer_sale_payments,
- count(id) as transactions,
- count(case when payment_type='offer' and (offer_name like '%mult_%' or offer_name like '%nopayer%') then id else null end) as offer_bank_transactions,
- count(case when payment_type='offer' and (offer_name like '%sale_%' or offer_name like '%event%') then id else null end) as offer_sale_transactions,
- 0 mquests_finish,
- 0 complexity_covered,
- 0 grzone_entries,
- 0 sessions_num,
- 0 sessions_time,
- 0 session_1_length,
- 0 session_2_flag,
- 0 session_3_flag,
- 0 ret_1d_flag,
- 0 ret_2d_flag,
- 0 ret_5d_flag,
- 0 ret_7d_flag,
- 0 rare_crafts,
- 0 bank_open_flag,
- 0 bank_accrual_flag,
- 0 ad_views,
- 0 click_on_fay_flag,
- 0 fay_arrived_flag,
- 0 ml_starts,
- 0 sq_starts,
- 0 tcsq_starts,
- 0 sq_finishes,
- 0 sq_prolongs,
- 0 sq_forgets,
- 0 sq_retries,
- 0 sq_restarts,
- 0 load_1_time,
- 0 load_2_time,
- 0 load_other_time,
- 0 offer_bank_starts,
- 0 offer_sale_starts,
- 0 last_house_start,
- 0 gfl_receive_flag,
- 0 gfl_use_flag,
- 0 first_map_load_time
- from master.mghost_payments natural join ghosts_mobile.versions_id
- where current_date-actdate<=40
- group by
- 1,
- 2,
- 3,
- 4,
- 5,
- 6,
- 7
- union
- all select
- id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date,
- is_new,
- version,
- comparison_type,
- 0 payments,
- 0 offer_bank_payments,
- 0 offer_sale_payments,
- 0 transactions,
- 0 offer_bank_transactions,
- 0 offer_sale_transactions,
- count(case when event = 'finish' and mf = 1 then id else null end) mquests_finish,
- sum(case when event = 'finish' and mf = 1 then complexity else null end) complexity_covered,
- sum(case when event = 'enter' then 1 else 0 end) grzone_entries,
- 0 sessions_num,
- 0 sessions_time,
- 0 session_1_length,
- 0 session_2_flag,
- 0 session_3_flag,
- 0 ret_1d_flag,
- 0 ret_2d_flag,
- 0 ret_5d_flag,
- 0 ret_7d_flag,
- 0 rare_crafts,
- 0 bank_open_flag,
- 0 bank_accrual_flag,
- 0 ad_views,
- 0 click_on_fay_flag,
- 0 fay_arrived_flag,
- 0 ml_starts,
- 0 sq_starts,
- 0 tcsq_starts,
- 0 sq_finishes,
- 0 sq_prolongs,
- 0 sq_forgets,
- 0 sq_retries,
- 0 sq_restarts,
- 0 load_1_time,
- 0 load_2_time,
- 0 load_other_time,
- 0 offer_bank_starts,
- 0 offer_sale_starts,
- 0 last_house_start,
- 0 gfl_receive_flag,
- 0 gfl_use_flag,
- 0 first_map_load_time
- from (select * from ext_master.mghost_content_conversion where current_date-actdate<=40) a
- natural
- join
- ghosts_mobile.versions_id
- left outer join
- (select
- distinct source AS content_name,
- monetization_flag AS mf,
- complexity
- from
- ghosts_mobile.mghost_clusters) b
- on a.content_name = b.content_name
- where
- current_date-actdate<=40
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((session_start_time-move_time+300)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, count(session_length) as sessions_num, sum(session_length) as sessions_time, max(case
- when session_number = 1 then session_length
- else null
- end) as session_1_length, max(case
- when session_number = 2 then 1
- else 0
- end) as session_2_flag, max(case
- when session_number = 3 then 1
- else 0
- end) as session_3_flag, max(case
- when floor((session_start_time-move_time+300)/86400.0) = 1 then 1 else 0 end) as ret_1d_flag,
- max(case when floor((session_start_time-move_time+300)/86400.0) = 2 then 1 else 0 end) as ret_2d_flag,
- max(case
- when floor((session_start_time-move_time+300)/86400.0) = 5 then 1 else 0 end) as ret_5d_flag,
- max(case when floor((session_start_time-move_time+300)/86400.0) = 7 then 1 else 0 end) as ret_7d_flag,
- 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, 0 last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, 0 first_map_load_time
- from
- ( select
- id,
- socnet,
- session_start_time,
- session_length,
- move_time,
- move_date,
- is_new,
- version,
- comparison_type,
- ram,
- cpu,
- screen_resolution,
- row_number() over (partition
- by
- id,
- socnet,
- comparison_type,
- version
- order by
- (case
- when session_start_time-move_time+300<0 then null else session_start_time-move_time end)) as session_number
- from
- master.mghost_sessions natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40
- )
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time, 0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, count(id) as rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, 0 last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, 0 first_map_load_time
- from
- ext_master.mghost_item_account natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40 and event='receive' and origin='craft' and item_type='rare'
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time, 0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, max(case
- when event='open' then 1
- else 0
- end) as bank_open_flag, max(case
- when event='accrual' then 1
- else 0
- end) as bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, 0 last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, 0 first_map_load_time
- from
- ext_master.mghost_bank_conversion natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40 and event in ('open','accrual')
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time,\0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, count(case
- when event in ('4_click_on_chest','ad_viewed') then id
- else null
- end) as ad_views, count(case
- when event in ('1_click_on_fay','click_on_icon') then id
- else null
- end) as click_on_fay_flag, count(case
- when event in ('0_advert_start','ad_loaded') then id
- else null
- end) as fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, 0 last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, 0 first_map_load_time
- from
- ext_master.mghost_advert natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40 and event in ('ad_loaded','0_advert_start','4_click_on_chest','ad_viewed','1_click_on_fay','click_on_icon')
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time,\0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, count(distinct(case
- when event='start'
- and map_name like '%ML%' then map_name
- else null
- end)) as ml_starts, count(distinct(case
- when event='start'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_starts, count(distinct(case
- when event='start'
- and map_name like '%TCSQ%' then map_name
- else null
- end)) as tcsq_starts, count(distinct(case
- when event='finish'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_finishes, count(distinct(case
- when event='prolong'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_prolongs, count(distinct(case
- when event='forget'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_forgets, count(distinct(case
- when event='retry'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_retries, count(distinct(case
- when event='restart'
- and map_name like '%SQ%'
- and map_name not like '%TCSQ%' then map_name
- else null
- end)) as sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, max(case
- when cluster_name='quest_lines/grandfather'
- and event='start' then 1
- when cluster_name='quest_lines/mario'
- and event='start' then 2
- when cluster_name='quest_lines/gardener'
- and event='start' then 3
- when cluster_name='quest_lines/jane'
- and event='start' then 4
- when cluster_name='quest_lines/andrew'
- and event='start' then 5
- when cluster_name='quest_lines/alex'
- and event='start' then 6
- else null
- end) as last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, 0 first_map_load_time
- from
- ext_master.mghost_cluster_conversion natural
- join
- ghosts_mobile.versions_id natural
- join
- ghosts_mobile.cluster_names
- where
- current_date-actdate<=40
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time+300)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time, 0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, max(case
- when load_number = 1
- and total_time>0 then total_time
- else null
- end) as load_1_time, max(case
- when load_number = 2
- and total_time>0 then total_time
- else null
- end) as load_2_time, avg(case
- when utc_timestamp-move_time+300>=0 and load_number>=3 and total_time>0 then total_time else null end) as load_other_time,
- 0 offer_bank_starts,
- 0 offer_sale_starts,
- 0 last_house_start,
- 0 gfl_receive_flag,
- 0 gfl_use_flag,
- 0 first_map_load_time
- from
- ( select id, socnet, utc_timestamp, move_time, move_date, total_time, is_new, version, comparison_type, ram, cpu, screen_resolution,
- row_number() over (partition by id, socnet, comparison_type, version order by (case when utc_timestamp-move_time+300<0 then null else utc_timestamp-move_time end)) as load_number
- from
- ext_master.mghost_load_stat natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40 and event = 'game_start'
- )
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select id,
- socnet,
- floor((utc_timestamp-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time, 0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, sum(case
- when event='receive'
- and (offer_name like '%nopayer%'
- or offer_name like '%mult_%') then 1
- else 0
- end) as offer_bank_starts, sum(case
- when event='receive'
- and offer_name like '%sale_%' then 1
- else 0
- end) as offer_sale_starts, 0 last_house_start, max(case
- when offer_name like '%gift%'
- and event='receive' then 1
- else 0
- end) as gfl_receive_flag, max(case
- when offer_name like '%gift%'
- and event='spend' then 1
- else 0
- end) as gfl_use_flag, 0 first_map_load_time
- from
- ext_master.mghost_offer natural
- join
- ghosts_mobile.versions_id
- where
- current_date-actdate<=40 and offer_type<>'craft_offer' and offer_type<>'user_data'
- group by 1, 2, 3, 4, 5, 6, 7
- union all
- select a.id,
- socnet,
- floor((finish_time-move_time)*4.0/86400.0)/4 AS days_since_move,
- move_date, is_new, version, comparison_type, 0 payments, 0 offer_bank_payments, 0 offer_sale_payments, 0 transactions, 0 offer_bank_transactions, 0 offer_sale_transactions, 0 mquests_finish, 0 complexity_covered, 0 grzone_entries, 0 sessions_num, 0 sessions_time, 0 session_1_length, 0 session_2_flag, 0 session_3_flag, 0 ret_1d_flag, 0 ret_2d_flag, 0 ret_5d_flag, 0 ret_7d_flag, 0 rare_crafts, 0 bank_open_flag, 0 bank_accrual_flag, 0 ad_views, 0 click_on_fay_flag, 0 fay_arrived_flag, 0 ml_starts, 0 sq_starts, 0 tcsq_starts, 0 sq_finishes, 0 sq_prolongs, 0 sq_forgets, 0 sq_retries, 0 sq_restarts, 0 load_1_time, 0 load_2_time, 0 load_other_time, 0 offer_bank_starts, 0 offer_sale_starts, 0 last_house_start, 0 gfl_receive_flag, 0 gfl_use_flag, (finish_time-start_time) as first_map_load_time
- from
- ( select *
- from ghosts_mobile.versions_id
- where comparison_type='newbies'
- ) a
- join
- ( select id, min(utc_timestamp) as start_time
- from ext_master.mghost_content_conversion
- where current_date-actdate<=40 and content_name='quests/mario/map_1' and event='start'
- group by
- id ) b
- on a.id = b.id
- join
- ( select
- id,
- min(utc_timestamp) as finish_time
- from
- ext_master.mghost_content_conversion
- where
- current_date-actdate<=40 and content_name='quests/mario/map_1' and event='finish'
- group by id
- ) c
- on a.id = c.id
- where (finish_time-start_time)>=0
- ) a
- left outer join
- ( select
- distinct id,
- monetary_type,
- actdate as pdate,
- socnet
- from
- ghosts_mobile.payer_type_distr
- where
- current_date-actdate<=40
- ) c
- on a.id = c.id and move_date = pdate and a.socnet = c.socnet
- left outer join
- ( select
- distinct a.id,
- country_group,
- traffic_group,
- a.socnet,
- date(timestamp 'epoch' + utc_timestamp * interval '1 second') as regdate
- from
- (select * from master.mghost_personal_info_new) a join (select * from master.mghost_personal_info_groups) b on a.id = b.id
- ) d
- on a.id = d.id and a.socnet = d.socnet
- join
- ( select *
- from ghosts_mobile.versions_id
- ) e
- on a.id = e.id and a.is_new = e.is_new and a.version = e.version and coalesce(a.comparison_type,'notest') = coalesce(e.comparison_type,'notest')
- where days_since_move>=-14
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement