select 
	adnest_mapping_composite.effective_adnest_id, 
	adnest_mapping_composite.ratio,
	tab2.*
from adnest_mapping_composite
inner join 
	(
		select 
			tab.adnest_id,
			sum(tab.frequency between 1 and 10) as "1-10",
			sum(tab.frequency between 11 and 50) as "11-50",
			sum(tab.frequency between 51 and 100) as "51-100",
			sum(tab.frequency between 101 and 500) as "101-500",
			sum(tab.frequency between 501 and 1000) as "501-10000"
		from
		(
			select adnest_id, visitor_id, count(visitor_id) as frequency
			from hist_impressions
			inner join timeslot t on t.timeslot_id = hist_impressions.timeslot_id
			where start_date between '2014-06-01 00:00:00' and '2014-09-08 00:00:00'
			group by adnest_id, visitor_id
			order by adnest_id, count(visitor_id) desc
		) tab
		group by 
			tab.adnest_id
	) tab2
on tab2.adnest_id = adnest_mapping_composite.adnest_id
group by 
	effective_adnest_id,
	adnest_id
;

create temporary table ranges 
(
   s int not null,
   e int not null
);

insert into ranges (s, e) values
   (0, 100),
   (101, 200),
   (201, 300),
   (301, 1000),
   (1001, 1500),
   (1501, 10000)
;