Optimize conversion goals

[Kugelschieber]

Both queries work the same, but the second one is 4x faster.

SELECT
	uniq(t.visitor_id) visitors,
	count(1) views,
	toFloat64OrDefault(visitors / greatest((
		SELECT uniq(visitor_id)
		FROM "session"
		WHERE client_id = 0
		AND toDate(time, 'UTC') >= toDate('2023-01-01', 'yyyy-mm-dd')
		AND toDate(time, 'UTC') <= toDate('2023-01-31', 'yyyy-mm-dd')
	), 1)) cr
FROM
	"page_view" t
JOIN (
	SELECT
		visitor_id visitor_id,
		session_id session_id,
		sum(page_views * sign) views
	FROM
		"session" t
	WHERE
		client_id = 0
		AND toDate(time, 'UTC') >= toDate('2023-01-01', 'yyyy-mm-dd')
		AND toDate(time, 'UTC') <= toDate('2023-01-31', 'yyyy-mm-dd')
	GROUP BY
		visitor_id,
		session_id
	HAVING
		sum(sign) > 0
) j ON j.visitor_id = t.visitor_id AND j.session_id = t.session_id
WHERE
	client_id = 0
	AND toDate(time, 'UTC') >= toDate('2023-01-01', 'yyyy-mm-dd')
	AND toDate(time, 'UTC') <= toDate('2023-01-31', 'yyyy-mm-dd')
	AND match("path", '/realm_dxb') = 1
ORDER BY
	visitors DESC
;

SELECT
	uniq(t.visitor_id) visitors,
	count(1) views,
	toFloat64OrDefault(visitors / greatest((
		SELECT uniq(visitor_id)
		FROM "session"
		WHERE client_id = 0
		AND toDate(time, 'UTC') >= toDate('2023-01-01', 'yyyy-mm-dd')
		AND toDate(time, 'UTC') <= toDate('2023-01-31', 'yyyy-mm-dd')
	), 1)) cr
FROM
	"page_view" t
WHERE
	client_id = 0
	AND toDate(time, 'UTC') >= toDate('2023-01-01', 'yyyy-mm-dd')
	AND toDate(time, 'UTC') <= toDate('2023-01-31', 'yyyy-mm-dd')
	AND match("path", '/realm_dxb') = 1
ORDER BY
	visitors DESC
;