apache · adriangb · Jun 9, 2026 · May 29, 2026 · Jun 7, 2026 · Jun 7, 2026
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q00.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q00.benchmark
@@ -0,0 +1,18 @@
+name Q00
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT COUNT(DISTINCT "SearchPhrase"), COUNT(DISTINCT "MobilePhone"), COUNT(DISTINCT "MobilePhoneModel")
+FROM hits;
+
+result sql_benchmarks/clickbench_extended/results/q00.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q01.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q01.benchmark
@@ -0,0 +1,18 @@
+name Q01
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT COUNT(DISTINCT "HitColor"), COUNT(DISTINCT "BrowserCountry"), COUNT(DISTINCT "BrowserLanguage")
+FROM hits;
+
+result sql_benchmarks/clickbench_extended/results/q01.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q02.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q02.benchmark
@@ -0,0 +1,17 @@
+name Q02
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT "BrowserCountry",  COUNT(DISTINCT "SocialNetwork"), COUNT(DISTINCT "HitColor"), COUNT(DISTINCT "BrowserLanguage"), COUNT(DISTINCT "SocialAction") FROM hits GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
+
+result sql_benchmarks/clickbench_extended/results/q02.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q03.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q03.benchmark
@@ -0,0 +1,17 @@
+name Q03
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT "SocialSourceNetworkID", "RegionID", COUNT(*), AVG("Age"), AVG("ParamPrice"), STDDEV("ParamPrice") as s, VAR("ParamPrice")  FROM hits GROUP BY "SocialSourceNetworkID", "RegionID" HAVING s IS NOT NULL ORDER BY s DESC LIMIT 10;
+
+result sql_benchmarks/clickbench_extended/results/q03.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q04.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q04.benchmark
@@ -0,0 +1,17 @@
+name Q04
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT "ClientIP", "WatchID",  COUNT(*) c, MIN("ResponseStartTiming") tmin, MEDIAN("ResponseStartTiming") tmed, MAX("ResponseStartTiming") tmax FROM hits WHERE "JavaEnable" = 0  GROUP BY  "ClientIP", "WatchID" HAVING c > 1 ORDER BY tmed DESC LIMIT 10;
+
+result sql_benchmarks/clickbench_extended/results/q04.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q05.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q05.benchmark
@@ -0,0 +1,17 @@
+name Q05
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT "ClientIP", "WatchID",  COUNT(*) c, MIN("ResponseStartTiming") tmin, APPROX_PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY "ResponseStartTiming") tp95, MAX("ResponseStartTiming") tmax FROM 'hits' WHERE "JavaEnable" = 0 GROUP BY  "ClientIP", "WatchID" HAVING c > 1 ORDER BY tp95 DESC LIMIT 10;
+
+result sql_benchmarks/clickbench_extended/results/q05.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q06.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q06.benchmark
@@ -0,0 +1,17 @@
+name Q06
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT COUNT(*) AS ShareCount FROM hits WHERE "IsMobile" = 1 AND "MobilePhoneModel" LIKE 'iPhone%' AND "SocialAction" = 'share' AND "SocialSourceNetworkID" IN (5, 12) AND "ClientTimeZone" BETWEEN -5 AND 5 AND regexp_match("Referer", '\/campaign\/(spring|summer)_promo') IS NOT NULL AND CASE WHEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1) ~ '^\d+$' THEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1)::INT ELSE 0 END > 1920 AND levenshtein(CAST("UTMSource" AS STRING), CAST("UTMCampaign" AS STRING)) < 3;
+
+result sql_benchmarks/clickbench_extended/results/q06.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q07.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q07.benchmark
@@ -0,0 +1,17 @@
+name Q07
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT "WatchID", MIN("ResolutionWidth") as wmin, MAX("ResolutionWidth") as wmax, SUM("IsRefresh") as srefresh FROM hits GROUP BY "WatchID" ORDER BY "WatchID" DESC LIMIT 10;
+
+result sql_benchmarks/clickbench_extended/results/q07.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q08.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q08.benchmark
@@ -0,0 +1,19 @@
+name Q08
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+-- Must set for ClickBench hits_partitioned dataset. See https://github.com/apache/datafusion/issues/16591
+-- set datafusion.execution.parquet.binary_as_string = true
+SELECT "RegionID", "UserAgent", "OS", AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time, AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency FROM hits GROUP BY "RegionID", "UserAgent", "OS" ORDER BY avg_latency DESC limit 10;
+
+result sql_benchmarks/clickbench_extended/results/q08.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q09.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q09.benchmark
@@ -0,0 +1,21 @@
+name Q09
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT MAX(len) FROM (
+    SELECT LENGTH(FIRST_VALUE("URL" ORDER BY "EventTime")) as len
+    FROM hits
+    GROUP BY "UserID"
+);
+
+result sql_benchmarks/clickbench_extended/results/q09.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q10.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q10.benchmark
@@ -0,0 +1,21 @@
+name Q10
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT MAX(len) FROM (
+    SELECT LENGTH(FIRST_VALUE("URL" ORDER BY "EventTime")) as len
+    FROM hits
+    GROUP BY "OS"
+);
+
+result sql_benchmarks/clickbench_extended/results/q10.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q11.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q11.benchmark
@@ -0,0 +1,21 @@
+name Q11
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT MAX(fv) FROM (
+    SELECT FIRST_VALUE("WatchID" ORDER BY "EventTime") as fv
+    FROM hits
+    GROUP BY "UserID"
+);
+
+result sql_benchmarks/clickbench_extended/results/q11.csv
diff --git a/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q12.benchmark b/benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q12.benchmark
@@ -0,0 +1,21 @@
+name Q12
+group clickbench_extended
+subgroup ${CLICKBENCH_TYPE:-single}
+
+init sql_benchmarks/clickbench/init/set_config.sql
+
+load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
+
+assert I
+SELECT COUNT(*) > 0 from hits;
+----
+true
+
+run
+SELECT MAX(fv) FROM (
+    SELECT FIRST_VALUE("WatchID" ORDER BY "EventTime") as fv
+    FROM hits
+    GROUP BY "OS"
+);
+
+result sql_benchmarks/clickbench_extended/results/q12.csv