Demo query results

SELECT
    i.asset_class,
    e.legal_name                      AS holding_entity,
    SUM(p.market_value_usd)           AS total_market_value_usd
FROM position p
JOIN instrument i ON p.instrument_id   = i.instrument_id
JOIN entity     e ON p.holding_entity_id = e.entity_id
WHERE p.as_of_date = '2025-12-31'
  AND p.valid_to IS NULL
GROUP BY i.asset_class, e.legal_name
ORDER BY i.asset_class, e.legal_name

SELECT
    lt.holding_entity_name,
    lt.asset_class,
    lt.instrument_subtype,
    COUNT(*)                          AS position_count,
    SUM(lt.attributed_value_usd)      AS total_attributed_value_usd
FROM vw_position_lookthrough lt
WHERE lt.holding_entity_name LIKE '%LongTail%'
GROUP BY lt.holding_entity_name, lt.asset_class, lt.instrument_subtype
ORDER BY lt.asset_class, lt.instrument_subtype

SELECT
    p.position_id,
    p.as_of_date,
    p.quantity,
    p.quantity_unit,
    p.market_value_usd,
    p.txn_from,
    p.txn_to
FROM position p
WHERE p.as_of_date = '2024-06-15'
  AND p.instrument_id = '00000000-0000-0000-0201-000000000001'
  AND p.txn_from <= TIMESTAMPTZ '2024-06-30 00:00:00+00'
  AND (p.txn_to IS NULL OR p.txn_to > TIMESTAMPTZ '2024-06-30 00:00:00+00')

SELECT
    p.position_id,
    p.as_of_date,
    p.quantity,
    p.quantity_unit,
    p.market_value_usd,
    p.txn_from,
    p.txn_to
FROM position p
WHERE p.as_of_date = '2024-06-15'
  AND p.instrument_id = '00000000-0000-0000-0201-000000000001'
  AND p.txn_to IS NULL

SELECT
    rb.break_category,
    e.legal_name                      AS holding_entity,
    COUNT(*)                          AS break_count,
    SUM(rb.break_amount_usd)          AS total_break_usd
FROM reconciliation_break rb
JOIN reconciliation_pair rp ON rb.pair_id          = rp.pair_id
JOIN reconciliation_run  rr ON rp.run_id           = rr.run_id
JOIN entity              e  ON rp.holding_entity_id = e.entity_id
WHERE rr.as_of_date >= '2025-10-01'
  AND rr.as_of_date <= '2025-12-31'
GROUP BY rb.break_category, e.legal_name
ORDER BY rb.break_category, e.legal_name

SELECT
    e.legal_name                      AS operator,
    i.asset_class,
    i.subtype,
    i.status,
    ier.effective_share_pct
FROM instrument_entity_role ier
JOIN instrument i ON ier.instrument_id = i.instrument_id
JOIN entity     e ON ier.entity_id     = e.entity_id
WHERE ier.role_type = 'OPERATOR'
  AND e.legal_name  = 'Haynesville Operating Co LLC'
ORDER BY i.asset_class, i.subtype

WITH latest_vintage AS (
    SELECT
        so.series_id,
        MAX(so.vintage)               AS max_vintage
    FROM series_obs so
    JOIN series         s  ON so.series_id    = s.series_id
    JOIN instrument_well iw ON s.instrument_id = iw.instrument_id
    WHERE iw.basin          = 'HAYNESVILLE'
      AND s.measure_code    = 'NET_REVENUE_USD_MONTHLY'
      AND so.valid_to IS NULL
    GROUP BY so.series_id
)
SELECT
    iw.api_number,
    iw.basin,
    iw.formation,
    DATE_TRUNC('month', so.observation_ts)::DATE  AS month,
    so.value                                       AS net_revenue_usd,
    lv.max_vintage                                 AS vintage
FROM latest_vintage lv
JOIN series_obs      so ON so.series_id    = lv.series_id
                       AND so.vintage      = lv.max_vintage
JOIN series          s  ON s.series_id     = so.series_id
JOIN instrument_well iw ON iw.instrument_id = s.instrument_id
ORDER BY iw.api_number, so.observation_ts