updated version of virality as a factor of NPS score

virality_nps

WITH viralit AS (
    Select host_id,
           count(distinct IFF(DATE_SIGNED_UP is not null, GUEST_ID, NULL))   count_signups,
           count(distinct IFF(DATE_TRANSFERRED is not null, GUEST_ID, NULL)) count_mnus
    from virality.referral
    GROUP BY 1),
     nps AS (SELECT bnps.user_id,
                    bnps.nps_score AS                                                       nps_score,
                    bnps.respond_date,
                    rank() over (partition by bnps.USER_ID order by bnps.RESPOND_DATE desc) rank
             FROM REPORTS.REPORT_NPS bnps
             WHERE 1 = 1
               AND bnps.NPS_SCORE is not null
             GROUP BY 1, 2, 3)
SELECT nps.nps_score                                                      AS nps,
       count(distinct nps.user_id)                                        AS nps_respondents,
       count(distinct viralit.host_id)                                    AS people_who_invited,
       count(distinct IFF(viralit.count_mnus > 0, viralit.host_id, NULL)) AS count_host_mnus,
       round(sum(viralit.count_mnus) / count(distinct host_id), 2)        AS mnus_per_host,
       round(sum(viralit.count_signups) / count(distinct host_id), 2)     AS signups_per_host
FROM nps
         LEFT JOIN viralit ON nps.user_id = viralit.host_id
         LEFT JOIN VIRALITY.HOST h ON h.USER_ID = viralit.HOST_ID AND IS_SUSPICIOUS = false AND HAS_TRANSFERRED = true
WHERE 1 = 1
  AND rank = 1                   -- only latest NPS
  AND viralit.count_signups < 50 -- to remove  "Super inviters"
GROUP BY 1
order by nps;