rank crime cuadrantes

gistfile1.txt

--cuadrantes
with crimes as
(select sum(count) as count,sector,cuadrante,max(population)as population, crime from cuadrantes  where date >= '2013-08-01' and date <= '2014-07-01' group by cuadrante, sector, crime)
SELECT * from (SELECT count,crime,sector,cuadrante,rank() over (partition by crime order by count desc) as rank,population from crimes group by count,crime,sector,cuadrante,population) as temp2 where rank <= (SELECT rank from (SELECT count,cuadrante,rank() over (partition by crime order by count desc) as rank, row_number() OVER (ORDER BY count desc) AS rownum from crimes) as rank10 where rownum = 10) order by crime, rank,sector, cuadrante


--sectores
with crimes as
(select (sum(count) / (sum(population) /12 )* 100000) as rate,sum(count) as count,sector,sum(population)/12 as population, crime from cuadrantes  where date >= '2013-08-01' and date <= '2014-07-01' group by sector, crime)
SELECT * from (SELECT count,rate,crime,sector,rank() over (partition by crime order by rate desc) as rank,population from crimes group by count,crime,sector,population, rate) as temp2 where rank <= (SELECT rank from (SELECT rate,rank() over (partition by crime order by rate desc) as rank, row_number() OVER (ORDER BY count desc) AS rownum from crimes) as rank10 where rownum = 10) order by crime, rank,sector

--diff
with difference as
(select crime, cuadrante, sector, max(population) as population, sum(case when date = '2014-07-01' or date = '2014-06-01' or date='2014-05-01' THEN count ELSE 0 END) as y2014, sum(case when date = '2013-07-01' or date = '2013-06-01' or date='2013-05-01' THEN count ELSE 0 END) as y2013, sum(case when date = '2014-07-01' or date = '2014-06-01' or date='2014-05-01' THEN count ELSE 0 END) - sum(case when date = '2013-07-01' or date = '2013-06-01' or date='2013-05-01' THEN count ELSE 0 END) as diff  from cuadrantes group by cuadrante, sector, crime order by diff desc)
SELECT * from (SELECT rank() over (partition by crime order by diff desc) as rank,crime,cuadrante,sector,population, y2013, y2014,diff from difference group by diff,crime,sector,cuadrante, population, y2013, y2014) as temp where rank <= 5