Untitled query #88812

SQL

-- Query to fetch article pageviews for the past 90 days
-- Replace PSID with the actual PetScan ID

WITH petscan_results AS (
    -- Retrieve article titles from PetScan results
    SELECT
        REPLACE(
            JSON_EXTRACT(page_json, '$.title'),
            '\"',
            ''
        ) AS article_title
    FROM
        petscan_pstags
    WHERE
        ps_id = 30391192
),

-- Query pageview data for the articles
pageviews AS (
    SELECT
        page_title,
        SUM(page_count) AS total_views
    FROM
        tawiki_p.pageviews
    WHERE
        pv_timestamp > DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY), '%Y%m%d') -- Past 90 days
        AND page_title IN (SELECT article_title FROM petscan_results)
    GROUP BY
        page_title
)

-- Final output
SELECT
    page_title AS article,
    total_views
FROM
    pageviews
ORDER BY
    total_views DESC;
SQL

Checking query status...
