Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 45 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/join/count_bans.sql b/yql/essentials/tests/sql/suites/join/count_bans.sql
new file mode 100644
index 0000000000..499db2c7f7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/join/count_bans.sql
@@ -0,0 +1,45 @@
+PRAGMA DisableSimpleColumns;
+/* postgres can not */
+USE plato;
+
+$records = (
+SELECT
+    key as ip,
+    subkey AS recordType,
+    Url::GetHost(value) AS host
+FROM spider_info
+);
+
+$results = (
+SELECT
+    ip,
+    host,
+    count(*) AS request_count
+FROM $records
+WHERE host IS NOT NULL AND recordType == "RESULT"
+GROUP BY ip, host
+);
+
+$bans = (
+SELECT
+    ip,
+    host,
+    count(*) AS fetcher_count
+FROM $records
+WHERE host IS NOT NULL AND recordType == "BAN_DETECTED"
+GROUP BY ip, host
+);
+
+SELECT
+    results.ip AS ip,
+    results.host AS host,
+    results.request_count AS request_count,
+    bans.fetcher_count AS fetcher_count
+FROM
+    $results AS results
+    INNER JOIN
+    $bans AS bans
+    ON bans.ip == results.ip
+    AND bans.host == results.host
+ORDER BY fetcher_count DESC
+;