student_comments

/* Pulls student comments and counts comments per student by month and week

Examples of common uses: Pull meal comments for Everest tracking purposes, pull behavior comments that track parent communication

 Instructions:
    Line 32: Update start of school year date to count "Week_Number" properly
    Filters:
    -   Line 58: Update academic year for current site
    -   Line 59: Update site
    -   Line 60: Update comment category
    -   Line 61: Update comment's created_date */

WITH stu_comments AS (
    SELECT
      com.student_id
      , com.comment
      , com.created_date :: DATE
      , com.modify_date :: DATE
      , comcat.code_translation
      , extract(month from com.created_date) AS month
      , com.sc_id
      , com.comment_type_id
      , trunc(date_part('day',com.created_date::timestamp - '2017-08-15'::timestamp) / 7) + 1 as Week_Number
      , date_trunc('week', com.created_date::timestamp)::date as Start_of_Week

    FROM public.student_comments com
      LEFT JOIN public.students stu
        ON com.student_id = stu.student_id
      LEFT JOIN codes.student_comment_categories comcat
        ON comcat.code_id = com.comment_type_id
)

SELECT
  sites.site_name
  , stu.local_student_id
  , stu.first_name
  , stu.last_name
  , sc.comment
  , sc.created_date :: DATE
  , sc.modify_date :: DATE
  , comcat.code_translation
  , COUNT(sc.created_date) OVER (PARTITION BY sc.month, local_student_id ORDER BY local_student_id) AS comments_per_month
  , sc.Start_of_Week
  , COUNT(sc.created_date) OVER (PARTITION BY sc.Start_of_Week, local_student_id ORDER BY local_student_id) AS commments_per_week

FROM stu_comments sc
  LEFT JOIN public.students stu
    ON sc.student_id = stu.student_id
  LEFT JOIN codes.student_comment_categories comcat
    ON comcat.code_id = sc.comment_type_id
  LEFT JOIN student_session_aff enrollments
    ON stu.student_id = enrollments.student_id
  LEFT JOIN public.sessions sess
    ON sess.session_id = enrollments.session_id
  LEFT JOIN public.sites
    ON sites.site_id = sess.site_id

WHERE sess.academic_year = 2018
 -- AND site_name LIKE '%Everest%'
 -- AND sc.code_translation = 'Meals'
 -- AND sc.created_date > '2017-08-01'