| Date | Time | User | Type | Name | Attribute | ||
|---|---|---|---|---|---|---|---|
| 1 | 31st December 2024 | 07:59:20 | remus | - | - | hidden | |
|
Before
false
After
true
|
|||||||
| 2 | 31st December 2024 | 07:59:20 | remus | - | - | public | |
|
Before
false
After
true
|
|||||||
| 3 | 31st December 2024 | 07:57:43 | remus | - | - | list_definition | |
|
Before
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Boulder problem'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
After
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
,count(*)
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
and cl.country_id = 1
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Boulder problem'
and ast.ascent_successful = true
and a.ascent_type_id = 2
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
and cl.country_id = 1
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 2
and a.ascent_style_id = 1
), dts as (
select
1948 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
Diff
--- before
+++ after
@@ -3,6 +3,7 @@
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
+ ,count(*)
from
ascents a
@@ -16,6 +17,7 @@
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
+ and cl.country_id = 1
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
@@ -26,7 +28,7 @@
where
ct.climb_type = 'Boulder problem'
and ast.ascent_successful = true
- and a.ascent_type_id = 1
+ and a.ascent_type_id = 2
group by
date_part('year', a.ascent_dt_start)
@@ -61,6 +63,7 @@
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
+ and cl.country_id = 1
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
@@ -69,11 +72,11 @@
and g.order_on = but.best
where
- a.ascent_type_id = 1
+ a.ascent_type_id = 2
and a.ascent_style_id = 1
), dts as (
select
- 1970 as yr
+ 1948 as yr
union all
|
|||||||
| 4 | 30th December 2024 | 18:50:30 | Mattsparksy | - | - | list_definition | |
|
Before
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Boulder'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
After
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Boulder problem'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
Diff
--- before
+++ after
@@ -24,7 +24,7 @@
on a.ascent_style_id = ast.ascent_style_id
where
- ct.climb_type = 'Boulder'
+ ct.climb_type = 'Boulder problem'
and ast.ascent_successful = true
and a.ascent_type_id = 1
|
|||||||
| 5 | 30th December 2024 | 18:46:33 | Mattsparksy | - | - | list_definition | |
|
Before
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Sport route'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
After
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Boulder'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
Diff
--- before
+++ after
@@ -24,7 +24,7 @@
on a.ascent_style_id = ast.ascent_style_id
where
- ct.climb_type = 'Sport route'
+ ct.climb_type = 'Boulder'
and ast.ascent_successful = true
and a.ascent_type_id = 1
|
|||||||
| 6 | 30th December 2024 | 18:45:57 | Mattsparksy | - | - | hidden | |
|
Before
true
After
false
|
|||||||
| 7 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_type_id | |
|
Before
None
After
1
|
|||||||
| 8 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | hidden | |
|
Before
false
After
true
|
|||||||
| 9 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | public | |
|
Before
true
After
false
|
|||||||
| 10 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_description_pretty | |
|
Before
None
After
<p>Britains hardest boulders through time.</p>
|
|||||||
| 11 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_definition | |
|
Before
None
After
with recursive per_year as (
select
date_part('year', a.ascent_dt_start) as yr
,ast.ascent_style_id
,max(g.order_on) as max_grade_order_on
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
and c.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join climb_types ct
on c.climb_type = ct.climb_type_id
inner join ascent_styles ast
on a.ascent_style_id = ast.ascent_style_id
where
ct.climb_type = 'Sport route'
and ast.ascent_successful = true
and a.ascent_type_id = 1
group by
date_part('year', a.ascent_dt_start)
,ast.ascent_style_id
,ast.ascent_style
), best_up_to as (
select
yr
,ascent_style_id
,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
from
per_year
), candidates as (
select
c.climb_name
,c.climb_id
,g.grade
,cl.climber_name
,cl.climber_id
,a.ascent_dt_start
,a.ascent_dt_end
from
ascents a
inner join climbs c
on a.climb_id = c.climb_id
and a.deleted_on is null
and c.deleted_on is null
and a.exclude_reason is null
inner join climbers cl
on a.climber_id = cl.climber_id
and cl.deleted_on is null
and cl.exclude_reason is null
inner join grades g
on c.grade_id = g.grade_id
inner join best_up_to but
on a.ascent_style_id = but.ascent_style_id
and date_part('year', a.ascent_dt_start) = but.yr
and g.order_on = but.best
where
a.ascent_type_id = 1
and a.ascent_style_id = 1
), dts as (
select
1970 as yr
union all
select
yr + 1
from
dts
where
yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
)
select
dts.yr as Year
,c.grade as Grade
,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
from
dts
left join candidates c
on dts.yr = date_part('year', c.ascent_dt_start)
group by
dts.yr
,c.grade
order by
dts.yr
Diff
--- before
+++ after
@@ -1 +1,107 @@
-
+with recursive per_year as (
+ select
+ date_part('year', a.ascent_dt_start) as yr
+ ,ast.ascent_style_id
+ ,max(g.order_on) as max_grade_order_on
+
+ from
+ ascents a
+ inner join climbs c
+ on a.climb_id = c.climb_id
+ and a.deleted_on is null
+ and c.deleted_on is null
+ and a.exclude_reason is null
+ and c.exclude_reason is null
+ inner join climbers cl
+ on a.climber_id = cl.climber_id
+ and cl.deleted_on is null
+ and cl.exclude_reason is null
+ inner join grades g
+ on c.grade_id = g.grade_id
+ inner join climb_types ct
+ on c.climb_type = ct.climb_type_id
+ inner join ascent_styles ast
+ on a.ascent_style_id = ast.ascent_style_id
+
+ where
+ ct.climb_type = 'Sport route'
+ and ast.ascent_successful = true
+ and a.ascent_type_id = 1
+
+ group by
+ date_part('year', a.ascent_dt_start)
+ ,ast.ascent_style_id
+ ,ast.ascent_style
+), best_up_to as (
+ select
+ yr
+ ,ascent_style_id
+ ,max(max_grade_order_on) over (partition by ascent_style_id order by yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as best
+
+ from
+ per_year
+), candidates as (
+ select
+ c.climb_name
+ ,c.climb_id
+ ,g.grade
+ ,cl.climber_name
+ ,cl.climber_id
+ ,a.ascent_dt_start
+ ,a.ascent_dt_end
+
+ from
+ ascents a
+ inner join climbs c
+ on a.climb_id = c.climb_id
+ and a.deleted_on is null
+ and c.deleted_on is null
+ and a.exclude_reason is null
+ inner join climbers cl
+ on a.climber_id = cl.climber_id
+ and cl.deleted_on is null
+ and cl.exclude_reason is null
+ inner join grades g
+ on c.grade_id = g.grade_id
+ inner join best_up_to but
+ on a.ascent_style_id = but.ascent_style_id
+ and date_part('year', a.ascent_dt_start) = but.yr
+ and g.order_on = but.best
+
+ where
+ a.ascent_type_id = 1
+ and a.ascent_style_id = 1
+), dts as (
+ select
+ 1970 as yr
+
+ union all
+
+ select
+ yr + 1
+
+ from
+ dts
+
+ where
+ yr <= (select max(date_part('year', ascent_dt_start)) from candidates)
+)
+
+select
+ dts.yr as Year
+ ,c.grade as Grade
+ ,string_agg('<a href="/climber/' || climber_id::varchar || '">' || climber_name || '</a> with <a href="/climb/' || climb_id::varchar || '">' || climb_name || '</a>', '<br>') as Climbers
+
+
+from
+ dts
+ left join candidates c
+ on dts.yr = date_part('year', c.ascent_dt_start)
+
+group by
+ dts.yr
+ ,c.grade
+
+order by
+ dts.yr
+
|
|||||||
| 12 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_description | |
|
Before
None
After
Britains hardest boulders through time.
Diff
--- before
+++ after
@@ -1 +1 @@
-
+Britains hardest boulders through time.
|
|||||||
| 13 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_name | |
|
Before
None
After
Britains Hardest Boulders Through Time
|
|||||||
| 14 | 30th December 2024 | 18:45:04 | Mattsparksy | - | - | list_type_name | |
|
Before
None
After
Bouldering
|
|||||||