posts(post_id, user_id, posted_at)

For every user-date on which someone posted, return that day's post count and the average daily count over that date and the two calendar days before it.

Days with no posts count as zero in the average, not as missing. So a user who posted 6 times on one day and nothing for two days before has a rolling average of 2, not 6.

Return user_id, date, posts_today and rolling_3d_avg rounded to two decimals, ordered by user then date.

A window frame over rows would average the previous two rows that exist, which is a different and wrong answer when days are missing.

Tables

posts

post_id  user_id  posted_at
-------  -------  -------------------
1        10       2026-05-01 09:00:00
2        10       2026-05-01 10:00:00
3        10       2026-05-04 09:00:00
4        10       2026-05-04 10:00:00
5        10       2026-05-04 11:00:00
6        10       2026-05-04 12:00:00
7        10       2026-05-04 13:00:00
8        10       2026-05-04 14:00:00
... 4 more row(s)

Expected result

user_id  date        posts_today  rolling_3d_avg
-------  ----------  -----------  --------------
10       2026-05-01  2            0.67
10       2026-05-04  6            2.00
10       2026-05-05  1            2.33
11       2026-05-01  1            0.33
11       2026-05-02  1            0.67
11       2026-05-03  1            1.00