Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (

id VARCHAR, rating float,	aquisition_date DATE);

INSERT INTO table1 VALUES

(01	,10	,'2022-04-18 07:00:00.000'),
(01	,8.2	,'2022-04-25 06:00:00.000');

CREATE TABLE



INSERT 0 2


CREATE TABLE table2 (

id VARCHAR, value INT,	aquisition_date DATE);

INSERT INTO table2 VALUES
(01	,02	,'2022-04-12 01:00:00.000'),
(01	,04	,'2022-04-13 02:00:00.000'),
(01	,02	,'2022-04-14 03:00:00.000'),
(01	,05	,'2022-04-15 04:00:00.000'),
(01	,06	,'2022-04-16 05:00:00.000'),
(01	,06	,'2022-04-17 06:00:00.000'),
(01	,10	,'2022-04-18 07:00:00.000'),
(01	,12	,'2022-04-19 08:00:00.000'),
(01	,19	,'2022-04-20 09:00:00.000'),
(01	,11	,'2022-04-21 08:00:00.000'),
(01	,10	,'2022-04-22 07:00:00.000'),
(01	,10	,'2022-04-23 06:00:00.000'),
(01	,20	,'2022-04-24 07:00:00.000'),
(01	,20	,'2022-04-25 07:00:00.000');

CREATE TABLE



INSERT 0 14


SELECT 
    table1.id
    ,CAST(table1.aquisition_date as date)
    ,table1.rating
    ,AVG(table2.value) as ranking_avg
FROM 
    table1 table1
INNER JOIN 
    table2 table2
ON
    table2.id =   table1.id
GROUP BY
    table1.id
   ,table1.rating 
   ,CAST(table1.aquisition_date as date)


id
aquisition_date
rating
ranking_avg


1
2022-04-25
8.2
9.7857142857142857

1
2022-04-18
10
9.7857142857142857



SELECT 2

id	aquisition_date	rating	ranking_avg
1	2022-04-25	8.2	9.7857142857142857
1	2022-04-18	10	9.7857142857142857