lesson_rating_aggregates

Lesson NPS — who's enjoying the content. Likes/dislikes consolidated per lesson, ready for perceived-quality ranking.

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

When to use it

• Best-rated lesson ranking → top showcase candidates.
• Lessons with negative balance (more dislikes than likes) → priority for review.
• Tenant-level content quality score.

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
id_lesson	BIGINT	FK → lessons.id. Part of PK.
total	INTEGER	Total votes received (likes + dislikes).
likes	INTEGER	Positive votes (vote = +1).
dislikes	INTEGER	Negative votes (vote = -1).
updated_at	TIMESTAMPTZ	Last update of the aggregate.

Usage pattern

-- Top 20 best-rated lessons (min. 50 votes)
SELECT id_lesson,
       likes,
       dislikes,
       total,
       round(100.0 * likes / NULLIF(total, 0), 1) AS likes_pct
FROM metrics.lesson_rating_aggregates
WHERE total >= 50
ORDER BY likes_pct DESC, total DESC
LIMIT 20;

-- Most "controversial" lessons (close to 50/50 with high volume)
SELECT id_lesson, likes, dislikes,
       abs(likes - dislikes) AS imbalance
FROM metrics.lesson_rating_aggregates
WHERE total >= 100
ORDER BY imbalance ASC
LIMIT 10;