Logs Analysis Project

Project Description

Project_Specification

Questions

1. What are the most popular three articles of all time? Which articles have been accessed the most? Present this information as a sorted list with the most popular article at the top
2. Who are the most popular article authors of all time? That is, when you sum up all of the articles each author has written, which authors get the most page views? Present this as a sorted list with the most popular author at the top.
3. On which days did more than 1% of requests lead to errors? The log table includes a column status that indicates the HTTP status code that the news site sent to the user's browser.

Requirements

• Python 3.5.3
• psycopg2
• Postgresql 9.6

How to run

• load the data onto the database

psql -d news -f newsdata.sql

• connect to the database

psql -d news

• create views
• python3 LogsAnalysis.py

Create Views

CREATEVIEWauthor_infoASSELECTauthors.name, articles.title, articles.slugFROM articles, authors
WHEREarticles.author=authors.idORDER BYauthors.name;

CREATEVIEWpath_viewASSELECTpath, COUNT(*) AS view
FROM log
GROUP BYpathORDER BYpath;

CREATEVIEWarticle_viewASSELECTauthor_info.name, author_info.title, path_view.viewFROM author_info, path_view
WHEREpath_view.path= CONCAT('/article/', author_info.slug)
ORDER BYauthor_info.name;

CREATEVIEWtotal_viewASSELECTdate(time), COUNT(*) AS views
FROM log GROUP BYdate(time)
ORDER BYdate(time);

CREATEVIEWerror_viewASSELECTdate(time), COUNT(*) AS errors
FROM log WHERE status ='404 NOT FOUND'GROUP BYdate(time) ORDER BYdate(time);

CREATEVIEWerror_rateASSELECTtotal_view.date, (100.0*error_view.errors/total_view.views) AS percentage
FROM total_view, error_view
WHEREtotal_view.date=error_view.dateORDER BYtotal_view.date;