---

SELECT
    s.name AS strategy_name,
    COUNT(*) AS cnt,
    SUM(b.total_credit - b.total_debit) AS total_balance
FROM back_office.account a
JOIN back_office.balance b
    ON b.account_id = a.id
    AND TO_DATE(:date_to, 'DD.MM.YYYY') BETWEEN b.start_date AND b.end_date
JOIN ourpension.sharer sh
    ON sh.id = a.owner_sharer_id
JOIN ourpension.contract c
    ON c.id = sh.contract_id
JOIN ourpension.invest_strategy_history ish
    ON ish.contract_id = c.id
    AND TO_DATE(:date_to, 'DD.MM.YYYY') 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
GROUP BY s.name
ORDER BY total_balance DESC;