combining two multiple bigquery SELECT FROM statements

Question

I'm somewhat new to BigQuery and SQL, so part of the difficulty I'm having may be that I don't know how to describe the problem I'm having sufficiently well to be able to search for an answer, but I have looked so please be gentle.

What I'm trying to do is count the total number of unique userIds that meet one set of criteria and divide this by the total number of unique userIds.

For example, to count all the unique users who have a purchase:

SELECT count(userId) 
FROM (SELECT userId 
FROM (FLATTEN([table1], user_attribute)) 
WHERE event_value > 0 and event_parameters.Name = "SKU" 
GROUP BY userId ORDER BY userId)

and to count the total number of unique users

 SELECT count(userId) 
 FROM (SELECT userId 
 FROM (FLATTEN([table1], user_attribute)) 
 GROUP BY userId ORDER BY userId)

I've tried to write the query as

SELECT buyers/total 
    FROM (SELECT COUNT(userId) AS buyers 
    FROM (SELECT userId 
    FROM (FLATTEN([table1], user_attribute)) 
    WHERE event_value > 0 and event_parameters.Name = "SKU" 
    GROUP BY userId ORDER BY userId), 
COUNT(userId) as total
    FROM (SELECT userId 
    FROM (FLATTEN([table1], user_attribute)) 
    GROUP BY userId ORDER BY userId))

But it doesn't work. I know I'm doing something fundamentally wrong, but I'm not sure what it is. I would appreciate any help.

Answer 1

You can count distinct users like this:

SELECT
  EXACT_COUNT_DISTINCT(userId) as buyers
FROM (FLATTEN([table1], user_attribute))
WHERE
  event_value > 0
  AND event_parameters.Name = "SKU"

One way to join them is to add a static scalar value and use that for join:

SELECT
  buyers/total
FROM (
  SELECT
    EXACT_COUNT_DISTINCT(userId) AS buyers,
    1 AS scalar,
  FROM (FLATTEN([table1], user_attribute))
  WHERE
    event_value > 0
    AND event_parameters.Name = "SKU") a
JOIN (
  SELECT
    COUNT(userId) AS total,
    1 AS scalar,
  FROM (FLATTEN([table1], user_attribute)) ) b
ON
  a.scalar=b.scalar