[simple_aggs]
athena = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF')) AS "_values" ("a", "b", "c")), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
bigquery = """
WITH \
values0 AS (SELECT 1 AS `a`, 2 AS `b`, 'A' AS `c` UNION ALL SELECT 3 AS `a`, 2 AS `b`, 'BB' AS `c` UNION ALL SELECT 5 AS `a`, 3 AS `b`, 'CCC' AS `c` UNION ALL SELECT 7 AS `a`, 3 AS `b`, 'DDDD' AS `c` UNION ALL SELECT 9 AS `a`, 3 AS `b`, 'EEEEE' AS `c` UNION ALL SELECT 11 AS `a`, 3 AS `b`, 'FFFFFF' AS `c`), \
values1 AS (SELECT min(`a`) AS `min_a`, max(`a`) AS `max_a`, avg(`a`) AS `avg_a`, sum(`a`) AS `sum_a`, count(`a`) AS `count_a`, `b` FROM values0 GROUP BY `b`) \
SELECT * FROM values1 ORDER BY `b` ASC NULLS FIRST
"""
clickhouse = """
WITH \
values0 AS (SELECT 1 AS "a", 2 AS "b", 'A' AS "c" UNION ALL SELECT 3 AS "a", 2 AS "b", 'BB' AS "c" UNION ALL SELECT 5 AS "a", 3 AS "b", 'CCC' AS "c" UNION ALL SELECT 7 AS "a", 3 AS "b", 'DDDD' AS "c" UNION ALL SELECT 9 AS "a", 3 AS "b", 'EEEEE' AS "c" UNION ALL SELECT 11 AS "a", 3 AS "b", 'FFFFFF' AS "c"), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
databricks = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF')) AS `_values` (`a`, `b`, `c`)), \
values1 AS (SELECT min(`a`) AS `min_a`, max(`a`) AS `max_a`, avg(`a`) AS `avg_a`, sum(`a`) AS `sum_a`, count(`a`) AS `count_a`, `b` FROM values0 GROUP BY `b`) \
SELECT * FROM values1 ORDER BY `b` ASC NULLS FIRST
"""
datafusion = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF')) AS "_values" ("a", "b", "c")), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
duckdb = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF')) AS "_values" ("a", "b", "c")), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
mysql = """
WITH \
values0 AS (SELECT * FROM (VALUES ROW(1, 2, 'A'), ROW(3, 2, 'BB'), ROW(5, 3, 'CCC'), ROW(7, 3, 'DDDD'), ROW(9, 3, 'EEEEE'), ROW(11, 3, 'FFFFFF')) AS `_values` (`a`, `b`, `c`)), \
values1 AS (SELECT min(`a`) AS `min_a`, max(`a`) AS `max_a`, avg(`a`) AS `avg_a`, sum(`a`) AS `sum_a`, count(`a`) AS `count_a`, `b` FROM values0 GROUP BY `b`) \
SELECT * FROM values1 ORDER BY `b` ASC
"""
postgres = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF')) AS "_values" ("a", "b", "c")), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
redshift = """
WITH \
values0 AS (SELECT 1 AS "a", 2 AS "b", 'A' AS "c" UNION ALL SELECT 3 AS "a", 2 AS "b", 'BB' AS "c" UNION ALL SELECT 5 AS "a", 3 AS "b", 'CCC' AS "c" UNION ALL SELECT 7 AS "a", 3 AS "b", 'DDDD' AS "c" UNION ALL SELECT 9 AS "a", 3 AS "b", 'EEEEE' AS "c" UNION ALL SELECT 11 AS "a", 3 AS "b", 'FFFFFF' AS "c"), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
snowflake = """
WITH \
values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b", "COLUMN3" AS "c" FROM (VALUES (1, 2, 'A'), (3, 2, 'BB'), (5, 3, 'CCC'), (7, 3, 'DDDD'), (9, 3, 'EEEEE'), (11, 3, 'FFFFFF'))), \
values1 AS (SELECT min("a") AS "min_a", max("a") AS "max_a", avg("a") AS "avg_a", sum("a") AS "sum_a", count("a") AS "count_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
result = '''
+-------+-------+-------+-------+---------+---+
| min_a | max_a | avg_a | sum_a | count_a | b |
+-------+-------+-------+-------+---------+---+
| 1     | 3     | 2.0   | 4     | 2       | 2 |
| 5     | 11    | 8.0   | 32    | 4       | 3 |
+-------+-------+-------+-------+---------+---+
'''


