LordkingFormat number in table and make it sortable
Updated 2024-01-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
›
⌄
with tmp as (
select *
from external.defillama.fact_dex_volume
order by volume desc limit 40
)
SELECT
volume,
to_varchar(volume, 'FM999,999,999,999') as volume_with_separator,
to_varchar(volume, '$999,999,999,999') as volume_with_dollar_sign,
CASE
WHEN volume >= 1000000000 THEN '$' || ROUND(TO_VARIANT(volume / 1000000000),4) || ' B'
WHEN volume >= 1000000 THEN '$' || ROUND(TO_VARIANT(volume / 1000000),4) || ' M'
WHEN volume >= 1000 THEN '$' || ROUND(TO_VARIANT(volume / 1000),4) || ' K'
ELSE TO_VARIANT(volume)
END AS formatted_volume
FROM
tmp
ORDER BY
CASE
WHEN formatted_volume LIKE '%B' THEN TRY_CAST(SUBSTR(formatted_volume, 1, LENGTH(formatted_volume) - 1) AS FLOAT) * 1000000000
WHEN formatted_volume LIKE '%M' THEN TRY_CAST(SUBSTR(formatted_volume, 1, LENGTH(formatted_volume) - 1) AS FLOAT) * 1000000
WHEN formatted_volume LIKE '%K' THEN TRY_CAST(SUBSTR(formatted_volume, 1, LENGTH(formatted_volume) - 1) AS FLOAT) * 1000
ELSE TRY_CAST(formatted_volume AS FLOAT)
END desc;
QueryRunArchived: QueryRun has been archived