How do I set up a rolling 7 day 75th percentile in SQL?

Question

I have a table that has the following columns:

• Event Date
• Location
• Employee Id
• Task Name
• Volume Per Hour

Using PostgreSQL, I need to calculate the 75th percentile of Volume Per Hour for a given location and task name across all employee ids and event dates assuming a rolling 7 day window. For example, if the event date is 11/16/2020, I would take the 75th percentile of volume per hour for all the individual dates and employee ids between 11/09/2020 and 11/16/2020. Can someone help me with this problem?

Sample Data:

Sample Output:

Answer 1

You should be able to achieve this by using generate_series and percentile_disc

with data_example as
(
 SELECT * FROM (VALUES 
 (date '2020-11-16','ABC',1,'Inbound',10),
 (date '2020-11-16','ABC',2,'Inbound',20),
 (date '2020-11-15','ABC',1,'Inbound',30),
 (date '2020-11-17','ABC',1,'Inbound',10)
 ) AS t (event_date,location,emp_id,task_name,volume)
)
,dates as
(
select generate_series(
           (date '2020-11-10')::timestamp,
           (date '2020-11-25')::timestamp,
           interval '1 day'
         ) as event_date
)
select d.event_date
, d.event_date - INTERVAL '7 day' AS window_start
,location
,task_name
,percentile_disc(0.75) within group (order by de.volume) perc_volume
,count(1) cnt
from dates d
join data_example de
    on de.event_date between d.event_date- INTERVAL '7 day' and d.event_date
group by 1,2,3,4
order by 1,2,3,4;