SQL queries for stats (weekly active users, weekly posts, etc.)

Weekly Active Users (users who viewed at least one post)

WITH WeeklyUserCounts AS (
    SELECT
        DISTINCT DATE_TRUNC('week', "createdAt") AS week_end,
        COUNT(*) OVER (ORDER BY DATE_TRUNC('week', "createdAt")) AS total_users
    FROM
        "Users"
    WHERE
    	deleted = false AND "acceptedTos" = true
)
SELECT
    TO_CHAR(DATE_TRUNC('week', e."createdAt"), 'YYYY-MM-DD') AS week_start,
    COUNT(DISTINCT e."userId") AS weekly_active_users,
    w.total_users AS total_users_at_week_end
FROM
    "LWEvents" e
JOIN
    WeeklyUserCounts w ON w.week_end = DATE_TRUNC('week', e."createdAt")
WHERE
    e."createdAt" >= '2023-11-06' AND
    e.name = 'post-view'
GROUP BY
    week_start, w.total_users
ORDER BY
    week_start;

Monthly Active Users

WITH MonthlyUserCounts AS (
    SELECT
        distinct DATE_TRUNC('month', "createdAt") AS month_end,
        COUNT(*) OVER (ORDER BY DATE_TRUNC('month', "createdAt")) AS total_users
    FROM
        "Users"
)
SELECT
    TO_CHAR(DATE_TRUNC('month', e."createdAt"), 'YYYY-MM-DD') AS month_start,
    COUNT(DISTINCT e."userId") AS monthly_active_users,
    m.total_users AS total_users_at_month_end
FROM
    "LWEvents" e
JOIN
    MonthlyUserCounts m ON m.month_end = DATE_TRUNC('month', e."createdAt")
WHERE
    e."createdAt" >= '2021-11-01' AND
    e.name = 'post-view'
GROUP BY
    month_start, m.total_users
ORDER BY
    month_start;

Weekly Active Content-Generating Users (users who made at least one post or comment)

WITH Weeks AS (
    SELECT
        DISTINCT DATE_TRUNC('week', "createdAt") AS week_end
    FROM
        "Users"
)
SELECT
    TO_CHAR(DATE_TRUNC('week', r."createdAt"), 'YYYY-MM-DD') AS week_start,
    COUNT(DISTINCT r."userId") AS weekly_active_users
FROM
    "Revisions" r
JOIN
    Weeks w ON w.week_end = DATE_TRUNC('week', r."createdAt")
WHERE
    r."createdAt" >= '2023-11-06' AND
    "updateType" = 'initial' AND
    "collectionName" in ('Posts', 'Comments')
GROUP BY
    week_start
ORDER BY
    week_start;

Total posts each week:

WITH Weeks AS (
    SELECT
        DISTINCT DATE_TRUNC('week', "createdAt") AS week_end
    FROM
        "Users"
)
SELECT
    TO_CHAR(DATE_TRUNC('week', p."createdAt"), 'YYYY-MM-DD') AS week_start,
    COUNT(*) AS weekly_posts
FROM
    "Posts" p
JOIN
    Weeks w ON w.week_end = DATE_TRUNC('week', p."createdAt")
WHERE
    p."createdAt" >= '2023-11-06'
GROUP BY
    week_start
ORDER BY
    week_start;

Total comments each week:

WITH Weeks AS (
    SELECT
        DISTINCT DATE_TRUNC('week', "createdAt") AS week_end
    FROM
        "Users"
)
SELECT
    TO_CHAR(DATE_TRUNC('week', c."createdAt"), 'YYYY-MM-DD') AS week_start,
    COUNT(*) AS weekly_comments
FROM
    "Comments" c
JOIN
    Weeks w ON w.week_end = DATE_TRUNC('week', c."createdAt")
WHERE
    c."createdAt" >= '2023-11-06'
GROUP BY
    week_start
ORDER BY
    week_start;