daily_lesson_events

Lesson interaction volume per day. Shows which lessons are being consumed — useful to rank content and identify "forgotten" lessons.

• Grain: (id_tenant, id_lesson, date)
• ETL strategy: merge
• PK: (id_tenant, id_lesson, date)

When to use it

• Top lessons by consumption for the week / month.
• Lessons with sudden access drops (signal of a tech issue or a funnel change).
• A/B analysis of new lessons vs. legacy content.

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
id_lesson	BIGINT	FK → lessons.id. Lesson in question. Part of PK.
date	DATE	Day. Part of PK.
total	INTEGER	Total events recorded on the lesson that day (views + completions + revisits — every hit, no user dedup).

For "distinct learners" per lesson (not events), use lesson_progress_aggregates.

Usage pattern

-- Top lessons of the week
SELECT id_lesson, sum(total) AS events
FROM metrics.daily_lesson_events
WHERE date >= current_date - interval '7 days'
GROUP BY id_lesson
ORDER BY events DESC
LIMIT 20;

-- Lessons that lost > 50% week-over-week
WITH this_week AS (
    SELECT id_lesson, sum(total) AS current
    FROM metrics.daily_lesson_events
    WHERE date BETWEEN current_date - 7 AND current_date - 1
    GROUP BY id_lesson
),
last_week AS (
    SELECT id_lesson, sum(total) AS previous
    FROM metrics.daily_lesson_events
    WHERE date BETWEEN current_date - 14 AND current_date - 8
    GROUP BY id_lesson
)
SELECT a.id_lesson, previous, current,
       round(100.0 * (current - previous) / NULLIF(previous, 0), 1) AS variation_pct
FROM last_week a JOIN this_week b USING (id_lesson)
WHERE previous >= 100 AND current < previous * 0.5
ORDER BY variation_pct;