Store number of captures per earnings record

[ZavenArra]

Create new field in the earnings schema called 'captures_count' and extend the DAG to store the total number of captures consolidated into each earnings record.

[dadiorchen]

@ZavenArra how do we count the captures number for every earnings record?

[dadiorchen]

My plan on this:
Use this SQL to update all data in earings:

with counted as (
  select earnings_id, count(*) as captures_count
  from trees
  where 
    earnings_id is not null
  group by earnings_id
) 
update earnings.earnings
  set captures_count = counted.captures_count
from counted
where counted.earnings_id = earnings.earnings.id;

I can add this as a function into the lib, so we can use it in the dag, which one is a good one for this goal?
https://github.com/Greenstand/treetracker-airflow-dags/blob/main/contracts-earnings-fcc-term-3.py ?

[ZavenArra]

We don't need to update ALL the records, just the ones that we are adding. The captures count should be computed when we create an earnings record, and from there it will never change.

[dadiorchen]

Which dag is the one you used to insert the earnings record? contracts-earnings-fcc-term-3.py ? there are three similar.

[dadiorchen]

I have made the change, seems to work: