你不能使用 lag 包含 COUNT aggregate函数，因为使用aggregate function contain aggregate函数时它无效。
您可以尝试使用子查询来创建它。

SELECT weeks_ago,
       NewRecords "New Records in Cohort",
      100 * (cnt - lag(cnt, 1) over (order by created_at)) / lag(cnt, 1) over (order by created_at) || '%' as growth
FROM (
    SELECT floor(datediff(f.created_at, curdate()) / 7) AS weeks_ago, 
           COUNT(*) over(partition by weeks_ago order by weeks_ago) cnt,
           count(DISTINCT f.id) NewRecords,
           f.created_at
    FROM answers f
) t1

我想您需要查找除当前行以外的所有行的运行计数。我想你可以把这个 LAG 功能如下：

SELECT
    COUNT(*) OVER (ORDER BY f.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) x, -- running count before current row
    COUNT(*) OVER (ORDER BY f.created_at) y -- running count including current row

你可以随心所欲地除法和乘法。
不。你只需要分开 GROUP BY 以及 LAG OVER :

WITH cte AS (
    SELECT
        FLOOR(DATEDIFF(created_at, CURDATE()) / 7) AS weeks_ago,
        COUNT(DISTINCT id) AS new_records
    FROM answers
    WHERE 1 = 1 -- todo: change this
    GROUP BY weeks_ago
    HAVING 1 = 1 -- todo: change this
)
SELECT
    cte.*,
    100 * (
        new_records - LAG(new_records) OVER (ORDER BY weeks_ago)
    ) / LAG(new_records) OVER (ORDER BY weeks_ago) AS percent_increase
FROM cte

小提琴