this program analyses log data from a database using queries, which return the three most popular articles of all time, the three most popular authors of all time, and the days in which more than one percent of requests leads to an error.

• use a terminal
• (this program was written in python 2.7)
• download virtualbox
• download vagrant
• download the data and files needed
• after downloading both, in your terminal cd into the downloaded 'files' and cd into vagrant file from your downloads
• run vagrant up in your terminal to start up your vagrant
• to load the data, after you cd into the vagrant directory, use the command psql -d news -f newsdata.sql
• to test the data, run \dt to see how many tables there arguments
• run \dt table (where 'table' is name of table) to see the schema of each table e.g \dt log to see log table, or \dt authors to see authors table, and \dt articles to see articles table.

• to copy the project on your computer, open your terminal and do this: git clone e.g $ git clone <remote repo> <repo name>
• you will find the remote repo link above by clicking on the green "clone or download" option in tab above the repository
• then run cd <repo name> in your terminal ( is whatever name you gave your repository in the terminal)
• this should open up the folder you just cloned from github
• open up your code editor

• to run the code run the following command in your terminal: python news.py;

No views were created for the first query.

• first view : CREATE OR REPLACE VIEW article_views as SELECT title, count(*) as views FROM articles a, log l WHERE a.slug = substring(l.path,10) GROUP BY title ORDER BY views;
• second view : CREATE OR REPLACE VIEW article_author as SELECT articles.title, authors.name FROM authors, articles WHERE articles.author = authors.id GROUP BY name, title;
• third view : CREATE OR REPLACE VIEW title_views_author as SELECT * FROM article_views natural join article_author;

CREATE OR REPLACE VIEW error_view as SELECT date(time) as date, round(100.0*sum(case log.status when '200 OK' then 0 else 1 end)/count(log.status),2) as percentage_error FROM log GROUP BY date ORDER BY percentage_error desc;

it's a good example to learn working on relational databases using psql and python.

this code follows the mit license