clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335706 fiddles created (27459 in the last week).

CREATE TABLE reviews ( id int , review_date date , app_id int -- REFERENCES apps.id ); INSERT INTO reviews VALUES ( 1, '2020-08-01', 1) , ( 2, '2020-08-02', 1) , ( 3, '2020-08-03', 1) , ( 4, '2020-08-04', 1) , ( 5, '2020-08-05', 1) , ( 6, '2020-08-06', 1) , ( 7, '2020-08-07', 1) , ( 8, '2020-08-07', 1) , ( 9, '2020-08-07', 1) , (10, '2020-08-07', 1) , (21, '2020-08-03', 2) , (22, '2020-08-03', 2) , (23, '2020-08-03', 2) , (24, '2020-08-03', 2) , (25, '2020-08-03', 2) , (26, '2020-08-05', 2) , (31, '2020-08-02', 3) , (32, '2020-08-02', 3) , (33, '2020-08-02', 3) , (34, '2020-08-02', 3) , (35, '2020-08-02', 3) , (36, '2020-08-03', 3) ;
22 rows affected
 hidden batch(es)


-- earliest review & current total count per app SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS total_ct FROM reviews GROUP BY 1;
app_id earliest_review total_ct
3 2020-08-02 6
2 2020-08-03 6
1 2020-08-01 10
 hidden batch(es)


-- Series of days from earliest to latest review SELECT generate_series(min(review_date) , max(review_date) , '1 day')::date FROM reviews;
generate_series
2020-08-01
2020-08-02
2020-08-03
2020-08-04
2020-08-05
2020-08-06
2020-08-07
 hidden batch(es)


-- complete solution -- WITH TIES requires Postgres 13 or later WITH cte AS ( SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS total_ct FROM reviews GROUP BY 1 ) SELECT * FROM ( SELECT generate_series(min(review_date) , max(review_date) , '1 day')::date FROM reviews ) d(review_window_start) LEFT JOIN LATERAL ( SELECT total_ct, array_agg(app_id) AS apps FROM ( SELECT app_id, total_ct FROM cte c WHERE c.earliest_review >= d.review_window_start ORDER BY total_ct DESC FETCH FIRST 1 ROWS WITH TIES ) sub GROUP BY 1 ) a ON true;
review_window_start total_ct apps
2020-08-01 10 {1}
2020-08-02 6 {3,2}
2020-08-03 6 {2}
2020-08-04
2020-08-05
2020-08-06
2020-08-07
 hidden batch(es)


-- the same for Postgres 12 or older WITH cte AS ( SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS total_ct FROM reviews GROUP BY 1 ) SELECT * FROM ( SELECT generate_series(min(review_date) , max(review_date) , '1 day')::date FROM reviews ) d(review_window_start) LEFT JOIN LATERAL ( SELECT total_ct, array_agg(app_id) AS apps FROM ( SELECT total_ct, app_id , rank() OVER (ORDER BY total_ct DESC) AS rnk FROM cte c WHERE c.earliest_review >= d.review_window_start ) sub WHERE rnk = 1 GROUP BY 1 ) a ON true;
review_window_start total_ct apps
2020-08-01 10 {1}
2020-08-02 6 {3,2}
2020-08-03 6 {2}
2020-08-04
2020-08-05
2020-08-06
2020-08-07
 hidden batch(es)