-
Notifications
You must be signed in to change notification settings - Fork 0
/
howToHackHackerNews
56 lines (46 loc) · 1.17 KB
/
howToHackHackerNews
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
select title, score
from hacker_news
order by score desc
limit 5;
select title, score
from hacker_news
where title is not null
order by score desc;
select user, sum(score) as total_score
from hacker_news
group by user
having total_score > 200
order by total_score desc;
select sum(score)
from hacker_news;
select (517 + 309 + 304 + 282) / 6366.0 as percentage;
select user, url, count(url)
from hacker_news
group by user
having url like '%https://www.youtube.com/watch?v=dQw4w9WgXcQ%';
select case
when url like '%github.com%' then 'GitHub'
when url like '%medium.com%' then 'Medium'
when url like '%nytimes.com%' then 'NY Times'
else 'Other'
end as 'Source'
,count(title)
from hacker_news
group by 1;
select timestamp
from hacker_news
limit 10;
select timestamp, strftime('%H', timestamp)
from hacker_news
group by 1
limit 20;
select timestamp, strftime('%H', timestamp), avg(score), count(title)
from hacker_news
group by 1
limit 20;
select timestamp, strftime('%H', timestamp) as hours, round(avg(score),0) as avg_score, count(title) as total_stories
from hacker_news
where timestamp is not null
group by 1
order by 3 desc
limit 20;