Query to find Shopify apps with that reached 500 reviews without any review below 3 stars.

500_3_stars.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 
  name 
FROM 
  apps 
WHERE 
  EXISTS(
    SELECT 
      1 
    FROM 
      ranked_reviews 
    WHERE 
      app_id = apps.id 
      AND rank >= 500
  ) 
  AND NOT EXISTS(
    SELECT 
      1 
    FROM 
      ranked_reviews 
    WHERE 
      app_id = apps.id 
      AND rank <= 500 
      AND score < 3
  )