renesys challenge

renesys challenge

wget http://www.renesys.com/challenge_site/static/data/no_zealand.txt.bz2
bzip2 -d no_zealand.txt.bz2

# http://www.renesys.com/challenge_site

CREATE TABLE IF NOT EXISTS ipnos (
  ip varchar(40) default NULL,
  source varchar(40) NOT NULL,
  country varchar(4) default NULL,
  region varchar(40) default NULL,
  city varchar(40) default NULL,
  score decimal(6,2) default NULL
) ;

LOAD DATA LOCAL INFILE 'no_zealand.txt' INTO TABLE ipnos FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

CREATE VIEW  ipnos_location AS SELECT ip, CONCAT( country,  '-', region,  '-', city ) AS location, score
FROM  `ipnos` 
WHERE country NOT IN ('NZ', 'BE') AND region NOT IN ('HI') ;

CREATE VIEW  `ipnos_location_count` AS SELECT COUNT( ip ) AS count_ip, location
FROM  `ipnos_location` 
GROUP BY location
ORDER BY location ;

SELECT *
FROM  `ipnos_location` il
INNER JOINx
ipnos_location_count USING(location)
WHERE count_ip > 10 and count_ip < 100
ORDER BY il.location
LIMIT 9999,1 ;