In this repository you will find my solution to the Logs Analysis project from Udacity's Full Stack Nanodegree. This project explores basic concepts regarding Python, Relational Databases, Queries and Views by analyzing a fictional news website database.
-
- What are the most popular three articles of all time?
-
- Who are the most popular article authors of all time?
-
- On which days did more than 1% of requests lead to errors?
Download link: https://www.python.org/downloads/
Download link: https://www.virtualbox.org/wiki/Downloads
Step One - Download and install vagrant from https://www.vagrantup.com/downloads.html
Step Two - cd
into the vagrant directory with your shell(windows - Git Bash, Linux/Mac OS- Terminal)
Step Three - run the command vagrant up
with your shell in order to turn on the virtual machine
and then run vagrant ssh
in order to get into the virtual machine shell
Follow the detailed instructions on PostgreSQL's website on the following link: https://www.postgresql.org/download/linux/ubuntu/
Step One - Download the database file from https://d17h27t6h515a5.cloudfront.net/topher/2016/August/57b5f748_newsdata/newsdata.zip
Step Two - Unzip the file and place newsdata.sql into the vagrant directory on your local machine
Step Three - cd
into the vagrant directory with your shell(windows - Git Bash, Linux/Mac OS- Terminal)
and run the command psql -d news -f newsdata.sql
- CREATE VIEW article_authors as SELECT articles.slug ,authors.name FROM authors, articles WHERE articles.author =authors .id;
View for showing the amount of entries with errors for each date in the log table(used in the third query)
- CREATE VIEW as failed_entries SELECT date(time),count(log.status) FROM log WHERE log.status NOT LIKE '%200%' group by date(time) ORDER BY date(time) DESC;
View for showing the amount of entries in total for each date in the log table(used in the third query)
- CREATE VIEW all_entries as SELECT date(time),count(log.status) FROM log group by date(time) ORDER BY date(time) DESC;
first_query() - The solution for the first query in the project
second_query() - The solution for the second query in the project
third_query() - The solution for the third query in the project