select SEC_TO_TIME(SUM(TIME_TO_SEC(k.duration))) as total_duration,g.total_book_count,count(k.series_mstr_token) as number_of_lesson,h.name as sub_subject_name,a.create_date as last_login_date,a.student_name, a.student_id,d.name as class_name,c.name as section_name,g.name as subject_name from student a,class b,section c,all_classes d,series e,subject f,all_subjects g,all_sub_subjects h,sub_subject i, lesson_series_master j,lesson_master k where a.school_token='qWL1ZO6' and b.school_token=a.school_token and c.class_token=b.token and d.token=b.class_name and a.school_token=b.school_token and c.school_token=b.school_token and a.class_token=b.token and a.section_token=c.token and c.class_token=b.token and a.status='1' and b.status='1' and c.status='1' and a.firebase_token is not null and e.school_token=a.school_token and e.class_token=b.token and e.status='1' and e.token=f.series_token and a.school_token=f.school_token and a.class_token=f.class_token and f.name=g.token and g.sub_subject_status='Y' and f.token=i.subject_token and i.name=h.token and i.series_token=e.token and a.school_token=i.school_token and i.class_token=a.class_token and g.sub_subject_status='Y' and j.class_tkn_cmn=b.class_name and j.series_tkn_cmn=e.name and j.subject_tkn_cmn=f.name and j.sub_subject_tkn_cmn=i.name and k.series_mstr_token=j.token group by g.name,a.create_date,a.student_name,a.student_id,d.name,c.name,k.series_mstr_token,h.name,g.total_book_count union all select SEC_TO_TIME(SUM(TIME_TO_SEC(k.duration))) as total_duration,g.total_book_count,count(k.series_mstr_token) as number_of_lesson,'NA' as sub_subject_name,a.create_date as last_login_date,a.student_name,a.student_id,d.name as class_name, c.name as section_name,g.name as subject_name from student a,class b,section c,all_classes d,series e,subject f,all_subjects g,lesson_series_master j, lesson_master k where a.school_token='qWL1ZO6' and b.school_token=a.school_token and c.class_token=b.token and d.token=b.class_name and a.school_token=b.school_token and c.school_token=b.school_token and a.class_token=b.token and a.section_token=c.token and c.class_token=b.token and a.status='1' and b.status='1' and c.status='1' and a.firebase_token is not null and e.school_token=a.school_token and e.class_token=b.token and e.status='1' and e.token=f.series_token and a.school_token=f.school_token and a.class_token=f.class_token and f.name=g.token and g.sub_subject_status='N' and j.class_tkn_cmn=b.class_name and j.series_tkn_cmn=e.name and j.subject_tkn_cmn=f.name and j.sub_subject_tkn_cmn='NULL' and k.series_mstr_token=j.token group by g.name,a.create_date,a.student_name,a.student_id,d.name,c.name,k.series_mstr_token,g.total_book_count order by subject_name
OLS/OTT APP Utilization Chart(Subject Wise)
| S.No | User Id | Name Of the User | Class & Sec/ Group | Last Login in Date and Time | Subject / Books Wise Details | Watched lesson | Remaining Lesson | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Subject | No. Of Books | No. Of lessons | Total Duration (Video Content) MM:SS | No. Of Lessons | Watched Duration | No. Of Lessons | Total Remaining Duration | Utilized % | |||||||
| Once | Total | Once | Total | ||||||||||||
| 1 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | English | Rhyme Book | 30 | 29:28:00 | |||||||
| 2 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | English | Course Book | 46 | 406:40:00 | |||||||
| 3 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | English | Reader Book | 28 | 107:56:00 | |||||||
| 4 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | English | Course Book | 45 | 313:40:00 | |||||||
| 5 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | English | Reader Book | 30 | 240:46:00 | |||||||
| 6 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | English | Course Book | 13 | 74:35:00 | |||||||
| 7 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | English | Course Book | 20 | 158:25:00 | |||||||
| 8 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | English | Course Book | 13 | 74:35:00 | |||||||
| 9 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | English | Rhyme Book | 30 | 29:28:00 | |||||||
| 10 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | English | Course Book | 20 | 158:25:00 | |||||||
| 11 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | English | Rhyme Book | 30 | 29:28:00 | |||||||
| 12 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | English | Course Book | 46 | 406:40:00 | |||||||
| 13 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | English | Course Book | 46 | 406:40:00 | |||||||
| 14 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | English | Reader Book | 30 | 240:46:00 | |||||||
| 15 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | English | Reader Book | 30 | 240:46:00 | |||||||
| 16 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | English | Course Book | 43 | 321:51:00 | |||||||
| 17 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | English | Course Book | 46 | 406:40:00 | |||||||
| 18 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | English | Reader Book | 28 | 107:56:00 | |||||||
| 19 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | English | Reader Book | 30 | 240:46:00 | |||||||
| 20 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | English | Course Book | 46 | 406:40:00 | |||||||
| 21 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | English | Course Book | 20 | 158:25:00 | |||||||
| 22 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | English | Course Book | 43 | 321:51:00 | |||||||
| 23 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | English | Reader Book | 30 | 240:46:00 | |||||||
| 24 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | English | Course Book | 52 | 350:00:00 | |||||||
| 25 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | English | Reader Book | 28 | 107:56:00 | |||||||
| 26 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | English | Course Book | 13 | 74:35:00 | |||||||
| 27 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | English | Reader Book | 34 | 215:32:00 | |||||||
| 28 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | English | Course Book | 43 | 321:51:00 | |||||||
| 29 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | EVS | NA | 29 | 275:20:00 | |||||||
| 30 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | EVS | NA | 26 | 135:59:00 | |||||||
| 31 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | EVS | NA | 26 | 135:59:00 | |||||||
| 32 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | EVS | NA | 29 | 275:20:00 | |||||||
| 33 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | EVS | NA | 26 | 135:59:00 | |||||||
| 34 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | EVS | NA | 29 | 275:20:00 | |||||||
| 35 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | EVS | NA | 30 | 205:03:00 | |||||||
| 36 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | EVS | NA | 20 | 135:21:00 | |||||||
| 37 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | EVS | NA | 29 | 275:20:00 | |||||||
| 38 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | EVS | NA | 29 | 275:20:00 | |||||||
| 39 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | GK | NA | 19 | 122:01:00 | |||||||
| 40 | 230345 | vivan | KG 2/A | 2026-11-10 10:09:11 | GK | NA | 18 | 69:34:00 | |||||||
| 41 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | GK | NA | 18 | 82:40:00 | |||||||
| 42 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | GK | NA | 16 | 70:12:00 | |||||||
| 43 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | GK | NA | 19 | 122:01:00 | |||||||
| 44 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | GK | NA | 16 | 70:12:00 | |||||||
| 45 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | GK | NA | 19 | 122:01:00 | |||||||
| 46 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | GK | NA | 20 | 118:20:00 | |||||||
| 47 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | GK | NA | 20 | 118:20:00 | |||||||
| 48 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | GK | NA | 18 | 82:40:00 | |||||||
| 49 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | GK | NA | 19 | 122:01:00 | |||||||
| 50 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | GK | NA | 20 | 118:20:00 | |||||||
| 51 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | GK | NA | 16 | 70:12:00 | |||||||
| 52 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | GK | NA | 19 | 122:01:00 | |||||||
| 53 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | GK | NA | 19 | 126:47:00 | |||||||
| 54 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | GK | NA | 18 | 82:40:00 | |||||||
| 55 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | Hindi | Pathya Pustak | 41 | 248:43:00 | |||||||
| 56 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | Hindi | Shishu Geet | 38 | 34:43:00 | |||||||
| 57 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | Hindi | Pathya Pustak | 46 | 313:36:00 | |||||||
| 58 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | Hindi | Vachan Pustak | 32 | 158:36:00 | |||||||
| 59 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | Hindi | Pathya Pustak | 46 | 313:36:00 | |||||||
| 60 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 61 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | Hindi | Pathya Pustak | 15 | 201:46:00 | |||||||
| 62 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 63 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | Hindi | Pathya Pustak | 35 | 213:31:00 | |||||||
| 64 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | Hindi | Shishu Geet | 40 | 45:04:00 | |||||||
| 65 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | Hindi | Pathya Pustak | 46 | 313:36:00 | |||||||
| 66 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | Hindi | Vachan Pustak | 29 | 139:35:00 | |||||||
| 67 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | Hindi | Pathya Pustak | 15 | 201:46:00 | |||||||
| 68 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | Hindi | Pathya Pustak | 15 | 201:46:00 | |||||||
| 69 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 70 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | Hindi | Pathya Pustak | 46 | 313:36:00 | |||||||
| 71 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | Hindi | Shishu Geet | 40 | 45:04:00 | |||||||
| 72 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | Hindi | Shishu Geet | 40 | 45:04:00 | |||||||
| 73 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | Hindi | Pathya Pustak | 35 | 213:31:00 | |||||||
| 74 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 75 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | Hindi | Pathya Pustak | 53 | 353:47:00 | |||||||
| 76 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | Hindi | Vachan Pustak | 29 | 139:35:00 | |||||||
| 77 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | Hindi | Pathya Pustak | 8 | 63:32:00 | |||||||
| 78 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | Hindi | Vachan Pustak | 37 | 238:24:00 | |||||||
| 79 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | Hindi | Pathya Pustak | 35 | 213:31:00 | |||||||
| 80 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | Hindi | Shishu Geet | 38 | 34:43:00 | |||||||
| 81 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | Hindi | Pathya Pustak | 46 | 313:36:00 | |||||||
| 82 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | Hindi | Vachan Pustak | 29 | 139:35:00 | |||||||
| 83 | 230345 | vivan | KG 2/A | 2026-11-10 10:09:11 | Hindi | Pathya Pustak | 21 | 158:54:00 | |||||||
| 84 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 85 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | Hindi | Pathya Pustak | 8 | 63:32:00 | |||||||
| 86 | 230345 | vivan | KG 2/A | 2026-11-10 10:09:11 | Hindi | Vachan Pustak | 47 | 116:04:00 | |||||||
| 87 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | Hindi | Pathya Pustak | 8 | 63:32:00 | |||||||
| 88 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | Hindi | Shishu Geet | 38 | 34:43:00 | |||||||
| 89 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | Mathematics | NA | 39 | 505:32:00 | |||||||
| 90 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | Mathematics | NA | 28 | 96:26:00 | |||||||
| 91 | 055542 | Hardik | Class 3/A | 2026-10-24 20:13:37 | Mathematics | NA | 47 | 544:45:00 | |||||||
| 92 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | Mathematics | NA | 39 | 505:32:00 | |||||||
| 93 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | Mathematics | NA | 16 | 149:10:00 | |||||||
| 94 | 619550 | Nimesh | Class 3/A | 2026-11-02 08:34:03 | Mathematics | NA | 47 | 544:45:00 | |||||||
| 95 | 363724 | Param 4th | Class 4/A | 2026-07-22 20:53:10 | Mathematics | NA | 40 | 483:09:00 | |||||||
| 96 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | Mathematics | NA | 28 | 96:26:00 | |||||||
| 97 | 949801 | anil | Class 3/A | 2026-07-23 13:32:49 | Mathematics | NA | 47 | 544:45:00 | |||||||
| 98 | 230345 | vivan | KG 2/A | 2026-11-10 10:09:11 | Mathematics | NA | 24 | 295:09:00 | |||||||
| 99 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | Mathematics | NA | 28 | 96:26:00 | |||||||
| 100 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | Mathematics | NA | 53 | 803:01:00 | |||||||
| 101 | 560968 | Ruchi | Class 3/A | 2026-09-21 18:50:13 | Mathematics | NA | 47 | 544:45:00 | |||||||
| 102 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | Mathematics | NA | 16 | 149:10:00 | |||||||
| 103 | 888063 | Rajnandani chouhan | Class 3/A | 2026-09-28 09:20:51 | Mathematics | NA | 47 | 544:45:00 | |||||||
| 104 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | Mathematics | NA | 16 | 149:10:00 | |||||||
| 105 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | Mathematics | NA | 39 | 505:32:00 | |||||||
| 106 | 479075 | vishwas yadav | Nursery/A | 2026-10-22 07:29:05 | Toddler Training | Fun Book | 29 | 46:20:00 | |||||||
| 107 | 180379 | tanisha | Class 1/B | 2026-11-02 05:02:50 | Toddler Training | Fun Book | 19 | 59:45:00 | |||||||
| 108 | 586591 | kasi | Nursery/A | 2026-11-05 15:45:37 | Toddler Training | Fun Book | 29 | 46:20:00 | |||||||
| 109 | 869325 | Purvi Yadav | Class 2/A | 2026-11-15 13:39:23 | Toddler Training | Fun Book | 15 | 35:46:00 | |||||||
| 110 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | Toddler Training | Fun Book | 30 | 66:29:00 | |||||||
| 111 | 608561 | anisha yadav | KG 1/A | 2026-11-16 12:47:23 | Toddler Training | Fun Book | 28 | 61:48:00 | |||||||
| 112 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | Toddler Training | Fun Book | 30 | 66:29:00 | |||||||
| 113 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | Toddler Training | Fun Book | 30 | 66:29:00 | |||||||
| 114 | 661704 | kunalkarma | KG 1/A | 2026-07-21 12:42:34 | Toddler Training | Fun Book | 28 | 61:48:00 | |||||||
| 115 | 128842 | Srashti | KG 1/A | 2026-11-18 21:13:55 | Toddler Training | Fun Book | 28 | 61:48:00 | |||||||
| 116 | 945468 | Nitya | Nursery/A | 2026-09-20 20:35:05 | Toddler Training | Fun Book | 29 | 46:20:00 | |||||||
| 117 | 524774 | yadhuveer | Class 1/B | 2026-10-01 18:16:57 | Toddler Training | Fun Book | 19 | 59:45:00 | |||||||
| 118 | 178816 | sadik mansuri | Class 1/B | 2026-10-04 08:01:23 | Toddler Training | Fun Book | 19 | 59:45:00 | |||||||
| User Total | |||||||||||||||
| Class & Sec Total | |||||||||||||||
| Grand Total | |||||||||||||||