Skip to content
Advertisement

Query to get the Average of ONLY the first occurrences

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;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement