cal-itp/data-infra

Clarify meaning of the `pending_charge_fare` `charge_type` value

Opened this issue · 0 comments

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