Team activity daily

1-- core.sprout_social.team_activity_daily
2-- Messages each Sprout user sent from Sprout, per day and profile: the volume side
3-- of Sprout's team report.
4--
5-- Grain: date_day (UTC) x sent_by_user_id x customer_profile_id.
6-- Depends on: staging.sprout_social.message, .profile
7--
8-- Only messages sent from inside Sprout carry the sending user. Posts published
9-- natively on the network count in message_volume_daily but not here. Response
10-- times are not in the synced fields, so they are not computed.
11
12SELECT
13    DATE(m.created_at)                AS date_day,
14    m.sent_by_user_id,
15    ANY_VALUE(m.sent_by_user_name)    AS sent_by_user_name,
16    p.customer_id,
17    m.customer_profile_id,
18    ANY_VALUE(p.profile_name)         AS profile_name,
19    ANY_VALUE(m.network)              AS network,
20    COUNT(*)                          AS messages_sent,
21    COUNTIF(m.case_id IS NOT NULL)    AS messages_sent_in_cases,
22    COUNT(DISTINCT m.post_type)       AS post_types_used
23FROM {{staging.sprout_social.message}} m
24-- profile is one row per customer_profile_id after staging, so this cannot fan out.
25LEFT JOIN {{staging.sprout_social.profile}} p
26    ON p.customer_profile_id = m.customer_profile_id
27WHERE m.sent_by_user_id IS NOT NULL
28GROUP BY date_day, m.sent_by_user_id, p.customer_id, m.customer_profile_id