CREATE OR REPLACE VIEW AUTHOR_ARTICLE_VIEW AS
SELECT A.NAME,
       AE.TITLE,
       L.TIME,
       to_char(L.TIME::date, 'MonthDD, YYYY') AS DAY,
       COUNT(AE.TITLE) AS VIEWED_ARTICLES,
       L.STATUS
FROM AUTHORS A
INNER JOIN ARTICLES AE ON A.ID = AE.AUTHOR
RIGHT JOIN LOG L ON AE.SLUG = SUBSTRING(L.PATH FROM 10)

GROUP BY A.NAME,
         AE.TITLE,
         L.TIME,
         L.STATUS
ORDER BY A.NAME ASC;

CREATE OR REPLACE VIEW TOP_ARTICLES_VIEW AS
SELECT NAME,
       TITLE,
       SUM(VIEWED_ARTICLES) AS TOTAL_VIEWED_ARTICLES
FROM AUTHOR_ARTICLE_VIEW
WHERE NAME IS NOT NULL
  AND STATUS = '200 OK'
GROUP BY NAME,
         TITLE
ORDER BY SUM(VIEWED_ARTICLES) DESC
LIMIT 3;

CREATE OR REPLACE VIEW AUTHORS_ALL_TIMES_VIEW AS
SELECT NAME,
       SUM(TOTAL_VIEWED_ARTICLES)
FROM
  ( SELECT NAME,
           TITLE,
           SUM(VIEWED_ARTICLES) AS TOTAL_VIEWED_ARTICLES
   FROM AUTHOR_ARTICLE_VIEW
   WHERE NAME IS NOT NULL
     AND STATUS = '200 OK'
   GROUP BY NAME,
            TITLE
   ORDER BY SUM(VIEWED_ARTICLES) DESC) AS TOP_ARTICLES
GROUP BY NAME
ORDER BY SUM(TOTAL_VIEWED_ARTICLES) DESC;

CREATE OR REPLACE VIEW VISITS_PER_DAY_VIEW AS
SELECT DAY,
       TOTAL_ACCESS_COUNT,
       SUCCESS,
       FAILURE,
       ROUND((FAILURE::decimal * 100) / TOTAL_ACCESS_COUNT, 1) AS PERCENTAGE_FAILURE
FROM
  ( SELECT DAY,
           COUNT(AA.STATUS) AS TOTAL_ACCESS_COUNT,

     ( SELECT COUNT(AB.STATUS)
      FROM AUTHOR_ARTICLE_VIEW AB
      WHERE AB.STATUS = '200 OK'
        AND AB.DAY = AA.DAY ) AS SUCCESS,

     ( SELECT COUNT(AC.STATUS)
      FROM AUTHOR_ARTICLE_VIEW AC
      WHERE AC.STATUS <> '200 OK'
        AND AC.DAY = AA.DAY ) AS FAILURE
   FROM AUTHOR_ARTICLE_VIEW AA
   GROUP BY DAY) VISITS
ORDER BY ROUND((FAILURE::decimal * 100) / TOTAL_ACCESS_COUNT, 1) DESC;