Query to find Shopify apps that reached 1000 reviews with no score less than 4 stars.

1k_4_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 >= 1000
  ) 
  AND NOT EXISTS(
    SELECT 
      1 
    FROM 
      ranked_reviews 
    WHERE 
      app_id = apps.id 
      AND rank <= 1000 
      AND score < 4
  )