[SQL] BigQuery : Subquery SELECT A WHERE B MAX AND C = Parent Alias

I hope you having a safe day!

I was struggling with this BigQuery SQL, I am not expert in BigQuery.

This is how data looks like

This is what I tried

SELECT
D.symbol,
(SELECT
exchange
FROM `wingspan-staging.tardis.derivative_ticker` a
JOIN (SELECT
MAX(t.funding_rate) AS max_fr
FROM `wingspan-staging.tardis.derivative_ticker` t
WHERE t.symbol = D.symbol) b
ON b.max_fr = a.funding_rate
WHERE a.symbol = D.symbol) AS max_exchange,
(SELECT
AVG(funding_rate)
FROM `wingspan-staging.tardis.derivative_ticker`
WHERE exchange = 'ftx'
AND symbol = D.symbol) AS ftx,
(SELECT
AVG(funding_rate)
FROM `wingspan-staging.tardis.derivative_ticker`
WHERE exchange = 'deribit'
AND symbol = D.symbol) AS deribit,
(SELECT
AVG(funding_rate)
FROM `wingspan-staging.tardis.derivative_ticker`
WHERE exchange = 'bitmex'
AND symbol = D.symbol) AS bitmex,
MAX(funding_rate) - MIN(funding_rate) AS diff
FROM `wingspan-staging.tardis.derivative_ticker` D
GROUP BY symbol


This is how I want

Currently everything is working except the second column max_exchange, if I run this code, it takes so long to complete.

(SELECT
exchange
FROM `wingspan-staging.tardis.derivative_ticker` a
JOIN (SELECT
MAX(t.funding_rate) AS max_fr
FROM `wingspan-staging.tardis.derivative_ticker` t
WHERE t.symbol = D.symbol) b
ON b.max_fr = a.funding_rate
WHERE a.symbol = D.symbol) AS max_exchange


If I run this way, the system shows Error Scalar subquery produced more than one element and If I add LIMIT 1 the actual error comes in Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

What I actually want to do is... First I want to group the first column symbol and then want to identify the highest funding_rate containing exchange name within the symbol. Next three columns are average of exchanges and last one is the difference between maximum and minimum of funding_rate.

This is may be simple thing for you, but I am struggling since 2 days. Can anyone help on this, how can I optimize this thing?

Thanks in Advance

Continue reading...