Dividing two SQL queries

June 29, 2022

I am not super familiar with SQL queries, but I am trying to divide the results of two queries, ex. # of users with at least $x / total # of users, and I keep running into an error. Here is the code:

SELECT x.number / y.number
FROM 
(
    SELECT COUNT(address) 
    FROM transferAmounts 
    WHERE poolholdings > 0
) x
join
(
    SELECT COUNT(address)
    FROM transferAmounts
) y on 1=1

I tried to do it without the y as well, and it gave me an error still. Here is the entire code above the statement I am having trouble with:

WITH transfers AS (
    SELECT
    evt_tx_hash AS tx_hash,
    tr."from" AS address,
    -tr.value AS amount,
    contract_address
     FROM erc20."ERC20_evt_Transfer" tr
     WHERE contract_address =  '\xD533a949740bb3306d119CC777fa900bA034cd52'
UNION ALL
    SELECT
    evt_tx_hash AS tx_hash,
    tr."to" AS address,
    tr.value AS amount,
      contract_address
     FROM erc20."ERC20_evt_Transfer" tr 
     where contract_address = '\xD533a949740bb3306d119CC777fa900bA034cd52'
),
transferAmounts AS (
    SELECT address,
    
    sum(amount)/1e18 as poolholdings FROM transfers 
    
    GROUP BY 1
    ORDER BY 2 DESC
)

SELECT x.number / y.number
FROM 
(
SELECT COUNT(address) 
FROM transferAmounts 
WHERE poolholdings > 0
) x
join
(
SELECT COUNT(address)
FROM transferAmounts
) y on 1=1

I know the code from the first line up until the SELECT x.number / y.number is correct because I previously used it to count the # of people with $>0 in their crypto wallet. Now I want to modify it to divide that value by the total # of wallets.

>Solution :

you use aliases that where not defined

SELECT x._number / y._number
FROM 
(
    SELECT COUNT(address) _number
    FROM transferAmounts 
    WHERE poolholdings > 0
) x
join
(
    SELECT COUNT(address) _number
    FROM transferAmounts
) y on 1=1