-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.py
34 lines (31 loc) · 870 Bytes
/
queries.py
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
"""SQL queries for logs-analysis."""
TOP3_ARTICLES = """
SELECT articles.title, count(*) AS views
FROM log, articles
WHERE log.path = '/article/' || articles.slug
GROUP BY articles.id
ORDER BY views DESC
LIMIT 3;
"""
TOP_AUTHORS = """
SELECT authors.name, sum(views_by_article) AS views_by_author
FROM (
SELECT articles.author AS author, count(*) AS views_by_article
FROM log, articles
WHERE log.path = '/article/' || articles.slug
GROUP BY articles.id
ORDER BY views_by_article
) AS views_by_article_table, authors
WHERE author = authors.id
GROUP BY authors.id
ORDER BY views_by_author DESC;
"""
ERROR_PERCENTAGE = """
SELECT *
FROM (
SELECT edb.day, (edb.errors * 100)::decimal / rbd.requests AS percentage
FROM errors_by_day edb, requests_by_day rbd
WHERE edb.day = rbd.day
) AS percentage_table
WHERE percentage > 1.0;
"""