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
)

How about this:

update subscription
    set sort =
        (select new_sort
         from (select id, cast (ifnull ((sort + lead (sort) over (partition by null order by sort)) / 2, max(sort) over (partition by null) + 1) as real) new_sort
               from subscription)
         where id = ?) /* reference_id */
    where id = ?  /* moving_id */

Sadly, SQLite only got window functions in 3.25.0, but even the latest Android (API 28) is only on 3.22.0:

$ adb -e shell sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt2

And obviously, older Android versions have older SQLites:

Android API	SQLite Version
API 27	3.19
API 26	3.18
API 24	3.9
API 21	3.8
API 11	3.7
API 8	3.6
API 3	3.5
API 1	3.4

I'd still like to know if there's a way to abstract the SELECTs just so the query is simpler.

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