-- all users who is registered with knewton enabled products.
SELECT
distinct enr.enrolledtoid classid,
enr.userid,
enr.createddatetime,
cpm.contentcontainerid product_id
FROM
dw.dimuserenrollment enr 
INNER JOIN 
dw.dimclassproductassociation cpm on 
enr.enrolledtoid=cpm.organizationid
where  UPPER(cpm.containertype)='PRODUCT' and UPPER(cpm.organizationtype)='CLASS'
AND enr.roletype ='STUDENT' 
AND enr.enrolledtotype='class'
AND enr.enrolledtoid IN
( SELECT DISTINCT
class_id
FROM
license_research.knewton_classes
) limit 100;

-- how many users who has assignments in knewton enabled products.
--select ext_realize_producttitle from dw.dimcontentcontainermapping limit 1000;
SELECT
enr.enrolledtoid classid,
enr.userid,
enr.isdeleted enrollment_delete_flag,
enr.status    enrollment_status ,
con.contentassignmentid,
con.assignmentid ,
con.contentid,
con.type              assignment_type,
con.status        assignment_status,
con.ext_realize_title assignment_name ,
con.isdeleted     assignmnet_deleteflag,
map.ext_realize_producttitle
FROM
dw.dimuserenrollment enr
INNER JOIN
dw.dimcontentassignment con ON enr.userid=con.assigneeid
INNER JOIN dw.dimcontentcontainermapping map ON con.contentid=map.contentid
and enr.enrolledtoid = con.ext_realize_classid
WHERE
enr.roletype ='STUDENT' 
AND enr.enrolledtoid IN
( SELECT DISTINCT
class_id
FROM
license_research.knewton_classes
) limit 100

-- how many users who has knewton adaptive assignments.
SELECT
enr.enrolledtoid classid,
enr.userid,
enr.isdeleted enrollment_delete_flag,
enr.status    enrollment_status ,
con.contentassignmentid,
con.assignmentid ,
con.contentid,
con.type              assignment_type,
con.status        assignment_status,
con.ext_realize_title assignment_name ,
con.isdeleted     assignmnet_deleteflag
FROM
dw.dimuserenrollment enr
INNER JOIN
dw.dimcontentassignment con ON enr.userid=con.assigneeid
INNER JOIN dw.dimcontent content ON con.contentid = content.contentassetid
and lower(content.adaptive)='knewton'
and enr.enrolledtoid = con.ext_realize_classid
and enr.intpartnerid = 'easybridge'
WHERE
enr.roletype ='STUDENT' 
AND enr.enrolledtoid IN
( SELECT DISTINCT
class_id
FROM
license_research.knewton_classes
) limit 100;