gistfile1.sql

        SELECT
          j1.name AS parent_node_id ,
          j2.name AS child_node_id  ,
          scenario_id               ,
          measure                   ,
          value                     ,
          revised_at                ,
          effective_at
        FROM
          edge_properties t1

        -- join nodes on parent_node_id
        LEFT JOIN
          nodes j1 ON j1.id = t1.parent_node_id

        -- join nodes on child_node_id
        LEFT JOIN
          nodes j2 ON j2.id = t1.child_node_id

        WHERE

          -- scenario and fork parents
          scenario_id IN (1956)

          -- latest revised_at <= scenario.published_at
          AND DATE(revised_at) = (
            SELECT
              DATE(MAX(revised_at))
            FROM
              edge_properties t2
            WHERE
                  t1.parent_node_id = t2.parent_node_id
              AND t1.child_node_id = t2.child_node_id
              AND t1.scenario_id = t2.scenario_id
              AND DATE(t2.revised_at) <= DATE('2014-01-01 00:00:00.000000')
          )

          -- latest scenario fork. NOTE we rely upon natural ordering of the
          -- id column to determine the latest fork; not sure if this is
          -- appropriately robust over the long term. OK for now, though.
          AND
            scenario_id = (
              SELECT
                MAX(scenario_id)
              FROM
                edge_properties t2
              WHERE
                    t1.scenario_id IN (1956)
                AND t2.scenario_id IN (1956)
                AND t1.parent_node_id = t2.parent_node_id
                AND t1.child_node_id = t2.child_node_id
                AND t1.effective_at = t2.effective_at
            )

          ORDER BY
            effective_at DESC