-- all users who is registered with knewton.

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
enr.enrolledtoid classid,
enr.userid,
enr.isdeleted enrollment_delete_flag,
enr.status    enrollment_status ,
enr.createddatetime,
con.contentassignmentid,
con.assignmentid ,
con.contentid,
con.type              assignment_type,
con.status        assignment_status,
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 ,
enr.createddatetime,
con.contentassignmentid,
con.assignmentid ,
con.contentid,
con.type              assignment_type,
con.status        assignment_status,
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.dimcontent content ON con.contentid = content.contentassetid
INNER JOIN dw.dimcontentcontainermapping map ON con.contentid=map.contentid
and lower(content.adaptive) = 'knewton'
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;'