Sample Queries

1. How to query supporter MRR for suggestions

   WITH suggestion_account AS (
       SELECT DISTINCT s.suggestion_id, ea.id AS external_account_id
       FROM supporter s
           INNER JOIN user u ON u.id = s.user_id
           INNER JOIN external_user eu ON eu.user_id = u.id 
           INNER JOIN external_account ea ON ea.id = eu.external_account_id
       WHERE ea.mrr_cents IS NOT NULL
   ),
   suggestion AS (
       SELECT DISTINCT id FROM suggestion_history
   )
   SELECT s.id, SUM(IFNULL(ea.mrr_cents, 0)) AS mrr_cents
   FROM suggestion s
       LEFT JOIN suggestion_account sa ON s.id = sa.suggestion_id
       LEFT JOIN external_account ea ON sa.external_account_id = ea.id
   GROUP BY 1
   

2. How to query supporter MRR for features

   WITH feature_account AS (
       SELECT DISTINCT fs.feature_id, ea.id AS external_account_id
       FROM feature_suggestion fs 
           INNER JOIN supporter s ON fs.suggestion_id = s.suggestion_id 
           INNER JOIN user u ON u.id = s.user_id
           INNER JOIN external_user eu ON eu.user_id = u.id 
           INNER JOIN external_account ea ON ea.id = eu.external_account_id
       WHERE ea.mrr_cents IS NOT NULL
   ),
   feature AS (
       SELECT DISTINCT id FROM feature_history 
   )
   SELECT f.id, SUM (IFNULL(ea.mrr_cents, 0)) AS mrr_cents
   FROM feature f
       LEFT JOIN feature_account fa ON f.id = fa.feature_id
       LEFT JOIN external_account ea ON ea.id = fa.external_account_id
   GROUP BY 1
   

3. How to query promoter, neutral and detractor supporters for suggestions

   WITH last_nps_rating AS (
       SELECT r.user_id, r.rating
       FROM nps_rating r
           INNER JOIN (
               SELECT user_id, MAX(created_at) AS created_at
               FROM nps_rating 
               GROUP BY 1
           ) last_rating_time ON r.user_id = last_rating_time.user_id
               AND r.created_at = last_rating_time.created_at
   ),
   suggestion AS (
       SELECT DISTINCT id FROM suggestion_history
   )
   SELECT s.id,
       SUM(CASE WHEN r.rating <= 6 THEN 1 ELSE 0 END) AS detractors,
       SUM(CASE WHEN r.rating BETWEEN 7 AND 8 THEN 1 ELSE 0 END) AS neutral,
       SUM(CASE WHEN r.rating >= 9 THEN 1 ELSE 0 END) AS promoters
   FROM suggestion s
       LEFT JOIN supporter supp ON supp.suggestion_id = s.id
       LEFT JOIN user u ON u.id = supp.user_id
       LEFT JOIN last_nps_rating r ON u.id = r.user_id
   GROUP BY 1