1. -- all users who is registered with knewton enabled products.
2. SELECT
3. distinct enr.enrolledtoid classid,
4. enr.userid,
5. enr.createddatetime,
6. cpm.contentcontainerid product_id
7. FROM
8. dw.dimuserenrollment enr
9. INNER JOIN
10. dw.dimclassproductassociation cpm on
11. enr.enrolledtoid=cpm.organizationid
12. where  UPPER(cpm.containertype)='PRODUCT' and UPPER(cpm.organizationtype)='CLASS'
13. AND enr.roletype ='STUDENT'
14. AND enr.enrolledtotype='class'
15. AND enr.enrolledtoid IN
16. ( SELECT DISTINCT
17. class_id
18. FROM
19. license_research.knewton_classes
20. ) limit 100;
21.  
22. -- how many users who has assignments in knewton enabled products.
23. --select ext_realize_producttitle from dw.dimcontentcontainermapping limit 1000;
24. SELECT
25. enr.enrolledtoid classid,
26. enr.userid,
27. enr.isdeleted enrollment_delete_flag,
28. enr.status    enrollment_status ,
29. con.contentassignmentid,
30. con.assignmentid ,
31. con.contentid,
32. con.type              assignment_type,
33. con.status        assignment_status,
34. con.ext_realize_title assignment_name ,
35. con.isdeleted     assignmnet_deleteflag,
36. map.ext_realize_producttitle
37. FROM
38. dw.dimuserenrollment enr
39. INNER JOIN
40. dw.dimcontentassignment con ON enr.userid=con.assigneeid
41. INNER JOIN dw.dimcontentcontainermapping map ON con.contentid=map.contentid
42. and enr.enrolledtoid = con.ext_realize_classid
43. WHERE
44. enr.roletype ='STUDENT'
45. AND enr.enrolledtoid IN
46. ( SELECT DISTINCT
47. class_id
48. FROM
49. license_research.knewton_classes
50. ) limit 100
51.  
52. -- how many users who has knewton adaptive assignments.
53. SELECT
54. enr.enrolledtoid classid,
55. enr.userid,
56. enr.isdeleted enrollment_delete_flag,
57. enr.status    enrollment_status ,
58. con.contentassignmentid,
59. con.assignmentid ,
60. con.contentid,
61. con.type              assignment_type,
62. con.status        assignment_status,
63. con.ext_realize_title assignment_name ,
64. con.isdeleted     assignmnet_deleteflag
65. FROM
66. dw.dimuserenrollment enr
67. INNER JOIN
68. dw.dimcontentassignment con ON enr.userid=con.assigneeid
69. INNER JOIN dw.dimcontent content ON con.contentid = content.contentassetid
70. and lower(content.adaptive)='knewton'
71. and enr.enrolledtoid = con.ext_realize_classid
72. and enr.intpartnerid = 'easybridge'
73. WHERE
74. enr.roletype ='STUDENT'
75. AND enr.enrolledtoid IN
76. ( SELECT DISTINCT
77. class_id
78. FROM
79. license_research.knewton_classes
80. ) limit 100;