CREATE VIEW vwlearningmodulecompletionanalytics AS (
 SELECT lm.id AS module_id,
    lm.name AS module_name,
    lm.description AS module_description,
    lm.type AS module_type,
    lm.state AS module_state,
    count(DISTINCT lma.userid) AS total_assigned_users,
    count(DISTINCT
        CASE
            WHEN (lma.isoverdue = true) THEN lma.userid
            ELSE NULL::character varying
        END) AS overdue_users,
    count(DISTINCT
        CASE
            WHEN ((lma.state)::text = 'Assigned'::text) THEN lma.userid
            ELSE NULL::character varying
        END) AS active_assignments,
    count(DISTINCT lar.userid) AS completed_users,
    round((((count(DISTINCT lar.userid))::numeric / (NULLIF(count(DISTINCT lma.userid), 0))::numeric) * (100)::numeric), 2) AS completion_rate_percentage,
    round(avg(lar.assessmentpercentagescore), 2) AS avg_assessment_score,
    round(avg(lar.completionpercentage), 2) AS avg_completion_percentage,
    round(avg(lar.lengthinminutes), 2) AS avg_duration_minutes,
    count(DISTINCT
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL) AND (lar.assessmentpercentagescore >= lar.passpercent)) THEN lar.userid
            ELSE NULL::character varying
        END) AS passed_users,
    count(DISTINCT
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL) AND (lar.assessmentpercentagescore < lar.passpercent)) THEN lar.userid
            ELSE NULL::character varying
        END) AS failed_users,
    round((((count(DISTINCT
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL) AND (lar.assessmentpercentagescore >= lar.passpercent)) THEN lar.userid
            ELSE NULL::character varying
        END))::numeric / (NULLIF(count(DISTINCT
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL)) THEN lar.userid
            ELSE NULL::character varying
        END), 0))::numeric) * (100)::numeric), 2) AS pass_rate_percentage,
    min(lma.dateassigned) AS earliest_assignment_date,
    max(lma.dateassigned) AS latest_assignment_date,
    min(lar.datecreated) AS earliest_completion_date,
    max(lar.datemodified) AS latest_completion_date
   FROM ((learningmodules lm
     LEFT JOIN learningmoduleassignments lma ON (((lm.id)::text = (lma.moduleid)::text)))
     LEFT JOIN learningassignmentresults lar ON ((((lma.userid)::text = (lar.userid)::text) AND ((lma.moduleid)::text = (lar.moduleid)::text))))
  WHERE ((lm.state)::text <> 'deleted'::text)
  GROUP BY lm.id, lm.name, lm.description, lm.type, lm.state
  ORDER BY (round((((count(DISTINCT lar.userid))::numeric / (NULLIF(count(DISTINCT lma.userid), 0))::numeric) * (100)::numeric), 2)) DESC, lm.name
)