What is wrong with SQL query in GOOGLE BIGQUERY?

Question

SQL

Friends, help solve the problem.
I'm trying to build a funnel by URL, the query returns the following: "Error: Cannot group by an aggregate."
The data is taken from Google Analytics.
Here is the query itself:
SELECT
COUNT(DISTINCT s1.clientId ) AS s1.User,
s1.page.pagePath
FROM (
SELECT
COUNT(DISTINCT clientId ) as User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page. pagePath = 'URL 1'
AND page.pagePath != 'utm'
) AS s1
INNER JOIN (
SELECT
COUNT(DISTINCT clientId ) AS User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page.pagePath = 'URL 2'
AND page.pagePath != 'utm') AS s2
ON
s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath
I will be very grateful for the answer.

Answer 1

In order to work, in theory, you need to throw out the grouping by user from the nested selects. Well, the abundance of DISTINCT is bad. At least you can remove DISTINCT from the first SELECT, it seems to me

SELECT COUNT(s1.clientId ) AS s1.User, s1.page.pagePath
FROM (
  SELECT
  COUNT(DISTINCT clientId ) as User,
  page.pagePath
  FROM
  [89437389479847487]
  GROUP BY page.pagePath
  HAVING
  page.pagePath = 'URL 1'
  AND page.pagePath != 'utm'
  ) AS s1
INNER JOIN (SELECT COUNT(DISTINCT clientId ) AS User, page.pagePath
      FROM
      [89437389479847487]
      GROUP BY page.pagePath
      HAVING page.pagePath = 'URL 2' AND page.pagePath != 'utm') AS s2
ON  s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath