SELECT
	client_ip, country, uri,
	MAX_BY(counter, counter) as max_counter_per_min
 FROM (
	WITH logs_with_concat_data AS (
		SELECT
			httprequest.clientip as client_ip,httprequest.country as country, httprequest.uri as uri,
			from_unixtime(timestamp/1000) as datetime
		FROM
			testdb.testtable
		WHERE year = 2020
		AND month = 05
		AND day = 07
		AND hour between 09 and 13
	)
	SELECT
		client_ip, country, uri,
		COUNT(*) as counter
	FROM
		logs_with_concat_data
	WHERE
		datetime > TIMESTAMP '2020-05-07 09:33:00'
	GROUP BY
		client_ip, country, uri,
		date_trunc('minute', datetime)
	HAVING
		(COUNT(*) >= 6.0 AND country = 'TR') OR 
		(COUNT(*) >= 20.0 AND country = 'CN') OR 
		(COUNT(*) >= 30.0 AND country = 'SE') OR 
		(COUNT(*) >= 10.0 AND country NOT IN ('TR','CN','SE'))
) GROUP BY
	client_ip, country, uri
ORDER BY
	max_counter_per_min DESC
LIMIT 10000;