Use MySQL Window Functions and CTEs for WordPress Analytics Queries

MySQL 8.0 added window functions (ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), etc.) and Common Table Expressions (CTEs via the WITH clause) — two features that transform complex reporting queries on WordPress data from multi-step PHP post-processing into single, declarative SQL statements that the query optimizer can execute efficiently. A window function computes an aggregate over a “window” of rows related to the current row without collapsing the result set like GROUP BY does — SUM(views) OVER (PARTITION BY author_id ORDER BY post_date) produces a running total of views per author alongside each individual post row. A CTE defines a named temporary result set that can be referenced multiple times in the same query, replacing nested subqueries with readable, reusable query blocks — particularly useful for multi-step WordPress analytics like “posts that are in the top 10% of their category by comment count”. Practical WordPress use cases: ranking posts by views within their category using DENSE_RANK() OVER (PARTITION BY category ORDER BY views DESC); computing week-over-week revenue change in a WooCommerce store using LAG(revenue) OVER (ORDER BY week); finding the median comment count across all posts using a CTE with ROW_NUMBER() and COUNT(*)/2; generating a cohort analysis of users who registered in a given month and their publishing activity. Recursive CTEs go further — a recursive CTE on wp_term_taxonomy can walk the parent-child category hierarchy to any depth, replacing the WordPress get_term_children() PHP loop with a single SQL query. Window functions require MySQL 8.0+ or MariaDB 10.2+ — verify the database version before using these features in a plugin intended for broad distribution. The full-text search post covers query performance for search; window functions cover the analytics reporting layer.

Problem: A WordPress editorial dashboard needs to show each post’s view count alongside its rank within its category, plus a running total of views per author, and flag posts whose view count is below the category median — currently implemented as three separate PHP WP_Query calls with array sorting and looping that runs for 3+ seconds on 10,000 posts.

Solution: Replace the three PHP loops with a single SQL query using window functions and a CTE — the database engine computes rankings, running totals, and median comparisons in one pass over the data, returning the complete result set in under 200ms.

-- ── CTE + Window Functions: post analytics dashboard ─────────────────────
WITH
-- Step 1: gather base data — posts with their primary category and view count
post_stats AS (
    SELECT
        p.ID,
        p.post_title,
        p.post_author,
        p.post_date,
        t.term_id                         AS category_id,
        t.name                            AS category_name,
        COALESCE( CAST(pm.meta_value AS UNSIGNED), 0 ) AS views
    FROM   wp_posts          p
    JOIN   wp_term_relationships tr ON tr.object_id = p.ID
    JOIN   wp_term_taxonomy      tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                                   AND tt.taxonomy = 'category'
    JOIN   wp_terms              t  ON t.term_id = tt.term_id
    LEFT   JOIN wp_postmeta      pm ON pm.post_id = p.ID
                                   AND pm.meta_key = '_post_views_count'
    WHERE  p.post_status = 'publish'
      AND  p.post_type   = 'post'
),

-- Step 2: compute per-category median using PERCENTILE_CONT approximation
category_medians AS (
    SELECT
        category_id,
        AVG(views) AS avg_views,
        -- Median: row at position COUNT/2 using ROW_NUMBER trick
        MAX( CASE WHEN rn_asc = rn_desc OR rn_asc + 1 = rn_desc THEN views END ) AS median_views
    FROM (
        SELECT
            category_id, views,
            ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY views ASC  ) AS rn_asc,
            ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY views DESC ) AS rn_desc
        FROM post_stats
    ) ranked
    GROUP BY category_id
)

-- Final: combine everything
SELECT
    ps.ID,
    ps.post_title,
    ps.category_name,
    ps.views,
    -- Rank within category (1 = most viewed)
    DENSE_RANK() OVER (
        PARTITION BY ps.category_id
        ORDER BY ps.views DESC
    )                                         AS category_rank,
    -- Running total of views per author over time
    SUM( ps.views ) OVER (
        PARTITION BY ps.post_author
        ORDER BY ps.post_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                         AS author_cumulative_views,
    -- Flag below-median posts
    CASE WHEN ps.views < cm.median_views THEN 1 ELSE 0 END AS below_median,
    cm.median_views
FROM      post_stats       ps
JOIN      category_medians cm ON cm.category_id = ps.category_id
ORDER BY  ps.category_name, category_rank;

// Execute the window function query via $wpdb (MySQL 8.0+ required)
function myplugin_get_post_analytics(): array {
    global $wpdb;

    // Use prepare() for any user-supplied values — this query has none,
    // but always validate and escape before interpolating variables.
    $sql = "
        WITH post_stats AS (
            SELECT p.ID, p.post_title, p.post_author, p.post_date,
                   t.term_id AS category_id, t.name AS category_name,
                   COALESCE(CAST(pm.meta_value AS UNSIGNED), 0) AS views
            FROM   {$wpdb->posts}             p
            JOIN   {$wpdb->term_relationships} tr ON tr.object_id = p.ID
            JOIN   {$wpdb->term_taxonomy}      tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
                                                  AND tt.taxonomy = 'category'
            JOIN   {$wpdb->terms}              t  ON t.term_id = tt.term_id
            LEFT JOIN {$wpdb->postmeta}        pm ON pm.post_id = p.ID
                                                  AND pm.meta_key = '_post_views_count'
            WHERE  p.post_status = 'publish' AND p.post_type = 'post'
        )
        SELECT ID, post_title, category_name, views,
               DENSE_RANK() OVER (PARTITION BY category_id ORDER BY views DESC) AS category_rank,
               SUM(views)   OVER (PARTITION BY post_author  ORDER BY post_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS author_cumulative_views
        FROM   post_stats
        ORDER  BY category_name, category_rank
        LIMIT  500
    ";

    $cache_key = 'myplugin_post_analytics_v1';
    $results   = get_transient( $cache_key );
    if ( false === $results ) {
        $results = $wpdb->get_results( $sql, ARRAY_A );
        set_transient( $cache_key, $results, 15 * MINUTE_IN_SECONDS );
    }
    return $results ?: [];
}

NOTE: Window functions and CTEs require MySQL 8.0+ or MariaDB 10.2+. Many shared hosting providers still run MySQL 5.7, which does not support these features and will throw a syntax error. Check the server version with SELECT VERSION() before deploying, and add a version check in your plugin’s activation hook: if ( version_compare( $wpdb->db_version(), '8.0', '<' ) ) { /* show admin notice */ }. For plugins targeting broad compatibility, implement a PHP fallback path that achieves the same result with multiple simple queries and PHP sorting when MySQL 8.0 is unavailable.