TIL: Reusing an alias in GROUP BY

I recently learned that you can simply reference an alias defined in the SELECT clause further down in the GROUP BY clause if you're using a column alias.

Consider this example table in SQLite:

create table users
(
    id    integer primary key,
    name  text
);

Instead of writing:

select
    lower(name) as name_lower,
    count(*)    as count
from
    users
group by
    lower(name);

You can do this:

select
    lower(name) as name_lower,
    count(*)    as count
from
    users
group by
    name_lower;

This is cleaner and also easier to refactor.