PostgreSQL - Get value from from the same table

Question

Get the average traffic from last week data per WEEK number and get the traffic data for last week Traffic(D-7)

For example if date = 5/13/2023, need to output traffic data (Traffic(D-7)) for date = 5/6/2023

I manage to get the Average but no idea how to retrieve the date-7 data and output it altogether

create table a
(
date  varchar(50),
Tname varchar(50),
Week varchar(5),
Traffic float
)

insert into  a values ('5/1/2023', 'ID1', '18', 7.98)
insert into  a values ('5/2/2023', 'ID1', '18', 4.44)
insert into  a values ('5/3/2023', 'ID1', '18', 5.66)
insert into  a values ('5/4/2023', 'ID1', '18', 10.01)
insert into  a values ('5/5/2023', 'ID1', '18', 9.41)
insert into  a values ('5/6/2023', 'ID1', '18', 6.71)
insert into  a values ('5/7/2023', 'ID1', '18', 8.24)
insert into  a values ('5/8/2023', 'ID1', '19', 8.97)
insert into  a values ('5/9/2023', 'ID1', '19', 6.74)
insert into  a values ('5/10/2023', 'ID1', '19', 6.45)
insert into  a values ('5/11/2023', 'ID1', '19', 9.33)
insert into  a values ('5/12/2023', 'ID1', '19', 8.08)
insert into  a values ('5/13/2023', 'ID1', '19', 8.36)


SELECT date, Tname, Week,
AVG(Traffic) OVER(PARTITION BY Week) AS AVTraffic
FROM a
ORDER BY week

http://sqlfiddle.com/#!18/538b7/3

Answer 1

Use the window function LAG with a 7 PRECEDING frame to catch the values from 7 days ago, so that you can avoid using unnecessary subqueries or even self joins, e.g.

SELECT 
  date, Tname, Week,
  LAG(traffic,7) OVER (PARTITION BY tname ORDER BY tname,to_date(date,'mm/dd/yyyy')),
  AVG(traffic)   OVER (PARTITION BY week,tname)
FROM a
ORDER BY tname,week;

   date    | tname | week |  lag  |        avg        
-----------+-------+------+-------+-------------------
 5/1/2023  | ID1   | 18   |       | 7.492857142857143
 5/2/2023  | ID1   | 18   |       | 7.492857142857143
 5/3/2023  | ID1   | 18   |       | 7.492857142857143
 5/4/2023  | ID1   | 18   |       | 7.492857142857143
 5/5/2023  | ID1   | 18   |       | 7.492857142857143
 5/6/2023  | ID1   | 18   |       | 7.492857142857143
 5/7/2023  | ID1   | 18   |       | 7.492857142857143
 5/8/2023  | ID1   | 19   |  7.98 | 7.988333333333333
 5/9/2023  | ID1   | 19   |  4.44 | 7.988333333333333
 5/10/2023 | ID1   | 19   |  5.66 | 7.988333333333333
 5/11/2023 | ID1   | 19   | 10.01 | 7.988333333333333
 5/12/2023 | ID1   | 19   |  9.41 | 7.988333333333333
 5/13/2023 | ID1   | 19   |  6.71 | 7.988333333333333
(13 rows)

Note:

• this query assumes there is only one measurement per date/tname
• Consider changing the data type of "date" to date. I had to cast it to make the query work properly.

Demo: db<>fiddle