What are the historical ranks for today's Top 10 URLS?

Analysis
1

A forgotten piece of HTTP Archive’s data is a history of the rank for each URL. This query takes today’s Top 10 URLs, and looks at how each website’s rank has changed over the last few years. (Note that ranks started being recorded in November of 2011, hence “startedDateTime >= 1320100000”).

SELECT YEAR(SEC_TO_TIMESTAMP(startedDateTime)) as year, MONTH(SEC_TO_TIMESTAMP(startedDateTime)) as month, 
       MAX(IF(url="http://www.google.com/",rank,0)) as Google,
       MAX(IF(url="http://www.facebook.com/",rank,0)) as Facebook,
       MAX(IF(url="http://www.youtube.com/",rank,0)) as YouTube,
       MAX(IF(url="http://www.yahoo.com/",rank,0)) as Yahoo,
       MAX(IF(url="http://www.baidu.com/",rank,0)) as Baidu,
       MAX(IF(url="http://www.wikipedia.org/",rank,0)) as Wikipedia,
       MAX(IF(url="http://www.amazon.com/",rank,0)) as Amazon,
       MAX(IF(url="http://www.taobao.com/",rank,0)) as Taobao,
       MAX(IF(url="http://www.twitter.com/",rank,0)) as Twitter,
       MAX(IF(url="http://www.qq.com/",rank,0)) as qq
FROM (TABLE_QUERY([httparchive:runs], 'REGEXP_MATCH(table_id, r"^201.*_01.*pages$")'))
WHERE url in ("http://www.google.com/", "http://www.facebook.com/", "http://www.youtube.com/", "http://www.yahoo.com/", "http://www.baidu.com/", 
              "http://www.wikipedia.org/", "http://www.amazon.com/", "http://www.taobao.com/", "http://www.twitter.com/", "http://www.qq.com/")
      AND startedDateTime >= 1320100000
GROUP BY year, month
ORDER BY year, month asc

The results show how some of the Top 10 have exchanged positions, and others have entered the Top 10 from outside.

1 Like