過去2週間分の登録者数から、着地予想を出す。(線形近似)

2weeks-forecast.sql

SELECT
    SUM(
      CASE WHEN registered_at >= DATE_TRUNC('month', now()) THEN 1 ELSE 0 END
    ) AS COUNT,
    SUM(
      CASE WHEN registered_at >= DATE_TRUNC('month', now()) THEN 1 ELSE 0 END
    ) + SUM(
      CASE WHEN registered_at BETWEEN DATE_TRUNC('day', now() + '-14 days') AND DATE_TRUNC('day', now()) THEN 1 ELSE 0 END
    ) / 14 * (
      date_part('day', DATE_TRUNC('month', now() + '1 months') + '-1 days') - date_part('day', DATE_TRUNC('day', now()))
    ) AS forecast
FROM user
WHERE registered_at >= DATE_TRUNC('month', now() + '-14 days')

forecast = 現在の登録者数 + 過去14日間の平均デイリー登録者数 * 今月の残り日数