audience_keyword_research.sql

DROP VIEW IF EXISTS flat_links;
CREATE TEMPORARY VIEW flat_links AS
SELECT
  l.id as link_id, title, snippet, link, domain, rank,
  link_type, search_engine_name, page_number,
  requested_at, num_results_for_query,
  num_results, q
FROM link l
LEFT JOIN (
  SELECT
    id AS link_id, search_engine_name, page_number,
    strftime('%Y-%m-%dT%H:%M:%S', requested_at) as requested_at,
    num_results_for_query, num_results, "query" AS q, serp.id
  FROM serp
) s ON s.id = l.serp_id;

DROP VIEW IF EXISTS query_domain_frequencies;
CREATE TEMPORARY VIEW query_domain_frequencies AS
SELECT
  q, domain, count(*) as domain_count
FROM flat_links
GROUP BY q, domain
ORDER BY q;