Anonymous/anonymous.sql at master · markschaver/Anonymous · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
SELECT
  source,
  count(source)
FROM anon
GROUP BY source
ORDER BY count(source)
  DESC;

SELECT
  date_published,
  count(date_published)
FROM anon
GROUP BY date_published
ORDER BY date_published
  DESC;

SELECT
  link,
  source,
  phrase,
  title,
  content,
  date_published
FROM anon
ORDER BY date_published
  DESC
LIMIT 100;

CREATE TABLE anon (
  source,
  phrase,
  title,
  link PRIMARY KEY ON CONFLICT IGNORE,
  content,
  date_published DATE
);

SELECT outlets.name
FROM anon
  LEFT OUTER JOIN outlets ON anon.source = outlets.url;

SELECT
  anon.source,
  outlets.name,
  anon.phrase,
  anon.title,
  anon.link,
  anon.content,
  anon.date_published
FROM anon
  LEFT OUTER JOIN outlets ON anon.source = outlets.url;

SELECT
  outlets.name,
  outlets.url
FROM outlets
ORDER BY outlets.name;

SELECT DISTINCT
  anon.source,
  outlets.name
FROM anon
  JOIN outlets ON anon.source = outlets.url
ORDER BY outlets.name;

SELECT DISTINCT anon.source
FROM anon;

SELECT
  anon.title,
  count(anon.title)
FROM anon
GROUP BY anon.title
ORDER BY count(anon.title)
  DESC;

SELECT
  anon.source,
  anon.content,
  count(anon.content)
FROM anon
GROUP BY anon.content
ORDER BY count(anon.content)
  DESC;

DELETE FROM anon
WHERE ROWID NOT IN (SELECT min(ROWID)
                    FROM anon
                    GROUP BY source, content);

DELETE FROM anon
WHERE ROWID NOT IN (SELECT min(ROWID)
                    FROM anon
                    GROUP BY source, title);

SELECT
  source,
  content,
  count(source)
FROM anon
GROUP BY source, content
ORDER BY count(source)
  DESC;

SELECT
  source,
  title,
  count(source)
FROM anon
GROUP BY source, title
ORDER BY count(source)
  DESC;

SELECT min(ROWID)
FROM anon
GROUP BY source, title;

SELECT
  source,
  title
FROM anon
GROUP BY source, title;

SELECT
  anon.source,
  outlets.name,
  anon.phrase,
  anon.title,
  anon.link,
  anon.content,
  anon.date_published
FROM anon
  LEFT OUTER JOIN outlets ON anon.source = outlets.url
ORDER BY date_published
  DESC
LIMIT 100, 10;

SELECT count(*)
FROM anon
  LEFT OUTER JOIN outlets ON anon.source = outlets.url
WHERE anon.source = 'www.nytimes.com';

SELECT count(*) FROM anon where source = 'www.nytimes.com';