lesson_progress_aggregates

Who watched each lesson. A list of user IDs who progressed through each lesson — ready for cohort joins without scanning the entire user_lesson_progress table.

• Grain: (id_tenant, id_lesson) — one row per lesson
• ETL strategy: merge
• PK: (id_tenant, id_lesson)

When to use it

• Cohorts: "did learners who watched lesson X also watch Y?"
• Funnel: how many learners reached the final lesson of the product?
• Tagging: build an automatic "watched intro" tag from the array.

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
id_lesson	BIGINT	FK → lessons.id. Part of PK.
user_ids	BIGINT[]	Array of user IDs who watched the lesson.
progress_total	INTEGER	Cumulative action sum for the lesson (reminder: each +1 = one completion).
updated_at	TIMESTAMPTZ	Last update of the aggregate.

Usage pattern

-- Distinct learners per lesson
SELECT id_lesson, array_length(user_ids, 1) AS viewers
FROM metrics.lesson_progress_aggregates
ORDER BY viewers DESC NULLS LAST
LIMIT 20;

-- Learners who watched lesson X but not lesson Y (Y is the next step in the funnel)
SELECT id_user
FROM unnest(
    (SELECT user_ids FROM metrics.lesson_progress_aggregates WHERE id_lesson = 1001)
) AS id_user
EXCEPT
SELECT id_user
FROM unnest(
    (SELECT user_ids FROM metrics.lesson_progress_aggregates WHERE id_lesson = 1002)
);