---

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.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
    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.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
    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 prod_line.pens_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
    prd.contract_id,
    c.id AS contract_id_found,
    c.scheme_id,
    c.pension_scheme_id_1c,
    ps.id AS ps_id,
    ps.brief AS ps_brief,
    ps.name AS ps_name,
    s.id AS strategy_id,
    s.name AS strategy_name,
    t.value,
    t.period_date
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

LEFT JOIN ourpension.contract c
    ON c.id = prd.contract_id

LEFT JOIN prod_line.pens_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

LIMIT 100;










SELECT
    c.scheme_id,
    COUNT(*) AS cnt,
    ps.id AS ps_id,
    ps.brief,
    ps.name
FROM ourpension.contract c
LEFT JOIN prod_line.pens_scheme ps
    ON ps.id = c.scheme_id
GROUP BY
    c.scheme_id,
    ps.id,
    ps.brief,
    ps.name
ORDER BY cnt DESC
LIMIT 50;