Top Domains

-- 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('2006-01-01')
AND timestamp < UNIX_TIMESTAMP('2006-02-01')
GROUP BY result_domain
ORDER BY result_count DESC
LIMIT 30

Monthly Query Dataset

-- Lastgeist: Query Dataset for Defined Period
-- WHERE clause: change date values to define period
-- SELECT clause: check auto query key

-- DROP, CREATE TABLE clause: change table name
-- INSERT clause: match table name to one above
-- FROM clause: original table (i.e., 'queries')


DROP TABLE IF EXISTS `monthly_query_report`;
CREATE TABLE `monthly_query_report`
(
 `UID` int(11) NOT NULL auto_increment,
 `query` varchar(100) NOT NULL default '',	
 `result_link` varchar(250) NOT NULL default '',
 `result_domain` varchar(250) NOT NULL default '',
 `last_result_num` int(11) NOT NULL default '0',
 `total_num_results` int(11) NOT NULL default '0',
 `google_cache` varchar(250) NOT NULL default '',
 `is_auto_query` tinyint(4) NOT NULL default '0',

 PRIMARY KEY  (`UID`),
 KEY `result_domain` (`result_domain`)
)
TYPE=MyISAM
AUTO_INCREMENT=1 ;

INSERT INTO `monthly_query_report`
(
 UID, query, result_link, result_domain,
 last_result_num, total_num_results, 
 google_cache, is_auto_query
)

SELECT '', query, result_link,
  REPLACE ( SUBSTRING (result_link,8,LOCATE('/',result_link,8)-8), 'www.', '' ) as result_domain,
  last_result as last_result_num, num_results as total_num_results, google_cache,
  is_auto='IS_AUTO_KEY'
FROM queries
WHERE query <> '' AND result_link REGEXP 'http://(.*)/.*'
AND timestamp >= UNIX_TIMESTAMP('2006-01-01')
AND timestamp < UNIX_TIMESTAMP('2006-02-01')