---

SELECT
    s.name AS strategy_name,
    COUNT(*) AS cnt,
    SUM(t.value) AS total_value
FROM back_office.eps_transaction et
JOIN back_office.eps_transaction_source ets
    ON ets.id = et.eps_transaction_source_id
JOIN back_office.transaction t
    ON t.operation_id = ets.operation_id
JOIN back_office.posted_register_details prd
    ON prd.operation_id = ets.operation_id
JOIN ourpension.contract c
    ON c.id = prd.contract_id
JOIN ourpension.pension_scheme ps
    ON ps.id = c.scheme_id
JOIN ourpension.invest_strategy_history ish
    ON ish.contract_id = prd.contract_id
    AND t.period_date BETWEEN ish.begin_date
        AND COALESCE(ish.end_date, CAST('infinity' AS timestamp))
JOIN prod_line.hb_invest_strategy s
    ON s.id = ish.strategy_id
WHERE 1 = 1
GROUP BY s.name
ORDER BY total_value DESC;