Return to Answer

WITH cte AS MATERIALIZED (
   SELECT a.id, a.slug, r.earliest_review, r.review_total
   FROM   apps a
   JOIN    (
      SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS review_total
      FROM   reviews
      GROUP  BY 1
      ) r ON r.app_id = a.id
SELECT *
FROM  (
   SELECT generate_series(min(review_date)
                        , max(review_date)
                        , '1 day')::date
   FROM   reviews
   ) d(review_window_start)
LEFT  JOIN LATERAL (
   SELECT app_id, slug, review_total, earliest_review
   FROM  (
      SELECT app_id, slug, review_total, earliest_review
            ,  rank() OVER (ORDER BY review_total DESC, earliest_review DESC) AS rnk
      FROM   cte c
      WHERE  c.earliest_review >= d.review_window_start
      ) sub
   WHERE  rnk = 1
   ) a ON true;

WITH cte AS MATERIALIZED (
   SELECT a.id, a.slug, r.earliest_review, r.review_total
   FROM   apps a
   JOIN    (
      SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS review_total
      FROM   reviews
      GROUP  BY 1
      ) r ON r.app_id = a.id
   )
SELECT *
FROM  (
   SELECT generate_series(min(review_date)
                        , max(review_date)
                        , '1 day')::date
   FROM   reviews
   ) d(review_window_start)
LEFT  JOIN LATERAL (
   SELECT app_id, slug, review_total, earliest_review
   FROM  (
      SELECT app_id, slug, review_total, earliest_review
            ,  rank() OVER (ORDER BY review_total DESC, earliest_review DESC) AS rnk
      FROM   cte c
      WHERE  c.earliest_review >= d.review_window_start
      ) sub
   WHERE  rnk = 1
   ) a ON true;

Another difference: earliest_review as additioal tiebreaker. That's just a gradual improvement, though, as there can stiill be 0-n winners per review_window_start. Instead of picking the (arbitrary) top two ( limit 2), select the top n with ties. (Wording already hints at the new technique in Postgres 13; see below.)

Another difference: earliest_review as additioal tiebreaker. That's just a gradual improvement, though, as there can stiill be 0-n winners per review_window_start. Instead of picking the (arbitrary) top two ( limit 2), select the first one, with ties. (Wording already hints at the new technique in Postgres 13; see below.)

Like in the related answer on SO, it will be cheaper yet in Postgres 13 using WITH TIES. See:

• https://stackoverflow.com/questions/63178114/greater-than-or-equal-to-all-and-equal-to-max-speed/63184287#63184287

...
LEFT  JOIN LATERAL (
   SELECT app_id, slug, review_total, earliest_review
   FROM   cte c
   WHERE  earliest_review >= d.review_window_start
   ORDER  BY review_total DESC, earliest_review DESC
   FETCH  FIRST 1 ROWS WITH TIES  -- new!
      ) sub

Like in the related answer on SO, it will be cheaper yet in Postgres 13 using WITH TIES. See:

• Greater than or equal to ALL() and equal to MAX() speed

...
LEFT  JOIN LATERAL (
   SELECT app_id, slug, review_total, earliest_review
   FROM   cte c
   WHERE  earliest_review >= d.review_window_start
   ORDER  BY review_total DESC, earliest_review DESC
   FETCH  FIRST 1 ROWS WITH TIES  -- new!
   ) a ON true;