---

SELECT

    c.id AS contract_id,

    c.number,

    c.service_type,

    cph.pens_product_id,

    pp."name" AS product_name,

    pp.pens_scheme_accum_stage_id AS scheme_id,

    ps.brief AS scheme_number,

    ps.index_scheme

FROM ourpension.contract c

JOIN ourpension.contract_product_history cph

    ON cph.contract_id = c.id

   AND CURRENT_TIMESTAMP BETWEEN

       cph.begin_date

       AND COALESCE(cph.end_date, 'infinity')

JOIN prod_line.pens_product pp

    ON pp.id = cph.pens_product_id

JOIN prod_line.pens_scheme ps

    ON ps.id = pp.pens_scheme_accum_stage_id

WHERE c.service_type = 'NPO'

  AND pp.pens_scheme_accum_stage_id IS NOT NULL

ORDER BY c.id DESC

LIMIT 20;