I have 2 tables like these:
Table Info
id | created_date 1 | 2020-07-12 11:00:00 2 | 2020-08-12 10:00:00 3 | 2020-09-14 02:00:00 4 | 2020-09-16 12:00:00 5 | 2020-09-18 17:00:00 6 | 2020-09-22 13:00:00 7 | 2020-10-22 12:00:00 ...
Table A
id | IndoId | occurrency_date 1 | Info1 | 2020-07-22 11:00:00 2 | Info1 | 2021-05-12 11:00:00 3 | Info1 | 2020-08-12 12:00:00 4 | Info2 | 2021-03-12 21:00:00 5 | Info2 | 2021-02-12 01:00:00 6 | Info2 | 2020-07-12 12:00:00 7 | Info3 | 2020-10-22 17:00:00 ...
I need to take the first occurrence (occurrency_date
ordered asc) of each Info and subtract from when the Info was created (created_date
), after that take the average between them
Something like this:
AVG( (Info1 created date - Info1 first occurrence) + (Info2 created date - Info2 first occurrence) + (Info3 created date - Info3 first occurrence) + ... )
I made many attempts but none were successful, how could I get this data?
Advertisement
Answer
If I understood your problem right, you just need to get the min(occurrency_date)
of each info_id
and with subquery get their crated_date
:
WITH j (info_id,val) AS ( SELECT info_id, (SELECT created_date FROM info WHERE id = a.info_id) - min(occurrency_date) FROM a GROUP BY info_id) SELECT avg(val) FROM j;