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='ewGWqaZ' 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='ewGWqaZ' 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 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | English | Rhyme Book | 30 | 29:28:00 | |||||||
| 2 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | English | Course Book | 69 | 411:36:00 | |||||||
| 3 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | English | Reader Book | 38 | 287:42:00 | |||||||
| 4 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | English | Course Book | 54 | 239:01:00 | |||||||
| 5 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | English | Reader Book | 26 | 88:50:00 | |||||||
| 6 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | English | Course Book | 54 | 364:10:00 | |||||||
| 7 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | English | Reader Book | 32 | 263:07:00 | |||||||
| 8 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | English | Course Book | 61 | 315:11:00 | |||||||
| 9 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | English | Course Book | 69 | 411:36:00 | |||||||
| 10 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | English | Reader Book | 27 | 145:20:00 | |||||||
| 11 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | English | Reader Book | 38 | 287:42:00 | |||||||
| 12 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | English | Course Book | 14 | 77:40:00 | |||||||
| 13 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | EVS | NA | 26 | 135:59:00 | |||||||
| 14 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | GK | NA | 19 | 110:39:00 | |||||||
| 15 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | GK | NA | 19 | 110:39:00 | |||||||
| 16 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | GK | NA | 19 | 120:17:00 | |||||||
| 17 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | GK | NA | 19 | 115:13:00 | |||||||
| 18 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | Hindi | Pathya Pustak | 8 | 63:32:00 | |||||||
| 19 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | Hindi | Shishu Geet | 38 | 34:43:00 | |||||||
| 20 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | Hindi | Pathya Pustak | 64 | 442:41:00 | |||||||
| 21 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | Hindi | Vachan Pustak | 42 | 279:27:00 | |||||||
| 22 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | Hindi | Pathya Pustak | 46 | 190:01:00 | |||||||
| 23 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | Hindi | Vachan Pustak | 28 | 132:19:00 | |||||||
| 24 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | Hindi | Pathya Pustak | 59 | 330:12:00 | |||||||
| 25 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | Hindi | Vachan Pustak | 34 | 250:00:00 | |||||||
| 26 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | Hindi | Vachan Pustak | 32 | 158:36:00 | |||||||
| 27 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | Hindi | Pathya Pustak | 64 | 442:41:00 | |||||||
| 28 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | Hindi | Vachan Pustak | 42 | 279:27:00 | |||||||
| 29 | 125341 | Navya kansari | Class 3/C | 2026-11-18 19:29:25 | Mathematics | NA | 63 | 755:09:00 | |||||||
| 30 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | Mathematics | NA | 57 | 693:52:00 | |||||||
| 31 | 112950 | Aadvik Goyal | Class 5/A | 2026-10-19 21:11:19 | Mathematics | NA | 66 | 761:55:00 | |||||||
| 32 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | Mathematics | NA | 11 | 72:50:00 | |||||||
| 33 | 445616 | Kavya Maski | Class 5/A | 2026-11-16 21:15:40 | Mathematics | NA | 66 | 761:55:00 | |||||||
| 34 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | Mathematics | NA | 45 | 354:32:00 | |||||||
| 35 | 161828 | shauryavi kunwar | Class 1/A | 2026-11-18 13:02:21 | Toddler Training | Fun Book | 21 | 86:28:00 | |||||||
| 36 | 214660 | Thanvi sahu | Class 2/A | 2026-11-21 19:00:24 | Toddler Training | Fun Book | 24 | 104:31:00 | |||||||
| 37 | 697219 | Deepali | Nursery/B | 2026-11-16 16:24:58 | Toddler Training | Fun Book | 35 | 58:26:00 | |||||||
| User Total | |||||||||||||||
| Class & Sec Total | |||||||||||||||
| Grand Total | |||||||||||||||