---

SELECT
    COUNT(*)
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;






SELECT
    c.scheme_id,
    ps.id AS pension_scheme_id,
    ps.index_scheme,
    ps.number,
    ps.name,
    COUNT(*) AS cnt
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
GROUP BY
    c.scheme_id,
    ps.id,
    ps.index_scheme,
    ps.number,
    ps.name
ORDER BY cnt DESC
LIMIT 50;








WITH base AS (
    SELECT
        ps.index_scheme AS schema_name,
        s.name AS strategy_name,
        t.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

    #if ("$!date_from" != "")
        AND t.period_date >= TO_DATE(:date_from, 'DD.MM.YYYY')
    #end

    #if ("$!date_to" != "")
        AND t.period_date <= TO_DATE(:date_to, 'DD.MM.YYYY')
    #end

    #if ("$!schema_index" != "")
        AND ps.id = CAST(:schema_index AS integer)
    #end

    #if ("$!strategy" != "")
        AND s.id = CAST(:strategy AS bigint)
    #end
)