mfjZKg0h

1
2CREATE TEMPORARY TABLE IF NOT EXISTS temp_ids1 (codex_app_profile_id INTEGER, ios_app_genre_id INTEGER, onavo_rank INTEGER);
3CREATE TEMPORARY TABLE IF NOT EXISTS temp_ids2 (codex_app_profile_id INTEGER, ios_app_genre_id INTEGER, onavo_rank INTEGER);
4DELETE FROM temp_ids1;
5DELETE FROM temp_ids2;
6
7INSERT INTO temp_ids1 (
8    SELECT id AS codex_app_profile_id, ios_app_genre_id, onavo_rank 
9    FROM codex_app_profiles cap 
10    INNER JOIN (SELECT codex_app_profile_id AS rank_id, onavo_rank 
11               FROM codex_monthly_stats 
12               WHERE month = 6 AND year = 2012) cms
13    ON cap.id = cms.rank_id);
14INSERT INTO temp_ids2 (SELECT * FROM temp_ids1);
15
16SELECT temp1.*
17FROM  temp_ids1 temp1 LEFT JOIN temp_ids2 temp2
18  ON  (temp1.ios_app_genre_id = temp2.ios_app_genre_id AND temp1.onavo_rank > temp2.onavo_rank)
19WHERE temp2.ios_app_genre_id IS NULL;