crux_ranking.sql

CREATE TABLE IF NOT EXISTS
  `httparchive-sandbox.crux.country_summary_082022` AS (
  SELECT
    DISTINCT origin,
    country_code
  FROM
    `chrome-ux-report.materialized.country_summary`
  WHERE
    yyyymm = 202208
    AND rank = 1000
  GROUP BY
    country_code,
    origin
  ORDER BY
    country_code,
    origin );

-- Count how often an origin appears in country top 1000 lists
CREATE TABLE IF NOT EXISTS
  `httparchive-sandbox.crux.origin_by_num_countries_082022` AS (
  SELECT
    DISTINCT origin,
    COUNT(*) OVER (PARTITION BY origin) AS num_countries
  FROM
    `httparchive-sandbox.crux.country_summary_082022`
  ORDER BY
    num_countries DESC );

-- How many HTTPS vs HTTP
SELECT
  COUNT(
    DISTINCT origin)
FROM
  `httparchive-sandbox.crux.country_summary_082022`
GROUP BY
  STARTS_WITH(origin, "http://");

-- Overall top 1000
CREATE TABLE IF NOT EXISTS
  `httparchive-sandbox.crux.overall_top_1000_082022` AS (
SELECT
  DISTINCT origin,
FROM
  `chrome-ux-report.all.202208`
WHERE
  experimental.popularity.rank = 1000
);

SELECT * FROM `httparchive-sandbox.crux.overall_top_1000_082022`;

-- Country code lookup table
SELECT
  DISTINCT country_code,
  `chrome-ux-report`.experimental.GET_COUNTRY(country_code) AS country
FROM
  `httparchive-sandbox.test.country_summary_august_2022`;

-- Reporting data sources

SELECT
  *
FROM
  `httparchive-sandbox.crux.origin_by_num_countries_082022`;

-- Alphabetical order
SELECT
  *
FROM
  `httparchive-sandbox.crux.country_summary_082022`;

-- Find unique origins for each country
SELECT
  `chrome-ux-report`.experimental.GET_COUNTRY(country_code) AS country,
  COUNT(*) AS num_unique_origins
FROM
  `httparchive-sandbox.crux.country_summary_082022` AS table1
    LEFT JOIN `httparchive-sandbox.crux.origin_by_num_countries_082022` AS table2 on table1.origin = table2.origin
WHERE num_countries = 1
GROUP BY country_code
ORDER by num_unique_origins desc;

-- Find average num_countries per country. Only include countries with a full 1000 entries
SELECT `chrome-ux-report`.experimental.GET_COUNTRY(country_code) AS country, avg_num_countries FROM (
SELECT
  country_code,
  AVG(num_countries) AS avg_num_countries,
  COUNT(table1.origin) AS count_origins
FROM
  `httparchive-sandbox.crux.country_summary_082022` AS table1
    LEFT JOIN `httparchive-sandbox.crux.origin_by_num_countries_082022` AS table2 on table1.origin = table2.origin
GROUP BY country_code
ORDER by avg_num_countries desc) where count_origins = 1000;

-- Ordered by most broadly popular 
SELECT
  table1.origin, country_code, num_countries
FROM
  `httparchive-sandbox.crux.country_summary_082022` AS table1
LEFT JOIN `httparchive-sandbox.crux.origin_by_num_countries_082022` AS table2 on table1.origin = table2.origin
ORDER BY country_code, num_countries DESC;

-- Ordered by least broadly popular 
SELECT
  table1.origin, country_code, num_countries
FROM
  `httparchive-sandbox.crux.country_summary_082022` AS table1
LEFT JOIN `httparchive-sandbox.crux.origin_by_num_countries_082022` AS table2 on table1.origin = table2.origin
ORDER BY country_code, num_countries;