data = [{"owner":"hank.koerner@gmail.com","date":"10\/29\/2012 10:42:07 PM","query":"SELECT top 100    term_id,sum(frequency) as [word count]   FROM [billhowe].[reuters_terms.csv]   group by term_id","id":"14540"},{"owner":"hank.koerner@gmail.com","date":"10\/29\/2012 10:43:35 PM","query":"SELECT top 100    term_id,sum(frequency) as [word count]   FROM [billhowe].[reuters_terms.csv]   group by term_id   order by [word count] desc","id":"14542"},{"owner":"hank.koerner@gmail.com","date":"10\/29\/2012 11:42:10 PM","query":"SELECT top 100    doc_id,sum(frequency) as doc_count   FROM [billhowe].[reuters_terms.csv]   group by doc_id   order by doc_count desc","id":"14544"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 12:08:34 AM","query":"SELECT top 100    doc_id,count(*) as doc_count   FROM [billhowe].[reuters_terms.csv]   group by doc_id   order by doc_count desc","id":"14545"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 12:09:28 AM","query":"SELECT top 100   count(distinct doc_id) as doc_count   FROM [billhowe].[reuters_terms.csv]","id":"14546"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 12:10:09 AM","query":"SELECT top 100   count(distinct term_id) as term_count   FROM [billhowe].[reuters_terms.csv]","id":"14547"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 12:11:35 AM","query":"SELECT top 100    count(*) as row_count   ,count(distinct doc_id) as doc_count   ,count(distinct term_id) as term_count   FROM [billhowe].[reuters_terms.csv]","id":"14548"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 12:13:05 AM","query":"SELECT --top 100    count(*) as row_count   ,count(distinct doc_id) as doc_count   ,count(distinct term_id) as term_count   FROM [billhowe].[reuters_terms.csv]","id":"14549"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:15:18 AM","query":"SELECT top 100   doc_id,term_id   ,SUM(CASE WHEN frequency>0 then 1 else 0 END) as tf FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id","id":"14550"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:18:15 AM","query":"SELECT top 100   doc_id,term_id,SUM(frequency) as tf FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id","id":"14551"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:20:25 AM","query":"SELECT top 100   doc_id,term_id,SUM(frequency) as tf FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id","id":"14552"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:21:00 AM","query":"SELECT top 100   doc_id,term_id,SUM(frequency) as tf FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id ORDER BY doc_id,term_id","id":"14553"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:24:11 AM","query":"SELECT top 100   doc_id,term_id,SUM(frequency) as tf   ,(SELECT COUNT(DISTINCT doc_id) FROM [billhowe].[reuters_terms.csv]) as D FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id ORDER BY doc_id,term_id","id":"14554"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:26:13 AM","query":"SELECT top 100   doc_id,term_id,MIN(frequency) as frequency,SUM(frequency) as tf   ,(SELECT COUNT(DISTINCT doc_id) FROM [billhowe].[reuters_terms.csv]) as D FROM [billhowe].[reuters_terms.csv] GROUP BY doc_id,term_id ORDER BY doc_id,term_id","id":"14555"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:43:46 AM","query":"SELECT top 100   doc_id,term_id,frequency   ,(SELECT COUNT(doc_id) FROM [billhowe].[reuters_terms.csv]) as D FROM [billhowe].[reuters_terms.csv] ORDER BY doc_id,term_id","id":"14557"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:44:47 AM","query":"SELECT top 100   doc_id,term_id,frequency   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D FROM [billhowe].[reuters_terms.csv] ORDER BY doc_id,term_id","id":"14558"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 3:56:47 AM","query":"SELECT top 100   term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id ORDER BY term_id","id":"14559"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:02:09 AM","query":"SELECT   term_id,tf,D,docs_with_T   ,log(D)-log(docs_with_T) as idf   ,tf * (log(D)-log(docs_with_T)) as [tf-idf] FROM ( SELECT top 100   term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id ORDER BY term_id   ) a","id":"14560"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:05:50 AM","query":"SELECT   term_id,tf,D,docs_with_T   ,log(D)-log(docs_with_T) as idf   ,tf * (log(D)-log(docs_with_T)) as [tf-idf] FROM ( SELECT --top 100   term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a","id":"14562"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:06:59 AM","query":"SELECT   term_id,tf,D,docs_with_T   ,log(D)-log(docs_with_T) as idf   ,tf * (log(D)-log(docs_with_T)) as [tf-idf] FROM ( SELECT --top 100   term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY [tf-idf]","id":"14563"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:08:45 AM","query":"SELECT   term_id,tf,D,docs_with_T   ,log(D)-log(docs_with_T) as idf   ,tf * (log(D)-log(docs_with_T)) as [tf-idf] FROM ( SELECT    term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY [tf-idf]","id":"14564"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:09:55 AM","query":"SELECT   term_id,tf,D,docs_with_T   ,log(D)-log(docs_with_T) as idf   ,tf * (log(D)-log(docs_with_T)) as [tf-idf] FROM ( SELECT    term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY [tf-idf] desc","id":"14565"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:22:41 AM","query":"--Assignment #1 Intro to DataSci --Compute the tf-idf (term frequency - inverse document frequency)  --in SQL --Verbose select to show intermediate terms  SELECT   term_id,tf,D,docs_with_T   ,log(D\/docs_with_T) as idf   ,tf * (log(D\/docs_with_T)) as [tf-idf] FROM ( SELECT    term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY [tf-idf] desc","id":"14567"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:23:30 AM","query":"--Assignment #1 Intro to DataSci --Compute the tf-idf (term frequency - inverse document frequency)  --in SQL --Verbose select to show intermediate terms  SELECT   term_id,tf,D,docs_with_T   ,log(D\/docs_with_T) as idf   ,tf * log(D\/docs_with_T) as [tf-idf] FROM ( SELECT    term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY [tf-idf] desc","id":"14568"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:24:57 AM","query":"--Assignment #1 Intro to DataSci --Compute the tf-idf (term frequency - inverse document frequency)  --in SQL --Verbose select to show intermediate terms  SELECT   term_id,tf,D,docs_with_T   ,log(D\/docs_with_T) as idf   ,tf * log(D\/docs_with_T) as tfidf FROM ( SELECT    term_id,SUM(frequency) as tf   ,(SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D   ,COUNT(*) as docs_with_T FROM [billhowe].[reuters_terms.csv] GROUP BY term_id   ) a ORDER BY tfidf desc","id":"14570"},{"owner":"hank.koerner@gmail.com","date":"10\/30\/2012 4:30:46 AM","query":"--assignment #1 intro to datasci --compute the tf-idf (term frequency - inverse document frequency)  --in sql --verbose select to show intermediate terms  select   term_id,tf,d,docs_with_t   ,log(d\/docs_with_t) as idf   ,tf * log(d\/docs_with_t) as tfidf from ( select    term_id,sum(frequency) as tf   ,(select count(distinct doc_id) from [billhowe].[reuters_terms.csv]) as d   ,count(*) as docs_with_t from [billhowe].[reuters_terms.csv] group by term_id   ) a","id":"252386"},{"owner":"hank.koerner@gmail.com","date":"10\/31\/2012 10:46:11 PM","query":"--DataSci Assignment 2, Question 2 --This query calculates term freq * inverse doc freq. --3 subqueries create 3 matrices each unique terms long --by 2 columns wide. --The outer query selects intermediate terms and --calculates the tf_idf from the 3 matrices. --matrix 1: (term_id,tf) --matrix 2: (term_id,D), where D is the # of docs in the corpus --matrix 3: (term_id,df), where df is doc freq for a given term  SELECT    a.term_id,tf,D,df,   tf*LOG(D\/df) as tf_idf   FROM   (     --This subquery creates the term frequency matrix     SELECT     term_id,SUM(frequency) as tf     FROM [billhowe].[reuters_terms.csv]     GROUP BY term_id   ) as a    inner join    (     --This subquery creates a matrix for documents D in the corpus     --The inner subquery calculates D, which is replicated for all term_id     SELECT     term_id,     (SELECT COUNT(distinct doc_id) FROM [billhowe].[reuters_terms.csv]) as D     FROM [billhowe].[reuters_terms.csv]     GROUP BY term_id   ) as b   on a.term_id=b.term_id   inner join    (     --This subquery calculated document frequency df     --where, by virtue of the GROUP BY, tf is not NULL     SELECT     term_id,     COUNT(doc_id) as df     FROM [billhowe].[reuters_terms.csv]     GROUP BY term_id   ) as c   on a.term_id=c.term_id   ORDER BY tf_idf desc","id":"14727"},{"owner":"hank.koerner@gmail.com","date":"10\/31\/2012 10:50:19 PM","query":"--datasci assignment 2, question 2 --this query calculates term freq * inverse doc freq. --3 subqueries create 3 matrices each unique terms long --by 2 columns wide. --the outer query selects intermediate terms and --calculates the tf_idf from the 3 matrices. --matrix 1: (term_id,tf) --matrix 2: (term_id,d), where d is the # of docs in the corpus --matrix 3: (term_id,df), where df is doc freq for a given term  --note: source table was inspected for unique terms, docs, rows, null values,  --etc. prior to writing the query.  select    a.term_id,tf,d,df,   tf*log(d\/df) as tf_idf   from   (     --this subquery creates the term frequency matrix     select     term_id,sum(frequency) as tf     from [billhowe].[reuters_terms.csv]     group by term_id   ) as a    inner join    (     --this subquery creates a matrix for documents d in the corpus     --the inner subquery calculates d, which is replicated for all term_id     select     term_id,     (select count(distinct doc_id) from [billhowe].[reuters_terms.csv]) as d     from [billhowe].[reuters_terms.csv]     group by term_id   ) as b   on a.term_id=b.term_id   inner join    (     --this subquery calculated document frequency df     --where, by virtue of the group by, tf is not null     select     term_id,     count(doc_id) as df     from [billhowe].[reuters_terms.csv]     group by term_id   ) as c   on a.term_id=c.term_id","id":"254097"}];
