SQLite: Using COALESCE inside a CASE statement

I have two tables: one with a record of person with initial number, and a second one with records of changes to this number.

During a join, I do coalesce(latest_of_series, initial) to get a singular number per person. So far so good.

I also split these numbers into ranges, and order each range separately. I know I can do:

select
  coalesce(latest, initial) as final,
  case
    when coalesce(latest, inital) > 1 and coalesce(latest, inital) < 100 then 'group 1'
    -- other cases
  end as group
-- rest of the query

but that's horribly unreadable, of course.

I tried:

select
  coalesce(latest_of_series, initial_if_no_series) as value,
  case
    when value > 1  and value < 100   then 'group 1'
    -- rest of the cases
  end as group
-- rest of the query

but then the sqlite complains that there's no column "value"

Is there really no way of using previous result of coalesce as a "variable"?

That's not an SQLite limitation. That's an SQL limitation.

All the column names are decided as one. You can't define a column in line 2 of your query and then refer to it in line 3 of your query. All columns derive from the tables you select, each on their own, they can't "see" each other.

But you can use nested queries.

select
  value,
  case 
    when value >= 1    and value < 100 then 'group 1'
    when value >= 100  and value < 200 then 'group 2'
                                       else 'group 3'
  end value_group
from
  (
    select
      coalesce(latest_of_series, initial_if_no_series) as value
    from
      my_table
    group by
      user_id
   ) v

This way, the columns of the inner query can be decided as one, and the columns of the outer query can be decided as one. It might ever be faster, depending on the circumnstances.