ClickHouse query

gistfile1.txt

SELECT
 	groupBy,
 	groupArray(Requested) AS Requested,


 	groupArray(KeywordsRankGroup0) AS KeywordsRankGroup0,
 	arrayMap(x -> x-KeywordsRankGroup0[1], KeywordsRankGroup0) AS KeywordsRankGroup0Change,

 	groupArray(KeywordsRankGroup1) AS KeywordsRankGroup1,
 	arrayMap(x -> x-KeywordsRankGroup1[1], KeywordsRankGroup1) AS KeywordsRankGroup1Change,

 	groupArray(KeywordsRankGroup2) AS KeywordsRankGroup2,
 	arrayMap(x -> x-KeywordsRankGroup2[1], KeywordsRankGroup2) AS KeywordsRankGroup2Change,

 	groupArray(KeywordsRankGroup3) AS KeywordsRankGroup3,
 	arrayMap(x -> x-KeywordsRankGroup3[1], KeywordsRankGroup3) AS KeywordsRankGroup3Change,

 	groupArray(KeywordsRankGroup4) AS KeywordsRankGroup4,
 	arrayMap(x -> x-KeywordsRankGroup4[1], KeywordsRankGroup4) AS KeywordsRankGroup4Change,

 	groupArray(KeywordsRankGroup5) AS KeywordsRankGroup5,
 	arrayMap(x -> x-KeywordsRankGroup5[1], KeywordsRankGroup5) AS KeywordsRankGroup5Change,


 	groupArray(URLsRankGroup0) AS URLsRankGroup0,
 	arrayMap(x -> x-URLsRankGroup0[1], URLsRankGroup0) AS URLsRankGroup0Change,

 	groupArray(URLsRankGroup1) AS URLsRankGroup1,
 	arrayMap(x -> x-URLsRankGroup1[1], URLsRankGroup1) AS URLsRankGroup1Change,

 	groupArray(URLsRankGroup2) AS URLsRankGroup2,
 	arrayMap(x -> x-URLsRankGroup2[1], URLsRankGroup2) AS URLsRankGroup2Change,

 	groupArray(URLsRankGroup3) AS URLsRankGroup3,
 	arrayMap(x -> x-URLsRankGroup3[1], URLsRankGroup3) AS URLsRankGroup3Change,

 	groupArray(URLsRankGroup4) AS URLsRankGroup4,
 	arrayMap(x -> x-URLsRankGroup4[1], URLsRankGroup4) AS URLsRankGroup4Change,

 	groupArray(URLsRankGroup5) AS URLsRankGroup5,
 	arrayMap(x -> x-URLsRankGroup5[1], URLsRankGroup5) AS URLsRankGroup5Change

 FROM (
 	SELECT
 		Requested,
 		groupBy,

 		KeywordsRankGroup0,
 		KeywordsRankGroup1,
 		KeywordsRankGroup2,
 		KeywordsRankGroup3,
 		KeywordsRankGroup4,
 		KeywordsRankGroup5,

 		URLsRankGroup0,
 		URLsRankGroup1,
 		URLsRankGroup2,
 		URLsRankGroup3,
 		URLsRankGroup4,
 		URLsRankGroup5
 	FROM
 	(
 		SELECT
 			arrayJoin([toDateTime('2018-03-01 00:00:00'),toDateTime('2018-03-02 00:00:00'),toDateTime('2018-03-03 00:00:00'),toDateTime('2018-03-04 00:00:00'),toDateTime('2018-03-05 00:00:00'),toDateTime('2018-03-06 00:00:00'),toDateTime('2018-03-07 00:00:00'),toDateTime('2018-03-08 00:00:00'),toDateTime('2018-03-09 00:00:00'),toDateTime('2018-03-10 00:00:00'),toDateTime('2018-03-11 00:00:00'),toDateTime('2018-03-12 00:00:00'),toDateTime('2018-03-13 00:00:00'),toDateTime('2018-03-14 00:00:00'),toDateTime('2018-03-15 00:00:00'),toDateTime('2018-03-16 00:00:00'),toDateTime('2018-03-17 00:00:00'),toDateTime('2018-03-18 00:00:00'),toDateTime('2018-03-19 00:00:00'),toDateTime('2018-03-20 00:00:00'),toDateTime('2018-03-21 00:00:00'),toDateTime('2018-03-22 00:00:00'),toDateTime('2018-03-23 00:00:00'),toDateTime('2018-03-24 00:00:00'),toDateTime('2018-03-25 00:00:00'),toDateTime('2018-03-26 00:00:00'),toDateTime('2018-03-27 00:00:00'),toDateTime('2018-03-28 00:00:00'),toDateTime('2018-03-29 00:00:00')]) AS Requested,
          groupBy
 		FROM
 		(
 			SELECT DISTINCT
 				groupBy
 			FROM (
 				WITH
 					sum(Results.EstimatedTraffic) AS sortField,
 					arrayJoin(splitByChar(';', dictGetString('TeamScheduleKeywords', 'Groups', (TeamScheduleID, KeywordID)))) AS groupBy
 				SELECT
 					groupBy
 				FROM OldWorkspaceRankings
 				ARRAY JOIN Results
 				WHERE Requested IN('2018-03-01 00:00:00','2018-03-02 00:00:00','2018-03-03 00:00:00','2018-03-04 00:00:00','2018-03-05 00:00:00','2018-03-06 00:00:00','2018-03-07 00:00:00','2018-03-08 00:00:00','2018-03-09 00:00:00','2018-03-10 00:00:00','2018-03-11 00:00:00','2018-03-12 00:00:00','2018-03-13 00:00:00','2018-03-14 00:00:00','2018-03-15 00:00:00','2018-03-16 00:00:00','2018-03-17 00:00:00','2018-03-18 00:00:00','2018-03-19 00:00:00','2018-03-20 00:00:00','2018-03-21 00:00:00','2018-03-22 00:00:00','2018-03-23 00:00:00','2018-03-24 00:00:00','2018-03-25 00:00:00','2018-03-26 00:00:00','2018-03-27 00:00:00','2018-03-28 00:00:00','2018-03-29 00:00:00')
 				AND ((lower(WorkspaceID)='redventures') AND (Results.Rank>=1) AND ((TeamScheduleID=466)))
 				GROUP BY Requested, groupBy
 				ORDER BY sortField DESC
 			) LIMIT 10, 5
 		)
 	)
 	ANY LEFT JOIN
 	(
 		WITH arrayJoin(splitByChar(';', dictGetString('TeamScheduleKeywords', 'Groups', (TeamScheduleID, KeywordID)))) AS groupBy
 		SELECT
 			groupBy,
 			Requested,

 			uniqExactIf(KeywordID, Results.RankGroup=0) AS KeywordsRankGroup0,
 			uniqExactIf(KeywordID, Results.RankGroup=1) AS KeywordsRankGroup1,
 			uniqExactIf(KeywordID, Results.RankGroup=2) AS KeywordsRankGroup2,
 			uniqExactIf(KeywordID, Results.RankGroup=3) AS KeywordsRankGroup3,
 			uniqExactIf(KeywordID, Results.RankGroup=4) AS KeywordsRankGroup4,
 			uniqExactIf(KeywordID, Results.RankGroup=5) AS KeywordsRankGroup5,

 			uniqExactIf(Results.URL, Results.RankGroup=0) AS URLsRankGroup0,
 			uniqExactIf(Results.URL, Results.RankGroup=1) AS URLsRankGroup1,
 			uniqExactIf(Results.URL, Results.RankGroup=2) AS URLsRankGroup2,
 			uniqExactIf(Results.URL, Results.RankGroup=3) AS URLsRankGroup3,
 			uniqExactIf(Results.URL, Results.RankGroup=4) AS URLsRankGroup4,
 			uniqExactIf(Results.URL, Results.RankGroup=5) AS URLsRankGroup5
 		FROM OldWorkspaceRankings
 		ARRAY JOIN Results
 		WHERE Requested IN('2018-03-01 00:00:00','2018-03-02 00:00:00','2018-03-03 00:00:00','2018-03-04 00:00:00','2018-03-05 00:00:00','2018-03-06 00:00:00','2018-03-07 00:00:00','2018-03-08 00:00:00','2018-03-09 00:00:00','2018-03-10 00:00:00','2018-03-11 00:00:00','2018-03-12 00:00:00','2018-03-13 00:00:00','2018-03-14 00:00:00','2018-03-15 00:00:00','2018-03-16 00:00:00','2018-03-17 00:00:00','2018-03-18 00:00:00','2018-03-19 00:00:00','2018-03-20 00:00:00','2018-03-21 00:00:00','2018-03-22 00:00:00','2018-03-23 00:00:00','2018-03-24 00:00:00','2018-03-25 00:00:00','2018-03-26 00:00:00','2018-03-27 00:00:00','2018-03-28 00:00:00','2018-03-29 00:00:00')
 		AND ((lower(WorkspaceID)='redventures') AND (Results.Rank>=1) AND ((TeamScheduleID=466)))
 		GROUP BY Requested, groupBy
 	)
 	USING (Requested, groupBy)
 	ORDER BY Requested
 )
 GROUP BY groupBy
 ORDER BY arrayElement(KeywordsRankGroup0, length(KeywordsRankGroup0)) DESC