select region, bsc, lac, cid, cell, sector, 

block_ps99.date_count as block_ps99_days, block_ps99.badparam_hours as block_ps99_hours, block_ps99.badparam_avg as block_ps99_avg,
block_hsdpa.date_count as block_hsdpa_days, block_hsdpa.badparam_hours as block_hsdpa_hours, block_hsdpa.badparam_avg as block_hsdpa_avg,
block_sp.date_count as block_sp_days, block_sp.badparam_hours as block_sp_hours, block_sp.badparam_avg as block_sp_avg,
cssr_ps99.date_count as cssr_ps99_days, cssr_ps99.badparam_hours as cssr_ps99_hours, cssr_ps99.badparam_avg as cssr_ps99_avg,
cssr_hsdpa.date_count as cssr_hsdpa_days, cssr_hsdpa.badparam_hours as cssr_hsdpa_hours, cssr_hsdpa.badparam_avg as cssr_hsdpa_avg,
cssr_sp.date_count as cssr_sp_days, cssr_sp.badparam_hours as cssr_sp_hours, cssr_sp.badparam_avg as cssr_sp_avg,
drop_ps99.date_count as drop_ps99_days, drop_ps99.badparam_hours as drop_ps99_hours, drop_ps99.badparam_avg as drop_ps99_avg,
drop_hsdpa.date_count as drop_hsdpa_days, drop_hsdpa.badparam_hours as drop_hsdpa_hours, drop_hsdpa.badparam_avg as drop_hsdpa_avg,
drop_sp.date_count as drop_sp_days, drop_sp.badparam_hours as drop_sp_hours, drop_sp.badparam_avg as drop_sp_avg,

block_ps99.weight_index as block_ps99_index, block_hsdpa.weight_index as block_hsdpa_index, block_sp.weight_index as block_sp_index,
cssr_ps99.weight_index as cssr_ps99_index, cssr_hsdpa.weight_index as cssr_hsdpa_index, cssr_sp.weight_index as cssr_sp_index,
drop_ps99.weight_index as drop_ps99_index, drop_hsdpa.weight_index as drop_hsdpa_index, drop_sp.weight_index as drop_sp_index,

(
(case when block_ps99.weight_index is null then 0 else block_ps99.weight_index end) * 9 +
(case when block_hsdpa.weight_index is null then 0 else block_hsdpa.weight_index end) * 9 +
(case when block_sp.weight_index is null then 0 else block_sp.weight_index end) * 10 +

(case when cssr_ps99.weight_index is null then 0 else cssr_ps99.weight_index end) * 10 +
(case when cssr_hsdpa.weight_index is null then 0 else cssr_hsdpa.weight_index end) * 9 +
(case when cssr_sp.weight_index is null then 0 else cssr_sp.weight_index end) * 13 +

(case when drop_ps99.weight_index is null then 0 else drop_ps99.weight_index end) * 12 +
(case when drop_hsdpa.weight_index is null then 0 else drop_hsdpa.weight_index end) * 13 +
(case when drop_sp.weight_index is null then 0 else drop_sp.weight_index end) * 15

) as weight_index
from (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, block_ps99_rate as badparam, (traff_dl_data_99 +  traff_ul_data_99) as traffic_field, block_ps99_rate*(traff_dl_data_99 +  traff_ul_data_99)/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and block_ps99_rate > 3 and (traff_dl_data_99 +  traff_ul_data_99)>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) block_ps99 
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, block_hsdpa_rate as badparam, traff_data_hsdpa as traffic_field, block_hsdpa_rate*traff_data_hsdpa/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and block_hsdpa_rate > 3 and traff_data_hsdpa>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) block_hsdpa using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, block_sp_rate as badparam, traff_sp_cell as traffic_field, block_sp_rate*traff_sp_cell/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and block_sp_rate > 2 and traff_sp_cell>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) block_sp using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, cssr_ps99 as badparam, (traff_dl_data_99 +  traff_ul_data_99) as traffic_field, cssr_ps99*(traff_dl_data_99 +  traff_ul_data_99)/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and cssr_ps99 > 0 and cssr_ps99< 97 and (traff_dl_data_99 +  traff_ul_data_99)>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) cssr_ps99 using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, cssr_hsdpa as badparam, traff_data_hsdpa as traffic_field, cssr_hsdpa*traff_data_hsdpa/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and cssr_hsdpa > 0 and cssr_hsdpa< 97 and traff_data_hsdpa>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) cssr_hsdpa using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, traff_sp_cell as badparam, cssr_sp as traffic_field, traff_sp_cell*cssr_sp/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and traff_sp_cell > 0 and traff_sp_cell< 97 and cssr_sp>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) cssr_sp using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, drop_ps99_rate as badparam, (traff_dl_data_99 +  traff_ul_data_99) as traffic_field, drop_ps99_rate*(traff_dl_data_99 +  traff_ul_data_99)/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and drop_ps99_rate > 1.1 and (traff_dl_data_99 +  traff_ul_data_99)>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) drop_ps99 using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, drop_hsdpa_rate as badparam, traff_data_hsdpa as traffic_field, drop_hsdpa_rate*traff_data_hsdpa/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and drop_hsdpa_rate > 1.1 and traff_data_hsdpa>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) drop_hsdpa using (region, bsc, lac, cid, cell, sector)
full join (
select *,  date_count * badparam_hours * lost_traf  as weight_index

from
(
select  region, bsc, lac, cid, cell, sector , count( date_ ) as date_count, avg( badparam_hours ) as badparam_hours, 
sum( lost_traf )*100./sum(traf_db) as badparam_avg,
min( badparam_min ) as badparam_min, max( badparam_max) as badparam_max, avg( traf_db )/avg( badparam_hours ) as traf_db_avg, sum(lost_traf) as lost_traf
from
(
select  date_, region, bsc, lac, cid, cell, sector , count( badparam ) as badparam_hours,  sum(lost_h_traf)*100./sum(traffic_field) as badparam_avg, 
min( badparam ) as badparam_min, max( badparam ) as badparam_max, sum( traffic_field ) as traf_db, sum(lost_h_traf) as lost_traf
from
(
select
date_part( 'day', start_date) as date_, region, bsc, lac, cid, cell, sector, drop_sp_rate as badparam, traff_sp_cell as traffic_field, drop_sp_rate*traff_sp_cell/100. as lost_h_traf

from web.cell_hour_3g
where   region='nov'  and  date( start_date) between ( date('2011-12-08') - interval '6 day') and '2011-12-08'  and drop_sp_rate > 1.1 and traff_sp_cell>0
) initial_selection
group by  date_, region, bsc, lac, cid, cell, sector 
having count( badparam ) >= 8
) badparam_ass_bad
group by  region, bsc, lac, cid, cell, sector 
having count( date_ ) >= 5 
) final ) drop_sp using (region, bsc, lac, cid, cell, sector)

order by weight_index desc

limit 50