Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 24 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx b/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx
new file mode 100644
index 0000000000..ffc03289d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx
@@ -0,0 +1,24 @@
+/* postgres can not */
+use plato;
+
+$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input);
+
+--insert into Output
+select
+  prefix,
+  region,
+  region + 2 as region_2,
+  age, -- use direct value in aggregation impossible
+  avg(age) as avg_age,
+  sum(age) as sum_age,
+  sum(avg(age)) over w1 as sum_by_avg_age,
+  lag(region) over w1 as prev_region,
+  some(prefix) over w1 as prefix_list,
+  lag(prefix) over w1 as prev_prefix_list,
+  lag(some(name)) over w1 as prev_region_list,
+  'test'
+from $data
+group by region, SUBSTRING(name,0,1) as prefix
+window w1 as (partition by region order by avg(age) desc)
+order by region, avg_age desc
+;