Query to find the average number of reviews before a negative review.

average_to_negative.sql

WITH ranked_reviews AS (
  SELECT 
    a.name AS app_name, 
    a.id AS app_id, 
    RANK() OVER (
      PARTITION BY r.app_id 
      ORDER BY 
        r.posted_at ASC
    ) AS rank, 
    r.score, 
    r.posted_at 
  FROM 
    apps a 
    INNER JOIN reviews r ON r.app_id = a.id
) 
SELECT 
  AVG(min_bad_review_rank) 
FROM 
  (
    SELECT 
      MIN(rank) AS min_bad_review_rank, 
      rr.app_id 
    FROM 
      ranked_reviews rr 
    WHERE 
      EXISTS(
        SELECT 
          1 
        FROM 
          ranked_reviews rr2 
        WHERE 
          rr2.app_id = rr.app_id 
          AND rr2.rank >= 100
      ) 
      AND score < 3 
    GROUP BY 
      rr.app_id
  ) AS q1