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