[median_agg]
athena = "UNSUPPORTED"
bigquery = "UNSUPPORTED"
clickhouse = """
WITH \
values0 AS (SELECT 1.0 AS "a", 2 AS "b" UNION ALL SELECT 3.0 AS "a", 2 AS "b" UNION ALL SELECT 5.5 AS "a", 3 AS "b" UNION ALL SELECT 7.5 AS "a", 3 AS "b" UNION ALL SELECT 100.0 AS "a", 3 AS "b") \
SELECT count("a") AS "count_a", median("a") AS "median_a" FROM values0
"""
databricks = """
WITH \
values0 AS (SELECT * FROM (VALUES (1.0, 2), (3.0, 2), (5.5, 3), (7.5, 3), (100.0, 3)) AS `_values` (`a`, `b`)) \
SELECT count(`a`) AS `count_a`, median(`a`) AS `median_a` FROM values0
"""
datafusion = """
WITH \
values0 AS (SELECT * FROM (VALUES (1.0, 2), (3.0, 2), (5.5, 3), (7.5, 3), (100.0, 3)) AS "_values" ("a", "b")) \
SELECT count("a") AS "count_a", median("a") AS "median_a" FROM values0
"""
duckdb = """
WITH \
values0 AS (SELECT * FROM (VALUES (1.0, 2), (3.0, 2), (5.5, 3), (7.5, 3), (100.0, 3)) AS "_values" ("a", "b")) \
SELECT count("a") AS "count_a", median("a") AS "median_a" FROM values0
"""
mysql = "UNSUPPORTED"
postgres = "UNSUPPORTED"
redshift = "UNSUPPORTED"
snowflake = """
WITH \
values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (1.0, 2), (3.0, 2), (5.5, 3), (7.5, 3), (100.0, 3))) \
SELECT count("a") AS "count_a", median("a") AS "median_a" FROM values0
"""
result = '''
+---------+----------+
| count_a | median_a |
+---------+----------+
| 5       | 5.5      |
+---------+----------+
'''

[variance_aggs]
athena = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3)) AS "_values" ("a", "b")), \
values1 AS (SELECT (round((stddev_samp("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var_samp("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
bigquery = "UNSUPPORTED"
clickhouse = """
WITH \
values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 2 AS "b" UNION ALL SELECT 5 AS "a", 3 AS "b" UNION ALL SELECT 7 AS "a", 3 AS "b" UNION ALL SELECT 9 AS "a", 3 AS "b"), \
values1 AS (SELECT (round((stddevSamp("a") * 100)) / 100) AS "stddev_a", (round((stddevPop("a") * 100)) / 100) AS "stddev_pop_a", (round((varSamp("a") * 100)) / 100) AS "var_a", (round((varPop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
databricks = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3)) AS `_values` (`a`, `b`)), \
values1 AS (SELECT (round((stddev_samp(`a`) * 100)) / 100) AS `stddev_a`, (round((stddev_pop(`a`) * 100)) / 100) AS `stddev_pop_a`, (round((var_samp(`a`) * 100)) / 100) AS `var_a`, (round((var_pop(`a`) * 100)) / 100) AS `var_pop_a`, `b` FROM values0 GROUP BY `b`) \
SELECT * FROM values1 ORDER BY `b` ASC NULLS FIRST
"""
datafusion = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3)) AS "_values" ("a", "b")), \
values1 AS (SELECT (round((stddev("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
duckdb = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3)) AS "_values" ("a", "b")), \
values1 AS (SELECT (round((stddev_samp("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var_samp("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
mysql = """
WITH \
values0 AS (SELECT * FROM (VALUES ROW(1, 2), ROW(3, 2), ROW(5, 3), ROW(7, 3), ROW(9, 3)) AS `_values` (`a`, `b`)), \
values1 AS (SELECT (round((stddev_samp(`a`) * 100)) / 100) AS `stddev_a`, (round((stddev_pop(`a`) * 100)) / 100) AS `stddev_pop_a`, (round((var_samp(`a`) * 100)) / 100) AS `var_a`, (round((var_pop(`a`) * 100)) / 100) AS `var_pop_a`, `b` FROM values0 GROUP BY `b`) \
SELECT * FROM values1 ORDER BY `b` ASC
"""
postgres = """
WITH \
values0 AS (SELECT * FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3)) AS "_values" ("a", "b")), \
values1 AS (SELECT (round((stddev_samp("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var_samp("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
redshift = """
WITH \
values0 AS (SELECT 1 AS "a", 2 AS "b" UNION ALL SELECT 3 AS "a", 2 AS "b" UNION ALL SELECT 5 AS "a", 3 AS "b" UNION ALL SELECT 7 AS "a", 3 AS "b" UNION ALL SELECT 9 AS "a", 3 AS "b"), \
values1 AS (SELECT (round((stddev_samp("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var_samp("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
snowflake = """
WITH \
values0 AS (SELECT "COLUMN1" AS "a", "COLUMN2" AS "b" FROM (VALUES (1, 2), (3, 2), (5, 3), (7, 3), (9, 3))), \
values1 AS (SELECT (round((stddev_samp("a") * 100)) / 100) AS "stddev_a", (round((stddev_pop("a") * 100)) / 100) AS "stddev_pop_a", (round((var_samp("a") * 100)) / 100) AS "var_a", (round((var_pop("a") * 100)) / 100) AS "var_pop_a", "b" FROM values0 GROUP BY "b") \
SELECT * FROM values1 ORDER BY "b" ASC NULLS FIRST
"""
result = '''
+----------+--------------+-------+-----------+---+
| stddev_a | stddev_pop_a | var_a | var_pop_a | b |
+----------+--------------+-------+-----------+---+
| 1.41     | 1.0          | 2.0   | 1.0       | 2 |
| 2.0      | 1.63         | 4.0   | 2.67      | 3 |
+----------+--------------+-------+-----------+---+
'''