Realtime ER Visits

You are a software engineer working in logistics for a hypothetical hospital in Iceland. You are responsible for software that determines optimal staffing. Your task is to help ensure that the emergency room (ER) remains adequately staffed as patient load fluctuates throughout the day. To do this, you need to look at patient volume throughout the day in realtime. Realtime data for number of visits is in the realtime_er_visits table.

The datetime_t column represents a 15-minute interval beginning at that time. The visit_count is the number of people that came into the ER during that interval.

To help the logistics team, your software needs to display a moving average, a.k.a. "rolling mean", of the number of people that visit the ER for each time interval. In our case, the moving average should be for 2 hours. So, the moving average for the 11:30:00 - 11:44:59 interval should go back to 9:45:00.

For each time interval in the realtime_er_visits table, select the datetime, the number of new visitors that interval, and the moving average for the past 2 hours (name this column avg_visit_count).

This hospital has some 15-minute intervals where nobody comes into the ER. Interestingly, when these happen, there is no row for that time interval in the table instead of a 0. Your query will need to account for this, as the moving average for that time will probably not be 0. For a hint, notice that there is a Postgres function generate_series which works for TIMESTAMPTZs.