most viewed Portals for the last 24 hours

SQL

USE arwiki_p;


WITH TopArticles AS (
    SELECT
        p.page_id,
        p.page_title,
        COUNT(*) AS view_count
    FROM
        page p
    JOIN
        revision r ON p.page_id = r.rev_page
    WHERE
        r.rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y%m%d%H%i%S') AND DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')
    GROUP BY
        p.page_id, p.page_title
)

, ArticlePortals AS (
    SELECT
        ta.page_id,
        ta.page_title,
        ta.view_count,
        cl.cl_to AS portal
    FROM
        TopArticles ta
    JOIN
        categorylinks cl ON ta.page_id = cl.cl_from
    WHERE
        cl.cl_to LIKE 'بوابة%'
)

SELECT
    ap.portal AS portal,
    SUM(ap.view_count) AS total_views
FROM
    ArticlePortals ap
GROUP BY
    ap.portal
ORDER BY
    total_views DESC
LIMIT 50;
SQL

Checking query status...
