user_product_progress

Per-user, per-product completion percentage. Derived table — fully recomputed on every ETL run from lessons, modules, products, and user_lesson_progress.

• ETL strategy: derived with write_disposition=replace (full snapshot)
• PK: (id_tenant, id_user, id_product) — composite
• Depends on: lessons, user_lesson_progress

Columns

Column	Type	Description
id_tenant	BIGINT NOT NULL	Part of PK.
id_user	BIGINT NOT NULL	Part of PK.
id_product	BIGINT NOT NULL	Part of PK.
progress	NUMERIC(5,2)	Percentage of completed lessons (0.00 → 100.00).
created_at	TIMESTAMPTZ	Snapshot timestamp (DEFAULT CURRENT_TIMESTAMP).

How it's computed

1. Build the valid lessons set = non-draft lessons + non-draft modules + non-draft product + everything with deleted_at IS NULL.
2. For each (id_user, id_lesson), sum action from user_lesson_progress — a lesson counts as completed if SUM(action) >= 1.
3. Filter to active users and tenants (deleted_at IS NULL).
4. progress = round(100.0 × completed_lessons / total_valid_lessons, 2).

Patterns

-- Users above 80% progress on any product
SELECT u.name, p.title, upp.progress
FROM lms.user_product_progress upp
JOIN lms.users u
    ON u.id = upp.id_user AND u.id_tenant = upp.id_tenant
JOIN lms.products p
    ON p.id = upp.id_product AND p.id_tenant = upp.id_tenant
WHERE upp.progress >= 80
ORDER BY upp.progress DESC;