Bug: fct_daily_trip_updates_vehicle_positions_completeness overcounts trips

[edasmalchi]

Describe the bug

fct_daily_trip_updates_vehicle_positions_completeness has a scheduled_trips column, but in almost all cases it's much higher than the actual number of daily scheduled trips.

To Reproduce

use this or a similar query, note the same trip_instance_key appearing multiple times on the same date

WITH map AS (
    SELECT *
    FROM `cal-itp-data-infra.staging.int_gtfs_quality__organization_dataset_map`
    WHERE public_customer_facing_or_regional_subfeed_fixed_route
),
st as (SELECT * from `cal-itp-data-infra.mart_gtfs.fct_scheduled_trips`),
ot as (SELECT * from `cal-itp-data-infra.mart_gtfs.fct_observed_trips`)

SELECT
  organization_name,
  organization_source_record_id,
  st.trip_instance_key,
  st.trip_id,
  DATE_TRUNC(st.service_date, DAY) AS service_date

FROM  st
LEFT JOIN  ot
  ON st.trip_instance_key = ot.trip_instance_key
LEFT JOIN  map
  ON map.schedule_feed_key = st.feed_key
WHERE st.trip_instance_key = '42203914350be2884ed04b9842cdd8ef'

Expected behavior
Each trip is only counted once per day

Additional context
can likely fix by adding date to the join (since cal-itp-data-infra.staging.int_gtfs_quality__organization_dataset_map) is temporal
LEFT JOIN map ON map.schedule_feed_key = st.feed_key AND map.date = st.service_date

[edasmalchi]

https://cal-itp.slack.com/archives/C02KH3DGZL7/p1733335706586069