Detail of processes

7za.exe x internet_users.zip it.net.user.p2_Indicator_en_csv_v2.csv

7za x world_population.zip sp.pop.totl_Indicator_en_csv_v2.csv

more  +2 sp.pop.totl_Indicator_en_csv_v2.csv > world_population_2013.tsv

more +2 it.net.user.p2_Indicator_en_csv_v2.csv > wb_internet_users_2013.tsv 

CREATE TABLE nb_internet_users AS
SELECT
    wb_internet_users.`Country Name` AS country_name,
    wb_internet_users.`Country Code` AS country_code,
    wb_internet_users.`2013` as pct_internauts,
    world_population.`2013` as population,
    CAST(wb_internet_users.`2013` * world_population.`2013` / 100 AS INT) as nb_internauts
FROM wb_internet_users LEFT JOIN world_population ON wb_internet_users.`Country Code` = world_population.`Country Code`

CREATE TABLE market_penetration AS
SELECT
    nb_internet_users.country_name,
    nb_internet_users.country_code,
    nb_internet_users.nb_internauts,
    sales.nb_sales,
    (nb_sales * 100.0 / nb_internauts) AS penetration_rate
FROM nb_internet_users JOIN sales ON nb_internet_users.country_name = sales.country_name

-- Compute variance (square of the standard deviation)
CREATE TABLE spread AS 
SELECT
    AVG((penetration_rate - sub.mean) * (penetration_rate - sub.mean)) as variance,
    mean
FROM market_penetration, (SELECT AVG(penetration_rate) AS mean FROM market_penetration) AS sub;
-- Then, check wether performance is in the norm
CREATE TABLE penetration_performance AS
SELECT
    country_name,
    country_code,
    mean, 
    penetration_rate, 
    (penetration_rate - mean) * abs(penetration_rate - mean) / variance AS norm_spread 
FROM market_penetration, spread WHERE penetration_rate IS NOT NULL;

python layout_stats.py