Last Google SQL Library
Queries at the lastgoogle site are logged in a mysql database. These are some of the queries I use with that database.Top Domains
SELECT statement to tally top result domains for queries in a given period-- Lastgeist: Top Domains for Defined Period (v.2)
-- WHERE clause: change date values to define period
-- LIMIT clause: number in countdown
-- FROM clause: check table name
SELECT
REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') AS result_domain,
count( REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') ) AS result_count
FROM queries
WHERE query <> '' AND result_link REGEXP 'http[s]?://.*'
AND timestamp >= UNIX_TIMESTAMP('2007-01-01')
AND timestamp < UNIX_TIMESTAMP('2007-02-01')
GROUP BY result_domain
ORDER BY result_count DESC
LIMIT 30
-- WHERE clause: change date values to define period
-- LIMIT clause: number in countdown
-- FROM clause: check table name
SELECT
REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') AS result_domain,
count( REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') ) AS result_count
FROM queries
WHERE query <> '' AND result_link REGEXP 'http[s]?://.*'
AND timestamp >= UNIX_TIMESTAMP('2007-01-01')
AND timestamp < UNIX_TIMESTAMP('2007-02-01')
GROUP BY result_domain
ORDER BY result_count DESC
LIMIT 30
CategoryLastGoogle
[Add comment]