Moves a subscription row after a given reference subscription row

move-subscription-after.sql

/* 
  Moves moving_id after reference_id by finding the next row after
  reference_id and setting moving_id.sort to be the midpoint of
  reference_id.sort and next_reference_id.sort
 */
UPDATE subscription
SET sort = (
  CASE WHEN (
    /* 
      Get the first sort value after the reference_id 
      Have to wrap in another SELECT to change an empty rowset
      to a NULL value.
     */
    SELECT ( 
      /* 
        Find the next row after reference_id
        I repeat this below. Is there a way to abstract it? 
       */
      SELECT sort
      FROM subscription
      WHERE sort > (
        /*
          I also repeat this 3 times. Is there a way to abstract it?
         */
        SELECT sort
        FROM subscription
        WHERE _id = ? /* reference_id */
      ) LIMIT 1
    ) as max_sort
  ) IS NULL THEN (
    /* 
       There is no sort value after the reference_id,
       so moving_id's sort becomes MAX(SORT) + 1
     */
    SELECT IFNULL(MAX(sort), 0) + 1
    FROM subscription
  ) ELSE (
    /*
       There is a sort value after reference_id,
       so moving_id's sort becomes:
       ( reference_id.sort + after_reference_id.sort ) / 2
     */
      (
        (
          /* This is repeated from above */
          SELECT sort
          FROM subscription
          WHERE sort > (
            /* This is repeated from above */
            SELECT sort
            FROM subscription
            WHERE _id = ? /* reference_id */
          ) LIMIT 1
        ) + (
            /* This is repeated twice from above */
            SELECT sort
            FROM subscription
            WHERE _id = ? /* reference_id */
        )
      ) / 2
  ) END 
) WHERE _id = ? /* moving_id */

subscription.sql

CREATE TABLE subscription (
  _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  sort REAL NOT NULL UNIQUE DEFAULT 0
)

I did get this working with SQLite 3.27.0

sqlite> select next_sort 
        from ( select id, 
                      lead(sort, 1, -42) 
                        over (order by sort) as next_sort 
               from subscription
        ) where id = 2;
next_sort
4.0