Untitled query #88812

SQL

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

-- Query to get view counts for the articles
view_counts AS (
    SELECT
        article_title,
        SUM(view_count) AS total_views
    FROM
        wmf.pageview_hourly
    WHERE
        project = 'ta.wikipedia'
        AND access_method = 'all-access'
        AND agent_type = 'user'
        AND to_timestamp(concat(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))) > CURRENT_DATE - INTERVAL 90 DAY
        AND article_title IN (SELECT article_title FROM petscan_results)
    GROUP BY
        article_title
)

-- Final result: Article titles and their view counts
SELECT
    article_title,
    total_views
FROM
    view_counts
ORDER BY
    total_views DESC;
SQL

Checking query status...
