Calculate fields in a view

I have the following table "numbers" in my MariaDB database:

Now I would like to create a view that lets me see:

• column 1: All possible numbers (distinct)
• column 2: the amount of occurrences of that number
• column 3: the amount of IDs between the last ID and the ID from that number.

That would result in this view:

So I started with:
"SELECT DISTINCT number FROM numbers" and then a left join on the same table to count , but that makes the query verrrry slow (and it didn’t work the way I wanted). Do you have any idea how to resolve this? Thanks a lot in advance!

>Solution :

Yo can group by number and use COUNT() aggregate function to get the column occurrences and MAX() window function to get the column IDdifferences:

SELECT number,
       COUNT(*) occurrences,
       MAX(MAX(id)) OVER () - MAX(id) IDdifferences
FROM numbers
GROUP BY number
ORDER BY number;

See the demo.