data-infra
data-infra copied to clipboard
Clarify meaning of the `pending_charge_fare` `charge_type` value
In several of our agencies' Littlepay data, either all or ~half of micropayments have a charge_type
value of pending_charge_fare
which is not documented in LP's data documentation. As a data user, I want to clarify the interpretation of this status so that I can correctly categorize these transactions.
Acceptance criteria:
- [ ] Contact Littlepay to request clarification of this status
- [ ] Write a follow-up ticket for next steps once clarification received (specifically: should this status be cast or replaced with something else?)
Query to identify affected agencies by year:
SELECT
EXTRACT(YEAR FROM transaction_time),
participant_id,
COUNTIF(charge_type = 'pending_charge_fare') AS pending_ct,
COUNT(*) AS total_ct,
ROUND(100*COUNTIF(charge_type = 'pending_charge_fare') / COUNT(*),2) AS pending_pct
FROM `cal-itp-data-infra.staging.stg_littlepay__micropayments`
GROUP BY 1,2
HAVING pending_ct > 0
ORDER BY 1 DESC, 2