{{
    config(
        materialized='incremental'
        , incremental_strategy='merge'
        , unique_key=['sender_user_id', 'p_date', 'notification_category']
    )
}}

WITH sender_notifications AS (
    SELECT * FROM {{ ref('int_grouped_notifications_individualized') }}
)

SELECT
    sender_user_id
    , p_date
    , CASE
        WHEN type = 'clip_like' THEN 'clip_like'
        WHEN type = 'follow' THEN 'follow'
        WHEN type IN ('clip_comment', 'comment_like', 'comment_reply', 'comment_mention') THEN 'comment_related'
        ELSE 'other'
    END AS notification_category
    , COUNT(*) AS n_notifications_sent_in_category
    , SUM(COUNT(*)) OVER (PARTITION BY sender_user_id, p_date) AS n_notifications_sent_on_day
FROM sender_notifications
WHERE 1=1
GROUP BY 1, 2, 3
