One credit union over time

Total assets and members for one institution, every quarter. It follows accounts.cuid, the institution, so NORTHERN's series runs across its 2026 charter conversion instead of stopping (which key):

SET VARIABLE base = 'https://data.quartercall.com/';
SET VARIABLE accounts = (
  SELECT list(getvariable('base') || q.accounts_path || 'data.parquet')
  FROM (SELECT unnest(quarters) AS q FROM read_json(getvariable('base') || 'index/manifest.json'))
  WHERE q.accounts_path IS NOT NULL);
-- The index is small: find every charter the institution has filed under, then read only those.
SET VARIABLE charters = (
  SELECT list(DISTINCT cu_number)
  FROM read_parquet(getvariable('base') || 'index/submissions.parquet')
  WHERE cuid = 5163);

SELECT quarter, cu_number,
       max(account_value) FILTER (WHERE account_code = 'A010') AS total_assets,
       max(account_value) FILTER (WHERE account_code = 'A083') AS members
FROM read_parquet(getvariable('accounts'))
WHERE cu_number IN (SELECT unnest(getvariable('charters')))
  AND account_code IN ('A010', 'A083')
GROUP BY quarter, cu_number
ORDER BY quarter;

examples/recipes/one-credit-union-over-time/assets-and-members.sql

Files are sorted by accounts.cu_number, not by cuid. So the query asks the small index for every charter the institution has filed under and filters on those, which lets DuckDB skip the rest of each file. For another institution, change 5163; find its cuid in profiles by name, or in the index by charter.

A083 is members. Add codes to the IN list and a FILTER column for each; the account codes reference names them.