| Date | Time | User | Type | Name | Attribute | ||
|---|---|---|---|---|---|---|---|
| 1 | 8th April 2025 | 12:19:09 | remus | - | - | list_definition | |
|
Before
with climbers_with_dobs as (
select
c.climber_id
,cm.value::date as date_of_birth
,cm.value
from
climbers c
inner join climber_meta cm
on c.climber_id = cm.climber_id
and cm.climber_meta_key_id = 4
where
c.deleted_on is null
), ascents_with_ages as (
select
g.grade_id
,a.ascent_dt_start - c.date_of_birth as age_days
,a.ascent_id
from
ascents a
inner join climbers_with_dobs c
on a.climber_id = c.climber_id
and a.deleted_on is null
inner join climbs cl
on cl.climb_id = a.climb_id
and cl.deleted_on is null
inner join grades g
on cl.grade_id = g.grade_id
where
a.ascent_dt_start = a.ascent_dt_end
), youngest as (
select
grade_id
,min(age_days) as age_days
from
ascents_with_ages
group by
grade_id
)
select
g.grade
,'<a href="/climber/' || cl.climber_id::varchar || '/' || cl.climber_name || '">' || cl.climber_name || '</a>' as "Climber"
,'<a href="/climb/' || c.climb_id::varchar || '/' || c.climb_name || '">' || c.climb_name || '</a>' as "Climb"
,awa.age_days as "Age (days)"
,(awa.age_days / 365)::varchar || ' years ' || (awa.age_days % 365)::varchar || ' days' as "Age"
from
ascents a
inner join ascents_with_ages awa
on a.ascent_id = awa.ascent_id
inner join youngest y
on y.grade_id = awa.grade_id
and y.age_days = awa.age_days
inner join climbers cl
on a.climber_id = cl.climber_id
inner join climbs c
on a.climb_id = c.climb_id
inner join grades g
on c.grade_id = g.grade_id
where
case
when grade_system_id = 1 and order_on >= 13 then 1 -- sport
when grade_system_id = 2 and order_on >= 60 then 1 -- trad
when grade_system_id = 3 and order_on >= 37 then 1 -- boulder
else 0
end = 1
and g.climb_type_id in (1,2,3)
order by
g.grade_system_id
,g.order_on
After
with climbers_with_dobs as (
select
c.climber_id
,c.climber_name
,cm.value::date as date_of_birth
,cm.value
from
climbers c
inner join climber_meta cm
on c.climber_id = cm.climber_id
and cm.climber_meta_key_id = 4
where
c.deleted_on is null
), ascents_with_ages as (
select
row_number() over (partition by g.grade_id order by a.ascent_dt_start - c.date_of_birth asc, a.ascent_dt_start asc) as row_number,
c.climber_id,
c.climber_name,
cl.climb_id,
cl.climb_name
,g.grade_id
,g.grade_system_id
,g.climb_type_id
,g.order_on
,g.grade
,a.ascent_dt_start
,c.date_of_birth
,a.ascent_dt_start - c.date_of_birth as age_in_days_at_ascent
,EXTRACT(YEAR FROM age(a.ascent_dt_start, c.date_of_birth))::integer AS age_in_years_at_ascent
,age(a.ascent_dt_start, c.date_of_birth) as age_interval_at_ascent
,a.ascent_id
from
ascents a
inner join climbers_with_dobs c
on a.climber_id = c.climber_id
and a.deleted_on is null
inner join climbs cl
on cl.climb_id = a.climb_id
and cl.deleted_on is null
inner join grades g
on cl.grade_id = g.grade_id
where
a.ascent_dt_start = a.ascent_dt_end
), youngest as (
select *
from ascents_with_ages
where row_number = 1
)
select
y.grade
,'<a href="/climber/' || y.climber_id::varchar || '">' || y.climber_name || '</a>' as "Climber"
,'<a href="/climb/' || y.climb_id::varchar || '">' || y.climb_name || '</a>' as "Climb"
,y.age_in_days_at_ascent as "Age (Days)"
,y.age_in_years_at_ascent || ' years ' || extract(days FROM y.ascent_dt_start - (y.date_of_birth + make_interval(years => y.age_in_years_at_ascent))) || ' days' AS "Age"
from
youngest y
where
case
when grade_system_id = 1 and order_on >= 13 then 1 -- sport
when grade_system_id = 2 and order_on >= 60 then 1 -- trad
when grade_system_id = 3 and order_on >= 37 then 1 -- boulder
else 0
end = 1
and climb_type_id in (1,2,3)
order by
grade_system_id
,order_on
Diff
--- before
|
|||||||
| 2 | 8th April 2025 | 09:43:30 | remus | - | - | list_definition | |
|
Before
with climbers_with_dobs as (
select
c.climber_id
,cm.value::date as date_of_birth
,cm.value
from
climbers c
inner join climber_meta cm
on c.climber_id = cm.climber_id
and cm.climber_meta_key_id = 4
where
c.deleted_on is null
), ascents_with_ages as (
select
g.grade_id
,a.ascent_dt_start - c.date_of_birth as age_days
,a.ascent_id
from
ascents a
inner join climbers_with_dobs c
on a.climber_id = c.climber_id
and a.deleted_on is null
inner join climbs cl
on cl.climb_id = a.climb_id
and cl.deleted_on is null
inner join grades g
on cl.grade_id = g.grade_id
where
a.ascent_dt_start = a.ascent_dt_end
), youngest as (
select
grade_id
,min(age_days) as age_days
from
ascents_with_ages
group by
grade_id
)
select
g.grade
,'<a href="/climber/' || cl.climber_id::varchar || '/' || cl.climber_name || '">' || cl.climber_name || '</a>' as "Climber"
,'<a href="/climb/' || c.climb_id::varchar || '/' || c.climb_name || '">' || c.climb_name || '</a>' as "Climb"
,awa.age_days as "Age (days)"
,(awa.age_days / 365)::varchar || ' years ' || (awa.age_days % 365)::varchar || ' days' as "Age"
from
ascents a
inner join ascents_with_ages awa
on a.ascent_id = awa.ascent_id
inner join youngest y
on y.grade_id = awa.grade_id
and y.age_days = awa.age_days
inner join climbers cl
on a.climber_id = cl.climber_id
inner join climbs c
on a.climb_id = c.climb_id
inner join grades g
on c.grade_id = g.grade_id
where
case
when grade_system_id = 1 and order_on >= 13 then 1 -- sport
when grade_system_id = 2 and order_on >= 60 then 1 -- trad
when grade_system_id = 3 and order_on >= 37 then 1 -- boulder
else 0
end = 1
order by
g.grade_system_id
,g.order_on
After
with climbers_with_dobs as (
select
c.climber_id
,cm.value::date as date_of_birth
,cm.value
from
climbers c
inner join climber_meta cm
on c.climber_id = cm.climber_id
and cm.climber_meta_key_id = 4
where
c.deleted_on is null
), ascents_with_ages as (
select
g.grade_id
,a.ascent_dt_start - c.date_of_birth as age_days
,a.ascent_id
from
ascents a
inner join climbers_with_dobs c
on a.climber_id = c.climber_id
and a.deleted_on is null
inner join climbs cl
on cl.climb_id = a.climb_id
and cl.deleted_on is null
inner join grades g
on cl.grade_id = g.grade_id
where
a.ascent_dt_start = a.ascent_dt_end
), youngest as (
select
grade_id
,min(age_days) as age_days
from
ascents_with_ages
group by
grade_id
)
select
g.grade
,'<a href="/climber/' || cl.climber_id::varchar || '/' || cl.climber_name || '">' || cl.climber_name || '</a>' as "Climber"
,'<a href="/climb/' || c.climb_id::varchar || '/' || c.climb_name || '">' || c.climb_name || '</a>' as "Climb"
,awa.age_days as "Age (days)"
,(awa.age_days / 365)::varchar || ' years ' || (awa.age_days % 365)::varchar || ' days' as "Age"
from
ascents a
inner join ascents_with_ages awa
on a.ascent_id = awa.ascent_id
inner join youngest y
on y.grade_id = awa.grade_id
and y.age_days = awa.age_days
inner join climbers cl
on a.climber_id = cl.climber_id
inner join climbs c
on a.climb_id = c.climb_id
inner join grades g
on c.grade_id = g.grade_id
where
case
when grade_system_id = 1 and order_on >= 13 then 1 -- sport
when grade_system_id = 2 and order_on >= 60 then 1 -- trad
when grade_system_id = 3 and order_on >= 37 then 1 -- boulder
else 0
end = 1
and g.climb_type_id in (1,2,3)
order by
g.grade_system_id
,g.order_on
Diff
--- before
|
|||||||
| 3 | 8th April 2025 | 08:04:51 | remus | - | - | list_definition | |
|
Before
None
After
with climbers_with_dobs as (
select
c.climber_id
,cm.value::date as date_of_birth
,cm.value
from
climbers c
inner join climber_meta cm
on c.climber_id = cm.climber_id
and cm.climber_meta_key_id = 4
where
c.deleted_on is null
), ascents_with_ages as (
select
g.grade_id
,a.ascent_dt_start - c.date_of_birth as age_days
,a.ascent_id
from
ascents a
inner join climbers_with_dobs c
on a.climber_id = c.climber_id
and a.deleted_on is null
inner join climbs cl
on cl.climb_id = a.climb_id
and cl.deleted_on is null
inner join grades g
on cl.grade_id = g.grade_id
where
a.ascent_dt_start = a.ascent_dt_end
), youngest as (
select
grade_id
,min(age_days) as age_days
from
ascents_with_ages
group by
grade_id
)
select
g.grade
,'<a href="/climber/' || cl.climber_id::varchar || '/' || cl.climber_name || '">' || cl.climber_name || '</a>' as "Climber"
,'<a href="/climb/' || c.climb_id::varchar || '/' || c.climb_name || '">' || c.climb_name || '</a>' as "Climb"
,awa.age_days as "Age (days)"
,(awa.age_days / 365)::varchar || ' years ' || (awa.age_days % 365)::varchar || ' days' as "Age"
from
ascents a
inner join ascents_with_ages awa
on a.ascent_id = awa.ascent_id
inner join youngest y
on y.grade_id = awa.grade_id
and y.age_days = awa.age_days
inner join climbers cl
on a.climber_id = cl.climber_id
inner join climbs c
on a.climb_id = c.climb_id
inner join grades g
on c.grade_id = g.grade_id
where
case
when grade_system_id = 1 and order_on >= 13 then 1 -- sport
when grade_system_id = 2 and order_on >= 60 then 1 -- trad
when grade_system_id = 3 and order_on >= 37 then 1 -- boulder
else 0
end = 1
order by
g.grade_system_id
,g.order_on
Diff
--- before
|
|||||||
| 4 | 8th April 2025 | 08:04:51 | remus | - | - | list_description_pretty | |
|
Before
None
After
<p>This is a list of the youngest ascents at each grade.</p>
|
|||||||
| 5 | 8th April 2025 | 08:04:51 | remus | - | - | list_name | |
|
Before
None
After
Youngest Ascents
|
|||||||
| 6 | 8th April 2025 | 08:04:51 | remus | - | - | list_type_id | |
|
Before
None
After
5
|
|||||||
| 7 | 8th April 2025 | 08:04:51 | remus | - | - | list_type_name | |
|
Before
None
After
Misc.
|
|||||||
| 8 | 8th April 2025 | 08:04:51 | remus | - | - | hidden | |
|
Before
false
After
true
|
|||||||
| 9 | 8th April 2025 | 08:04:51 | remus | - | - | list_description | |
|
Before
None
After
This is a list of the youngest ascents at each grade.
Diff
--- before
|
|||||||