How to consecutively count everything greater than or equal to itself in SQL?

Let’s say if I have a table that contains Equipment IDs of equipments for each Equipment Type and Equipment Age, how can I do a Count Distinct of Equipment IDs that have at least that Equipment Age.

For example, let’s say this is all the data we have:

equipment_type	equipment_id	equipment_age
Screwdriver	A123	1
Screwdriver	A234	2
Screwdriver	A345	2
Screwdriver	A456	2
Screwdriver	A567	3

I would like the output to be:

equipment_type	equipment_age	count_of_equipment_at_least_this_age
Screwdriver	1	5
Screwdriver	2	4
Screwdriver	3	1

Reason is there are 5 screwdrivers that are at least 1 day old, 4 screwdrivers at least 2 days old and only 1 screwdriver at least 3 days old.

So far I was only able to do count of equipments that falls within each equipment_age (like this query shown below), but not “at least that equipment_age”.

SELECT
equipment_type,
equipment_age,
COUNT(DISTINCT equipment_id) as count_of_equipments
FROM equipment_table
GROUP BY 1, 2

Answer

Consider below join-less solution

select distinct
  equipment_type,
  equipment_age,
  count(*) over equipment_at_least_this_age as count_of_equipment_at_least_this_age
from equipment_table 
window equipment_at_least_this_age as (
  partition by equipment_type 
  order by equipment_age 
  range between current row and unbounded following
)     

if applied to sample data in your question – output is