-- Combine all sources into a single dataset

SELECT * FROM $query('b55e1dfb-250b-467d-89c1-f9de879e8ea4')

UNION ALL

SELECT * FROM $query('6f5da6aa-34a2-4859-9704-66b77e96e760')

UNION ALL

SELECT * FROM $query('120fe825-35a9-455f-860b-4624cf8886ca')

UNION ALL

SELECT * FROM $query('eb7b20a5-01a4-4667-982f-8d135d9d3faa')

),

filtered_data AS (

-- Sum the 30d change for Bitcoin and Ethereum

SELECT

SUM("30d change") AS raw_value

FROM combined_data

WHERE Chain IN ('Bitcoin', 'Ethereum')

),

value_range AS (

-- Ensure min_value and max_value are not NULL

SELECT

COALESCE(MIN(raw_value), 0) AS min_value,

COALESCE(MAX(raw_value), 0) AS max_value

FROM filtered_data

),

final_score AS (

SELECT

raw_value,

min_value,

max_value,

CASE

WHEN min_value = max_value THEN 50 -- Prevent division by zero, assign neutral score

ELSE ROUND(((raw_value - min_value) / NULLIF(max_value - min_value, 0)) * 100, 2)

END AS Final_Score