Joining profiles

Total assets by charter type for one quarter, joining the facts to the profile:

-- LEFT JOIN: profiles does not cover accounts. Keep the unmatched visible.
SELECT coalesce(p.credit_union_type_id, '(no profile row)') AS type,
       count(*) AS credit_unions,
       sum(a.account_value) AS total_assets
FROM read_parquet('https://data.quartercall.com/curated/accounts/quarter=2026Q2/data.parquet') a
LEFT JOIN read_parquet('https://data.quartercall.com/curated/profiles/quarter=2026Q2/data.parquet') p
  USING (cu_number)
WHERE a.account_code = 'A010'
GROUP BY 1
ORDER BY 3 DESC;

Three rules for any join to profiles:

• Join LEFT, on the quarter's own files. A filing with no Profile block has no profile row; an inner join drops it silently. The (no profile row) line above is that filing.
• Join on cu_number within a quarter, or on cuid across quarters. Never on profile_charter_number: it names the charter NCUA held when the filing was fetched.
• credit_union_type_id is the charter type in that quarter. It changes with a charter conversion, which is why IN ('FCU', 'FISCU') keeps the federally insured population across one.