Calculate mAP@k and mP@k in SQL

map@k.sql

/*
is_relevant_col is
 - '0' for irrelevant recommendations (negatives)
 - '1' for relevant recommendations (positives)
*/

WITH t1 AS ( -- Precition@k per row
    SELECT *,
        avg(is_relevant_col)
            OVER(
                PARTITION BY customer_col
                ORDER BY confidence_col DESC
                RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS precision_k_col
    FROM `{recommendations_table}`
    ORDER BY customer_col, confidence_col DESC
), 
t2 AS ( -- Exclude negatives from calculation
    Select *, is_relevant_col * precision_k_col AS calc_precision_col
    From t1
    ORDER BY customer_col, confidence_col DESC
),
t3 AS ( -- Calc avg precition per customer
    SELECT customer_col, IFNULL(round(avg(NULLIF(calc_precision_col, 0)), 4), 0) AS avg_precision_col
    FROM t2
    GROUP BY customer_col
    ORDER BY customer_col
) -- Calc mean Average Precition@k for model
Select avg(avg_precision_col) AS mAP_k
FROM {ap_table}

mp@k.sql

/*
is_relevant_col is
 - '0' for irrelevant recommendations (negatives)
 - '1' for relevant recommendations (positives)
*/

WITH t1 AS ( -- Precition@k per row
    SELECT *,
        avg(is_relevant_col)
            OVER(
                PARTITION BY customer_col
                ORDER BY confidence_col DESC
                RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS precision_k_col
    FROM `{recommendations_table}`
    ORDER BY customer_col, confidence_col DESC
),
t2 AS (  -- Calc precition per customer
    SELECT customer_col, round(avg(precision_k_col), 4) AS precision_col
    FROM t1
    GROUP BY customer_col
    ORDER BY customer_col 
) -- Calc mean precition@k of model
SELECT round(avg(precision_col), 4) AS mP_k
FROM t2