YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

1 files changed, 70 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql
new file mode 100644
index 0000000000..e0d2d2b0d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/aggregate/yql-18511.sql
@@ -0,0 +1,70 @@
+/* yt can not */
+
+$round_period = ($day, $period) -> {
+    RETURN
+    CASE
+        WHEN $period = 'd' THEN $day
+        WHEN $period = 'w' THEN DateTime::MakeDate(DateTime::StartOfWeek($day))
+        WHEN $period = 'm' THEN DateTime::MakeDate(DateTime::StartOfMonth($day))
+        ELSE $day
+    END
+};
+
+$data = 
+SELECT 
+    $round_period(day, 'd') AS day,
+    $round_period(day, 'w') AS week,
+    $round_period(day, 'm') AS month,
+    IF(user_card_cnt <= 10, user_card_cnt, 11) AS user_cards_segm,
+    is_proven_owner,
+    user_id,
+FROM (
+    SELECT 
+        Date("2024-04-29") AS day,
+        "ALLO" AS mark,
+        "???" AS model,
+        5 AS user_card_cnt,
+        'ACTIVE' AS status,
+        999 AS user_id,
+        1 AS is_proven_owner,
+    UNION ALL
+    SELECT 
+        Date("2024-04-29") AS day,
+        "ALLO" AS mark,
+        "!!!!!!" AS model,
+        50 AS user_card_cnt,
+        'ACTIVE' AS status,
+        1111 AS user_id,
+        0 AS is_proven_owner,
+);
+
+SELECT
+    day,
+    GROUPING(day) AS grouping_day,
+    week,
+    GROUPING(week) AS grouping_week,
+    month,
+    GROUPING(month) as grouping_month,
+    CASE
+        WHEN GROUPING(week) == 1 AND GROUPING(month) == 1 THEN 'd'
+        WHEN GROUPING(day) == 1 AND GROUPING(month) == 1 THEN 'w'
+        WHEN GROUPING(day) == 1 AND GROUPING(week) == 1 THEN 'm'
+        ELSE NULL
+    END AS period_type,
+    user_cards_segm,
+    if(GROUPING(user_cards_segm) = 1, -300, user_cards_segm) AS __user_cards_segm__,
+    GROUPING(user_cards_segm) as grouping_user_cards_segm,
+    COUNT(DISTINCT user_id) AS all_user_qty,
+FROM $data AS t
+GROUP BY
+    GROUPING SETS(
+        -- day grouping
+        (day),
+        (day, user_cards_segm),
+        -- -- week grouping
+        (week),
+        (week, user_cards_segm),
+        -- -- month grouping
+        (month),
+        (month, user_cards_segm)
+    )