Skip to content

Instantly share code, notes, and snippets.

@billsbooth

billsbooth/trades-queries.sql

Created November 17, 2025 18:11
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save billsbooth/d9681b4843575d3993fd9537a4c6e7b9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save billsbooth/d9681b4843575d3993fd9537a4c6e7b9 to your computer and use it in GitHub Desktop.
Download ZIP
Trades Queries with a User Address
Raw
trades-queries.sql
SELECT *
FROM system.query_log
WHERE (type = 'QueryFinish') AND (query ILIKE '%DuQabFqdC9eeBULVa7TTdZYxe8vK8ct5DZr4Xcf7docy%')
ORDER BY event_time ASC
LIMIT 2
Query id: ab6ead71-df30-4e3e-b77b-16c1ab8a3823
Row 1:
──────
hostname: chi-bw-us-east-1-bw-us-east-1-0-2-0.chi-bw-us-east-1-bw-us-east-1-0-2.altinity-cloud-managed-clickhouse.svc.cluster.local
type: QueryFinish
event_date: 2025-11-07
event_time: 2025-11-07 11:45:18
event_time_microseconds: 2025-11-07 11:45:18.874220
query_start_time: 2025-11-07 11:45:15
query_start_time_microseconds: 2025-11-07 11:45:15.928728
query_duration_ms: 2945
read_rows: 1429531 -- 1.43 million
read_bytes: 128111863 -- 128.11 million
written_rows: 0
written_bytes: 0
result_rows: 5
result_bytes: 7200
memory_usage: 274638539 -- 274.64 million
current_database: public
query:
WITH
trades AS (
SELECT * FROM trades
WHERE
user_address = 'DuQabFqdC9eeBULVa7TTdZYxe8vK8ct5DZr4Xcf7docy'
AND timestamp < toDateTime64('2025-11-07 11:45:15.867596949', 3)
AND timestamp >= toDateTime64('2025-10-31 11:45:15.867596949', 3)
ORDER BY timestamp DESC
LIMIT 5
)
SELECT
trades.network,
trades.exchange,
trades.market_id,
trades.base_token_contract_address,
trades.quote_token_contract_address,
trades.user_address,
trades.base_amount / intExp10(base_tokens.decimals),
trades.quote_amount / intExp10(quote_tokens.decimals),
if(
base_tokens.decimals > quote_tokens.decimals,
trades.price * intExp10(base_tokens.decimals - quote_tokens.decimals),
trades.price / intExp10(quote_tokens.decimals - base_tokens.decimals)
) AS price,
trades.direction,
trades.timestamp,
trades.block,
if(
direction = 'base_to_quote',
trades.trade_fee / intExp10(base_tokens.decimals),
trades.trade_fee / intExp10(quote_tokens.decimals)
) AS trade_fee,
if(
direction = 'base_to_quote',
trades.protocol_fee / intExp10(base_tokens.decimals),
trades.protocol_fee / intExp10(quote_tokens.decimals)
) AS protocol_fee,
trades.tx_signature,
trades.instruction,
trades.created_at
FROM trades
LEFT ANY JOIN tokens as base_tokens ON
base_tokens.network = trades.network
AND base_tokens.address = trades.base_token_contract_address
LEFT ANY JOIN quote_tokens_dict as quote_tokens ON
quote_tokens.network = trades.network
AND quote_tokens.address = trades.quote_token_contract_address
ORDER BY timestamp DESC, tx_signature ASC, instruction ASC
formatted_query:
normalized_query_hash: 9323923237422491991 -- 9.32 quintillion
query_kind: Select
databases: ['public']
tables: ['public.quote_tokens_dict','public.tokens','public.trades']
columns: ['public.quote_tokens_dict.address','public.quote_tokens_dict.decimals','public.quote_tokens_dict.network','public.tokens.address','public.tokens.decimals','public.tokens.network','public.trades.base_amount','public.trades.base_token_contract_address','public.trades.block','public.trades.created_at','public.trades.direction','public.trades.exchange','public.trades.instruction','public.trades.market_id','public.trades.network','public.trades.price','public.trades.protocol_fee','public.trades.quote_amount','public.trades.quote_token_contract_address','public.trades.timestamp','public.trades.trade_fee','public.trades.tx_signature','public.trades.user_address']
partitions: ['public.tokens.all','public.trades.all']
projections: ['public.trades.trade_user_projection']
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: admin
query_id: b4a844b1-ed4f-4941-8918-d38dd38e2921
address: ::ffff:10.129.251.23
port: 60590
initial_user: admin
initial_query_id: b4a844b1-ed4f-4941-8918-d38dd38e2921
initial_address: ::ffff:10.129.251.23
initial_port: 60590
initial_query_start_time: 2025-11-07 11:45:15
initial_query_start_time_microseconds: 2025-11-07 11:45:15.928728
interface: 1
is_secure: 0
os_user:
client_hostname: 8c51d06cf980
client_name: ferumv5-go-client/0.1 clickhouse-go/2.30.0 (lv:go/1.23.3; os:linux)
client_revision: 54460
client_version_major: 2
client_version_minor: 30
client_version_patch: 0
script_query_number: 0
script_line_number: 0
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 4294967295 -- 4.29 billion
log_comment:
thread_ids: [751,10245,1577,10069,10138,1484,672,1440,773,852,745,1436,1571,1606,1547,1675,866,669,774,720,1508,1599,667,848,880,691,1347,703,900,772,1433,1515,1503,706,903,755,1346,1385,1502,1597,1526,1329,1348,1267,1661,754,716,9975,1701,1514,719,1638,850,909,712,709,1497,906,1609,1512,895,698,689,1674,886,1546,1349,710,907,1695,1690,10261,1593,1425,1622,1641,1651,5197,1454,1573,1379,799,1390,1658,870,1458,1064,790,1381,884,1672,713,1698,1353,762,1635,1586,704,1689,901,1492,1686,1065,1262,5196,1650,1570,585,855,1643,1596,875,1663,771,1541,871,1659,1671,883,1516,885,1673,700,897,1602,1642,1351,1645,763,1354,668,865,1259,859,1647,1677,904,1692,707,1383,1580,1442,1583,5195,876,1664,894,697,1618,1339,1424,1621,1435,1532,1326,1605,1702,896,1684,1594,806,881,684,675,1266,685,882]
peak_threads_usage: 22
ProfileEvents: {'Query':1,'SelectQuery':1,'InitialQuery':1,'QueriesWithSubqueries':2,'SelectQueriesWithSubqueries':2,'SelectQueriesWithPrimaryKeyUsage':3,'FileOpen':49,'ReadBufferFromFileDescriptorReadBytes':84247444,'ReadCompressedBytes':81794814,'CompressedReadBufferBlocks':598,'CompressedReadBufferBytes':115045146,'OpenedFileCacheHits':223,'OpenedFileCacheMisses':49,'OpenedFileCacheMicroseconds':247,'IOBufferAllocs':546,'IOBufferAllocBytes':64102544,'ArenaAllocChunks':1,'ArenaAllocBytes':4096,'FunctionExecute':196,'MarkCacheHits':272,'CreatedReadBufferOrdinary':272,'DiskReadElapsedMicroseconds':122573,'NetworkReceiveElapsedMicroseconds':26,'NetworkSendElapsedMicroseconds':1193,'NetworkSendBytes':178985,'GlobalThreadPoolLockWaitMicroseconds':20050,'GlobalThreadPoolJobs':89,'LocalThreadPoolExpansions':88,'LocalThreadPoolShrinks':56,'LocalThreadPoolThreadCreationMicroseconds':76475,'LocalThreadPoolLockWaitMicroseconds':3576189,'LocalThreadPoolJobs':989,'JoinBuildTableRowCount':1278545,'JoinProbeTableRowCount':10,'JoinResultRowCount':10,'SelectedParts':34,'SelectedPartsTotal':35,'SelectedRanges':34,'SelectedMarks':185,'SelectedMarksTotal':6109,'SelectedRows':1429531,'SelectedBytes':128111863,'RowsReadByMainReader':1429529,'FilteringMarksWithPrimaryKeyMicroseconds':8965,'WaitMarksLoadMicroseconds':1642,'ContextLock':130,'RWLockAcquiredReadLocks':3,'PartsLockHoldMicroseconds':174,'PartsLockWaitMicroseconds':10,'RealTimeMicroseconds':12720271,'UserTimeMicroseconds':526085,'SystemTimeMicroseconds':263726,'SoftPageFaults':62654,'OSIOWaitMicroseconds':70000,'OSCPUWaitMicroseconds':475287,'OSCPUVirtualTimeMicroseconds':789188,'OSReadBytes':7929856,'OSReadChars':84786211,'OSWriteChars':29400,'QueryProfilerRuns':13,'ThreadPoolReaderPageCacheMiss':837,'ThreadPoolReaderPageCacheMissBytes':84247444,'ThreadPoolReaderPageCacheMissElapsedMicroseconds':122573,'SynchronousReadWaitMicroseconds':5526261,'HashJoinPreallocatedElementsInHashTables':1162624,'InterfaceNativeSendBytes':178985,'ConcurrencyControlSlotsGranted':1,'ConcurrencyControlSlotsDelayed':31,'ConcurrencyControlSlotsAcquired':2,'ConcurrencyControlSlotsAcquiredNonCompeting':1,'ConcurrencyControlQueriesDelayed':1,'FilterTransformPassedRows':297352,'FilterTransformPassedBytes':172755305}
Settings: {'connect_timeout_with_failover_ms':'1000','load_balancing':'nearest_hostname','distributed_aggregation_memory_efficient':'1','do_not_merge_across_partitions_select_final':'1','os_thread_priority':'2','log_queries':'1','max_bytes_before_external_group_by':'65498251264','max_bytes_before_external_sort':'65498251264','max_memory_usage':'117896852275','prefer_localhost_replica':'0','parallel_view_processing':'1','async_insert_busy_timeout_max_ms':'200','parallel_replicas_for_cluster_engines':'0'}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: ['public.quote_tokens_dict']
used_formats: []
used_functions: ['less','divide','toYYYYMM','equals','if','toDateTime64','intExp10','greaterOrEquals','minus','and','multiply','notIn','greater']
used_storages: []
used_table_functions: []
used_row_policies: []
used_privileges: ['SELECT(decimals, network, address) ON public.quote_tokens_dict','SELECT(decimals, network, address) ON public.tokens','SELECT(network, exchange, market_id, base_token_contract_address, quote_token_contract_address, user_address, base_amount, quote_amount, price, direction, timestamp, block, trade_fee, protocol_fee, tx_signature, instruction, created_at) ON public.trades']
missing_privileges: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
query_cache_usage: None
asynchronous_read_counters: {}
Row 2:
──────
hostname: chi-bw-us-east-1-bw-us-east-1-0-2-0.chi-bw-us-east-1-bw-us-east-1-0-2.altinity-cloud-managed-clickhouse.svc.cluster.local
type: QueryFinish
event_date: 2025-11-07
event_time: 2025-11-07 12:46:55
event_time_microseconds: 2025-11-07 12:46:55.017211
query_start_time: 2025-11-07 12:46:50
query_start_time_microseconds: 2025-11-07 12:46:50.521095
query_duration_ms: 4496
read_rows: 1416195 -- 1.42 million
read_bytes: 122869475 -- 122.87 million
written_rows: 0
written_bytes: 0
result_rows: 5
result_bytes: 6440
memory_usage: 275723621 -- 275.72 million
current_database: public
query:
WITH
trades AS (
SELECT * FROM trades
WHERE
user_address = 'DuQabFqdC9eeBULVa7TTdZYxe8vK8ct5DZr4Xcf7docy'
AND timestamp < toDateTime64('2025-11-07 12:46:50.515768015', 3)
AND timestamp >= toDateTime64('2025-10-31 12:46:50.515768015', 3)
ORDER BY timestamp DESC
LIMIT 5
)
SELECT
trades.network,
trades.exchange,
trades.market_id,
trades.base_token_contract_address,
trades.quote_token_contract_address,
trades.user_address,
trades.base_amount / intExp10(base_tokens.decimals),
trades.quote_amount / intExp10(quote_tokens.decimals),
if(
base_tokens.decimals > quote_tokens.decimals,
trades.price * intExp10(base_tokens.decimals - quote_tokens.decimals),
trades.price / intExp10(quote_tokens.decimals - base_tokens.decimals)
) AS price,
trades.direction,
trades.timestamp,
trades.block,
if(
direction = 'base_to_quote',
trades.trade_fee / intExp10(base_tokens.decimals),
trades.trade_fee / intExp10(quote_tokens.decimals)
) AS trade_fee,
if(
direction = 'base_to_quote',
trades.protocol_fee / intExp10(base_tokens.decimals),
trades.protocol_fee / intExp10(quote_tokens.decimals)
) AS protocol_fee,
trades.tx_signature,
trades.instruction,
trades.created_at
FROM trades
LEFT ANY JOIN tokens as base_tokens ON
base_tokens.network = trades.network
AND base_tokens.address = trades.base_token_contract_address
LEFT ANY JOIN quote_tokens_dict as quote_tokens ON
quote_tokens.network = trades.network
AND quote_tokens.address = trades.quote_token_contract_address
ORDER BY timestamp DESC, tx_signature ASC, instruction ASC
formatted_query:
normalized_query_hash: 9323923237422491991 -- 9.32 quintillion
query_kind: Select
databases: ['public']
tables: ['public.quote_tokens_dict','public.tokens','public.trades']
columns: ['public.quote_tokens_dict.address','public.quote_tokens_dict.decimals','public.quote_tokens_dict.network','public.tokens.address','public.tokens.decimals','public.tokens.network','public.trades.base_amount','public.trades.base_token_contract_address','public.trades.block','public.trades.created_at','public.trades.direction','public.trades.exchange','public.trades.instruction','public.trades.market_id','public.trades.network','public.trades.price','public.trades.protocol_fee','public.trades.quote_amount','public.trades.quote_token_contract_address','public.trades.timestamp','public.trades.trade_fee','public.trades.tx_signature','public.trades.user_address']
partitions: ['public.tokens.all','public.trades.all']
projections: ['public.trades.trade_user_projection']
views: []
exception_code: 0
exception:
stack_trace:
is_initial_query: 1
user: admin
query_id: 2e1e8da9-7a25-42e7-ae50-f2594da0885f
address: ::ffff:10.129.251.23
port: 59934
initial_user: admin
initial_query_id: 2e1e8da9-7a25-42e7-ae50-f2594da0885f
initial_address: ::ffff:10.129.251.23
initial_port: 59934
initial_query_start_time: 2025-11-07 12:46:50
initial_query_start_time_microseconds: 2025-11-07 12:46:50.521095
interface: 1
is_secure: 0
os_user:
client_hostname: 8c51d06cf980
client_name: ferumv5-go-client/0.1 clickhouse-go/2.30.0 (lv:go/1.23.3; os:linux)
client_revision: 54460
client_version_major: 2
client_version_minor: 30
client_version_patch: 0
script_query_number: 0
script_line_number: 0
http_method: 0
http_user_agent:
http_referer:
forwarded_for:
quota_key:
distributed_depth: 0
revision: 4294967295 -- 4.29 billion
log_comment:
thread_ids: [1496,1351,10302,10217,1625,667,771,1385,852,698,751,773,1484,719,799,716,1701,1699,1502,1597,866,669,876,10069,1436,685,700,1547,1677,1571,865,668,1659,754,697,894,884,10138,880,684,881,909,712,1606,1508,720,745,1497,906,709,1329,1526,672,904,1692,707,1674,886,10145,689,1690,1433,1424,10155,896,1514,755,1346,10261,1503,10144,885,691,872,675,772,1577,1599,1675,10114,10133,762,859,10074,1609,1512,1516,1689,704,10160,901,1492,900,703,1491,1348,10213,1545,10248,1645,10116,1702,1453,5196,850,10109,1638,10209,1541,1065,774,1515,10183,10254,1427,1639,9925,863,1391,10059,10167,908,1574,10034,1438,1381,790,10102,875,1663,10192,1382,1576,1335,706,903,10162,1529,1658,1461,870,1064,10041,10115,1347,10212,1661,1267,10288,1611,10082,1546,10119,713,1392,710,907,10273,1594,10262,1511,1573,10241,10285,1678,890,10202,1440,849,1622,1425,1601,10185,1359,848]
peak_threads_usage: 10
ProfileEvents: {'Query':1,'SelectQuery':1,'InitialQuery':1,'QueriesWithSubqueries':2,'SelectQueriesWithSubqueries':2,'SelectQueriesWithPrimaryKeyUsage':3,'FileOpen':17,'ReadBufferFromFileDescriptorReadBytes':81506800,'ReadCompressedBytes':79599288,'CompressedReadBufferBlocks':538,'CompressedReadBufferBytes':109653594,'OpenedFileCacheHits':201,'OpenedFileCacheMisses':17,'OpenedFileCacheMicroseconds':92,'IOBufferAllocs':438,'IOBufferAllocBytes':53693311,'ArenaAllocChunks':1,'ArenaAllocBytes':4096,'FunctionExecute':172,'MarkCacheHits':218,'CreatedReadBufferOrdinary':218,'DiskReadElapsedMicroseconds':15788,'NetworkReceiveElapsedMicroseconds':24,'NetworkSendElapsedMicroseconds':1182,'NetworkSendBytes':175772,'GlobalThreadPoolLockWaitMicroseconds':37938,'GlobalThreadPoolJobs':99,'LocalThreadPoolExpansions':98,'LocalThreadPoolShrinks':66,'LocalThreadPoolThreadCreationMicroseconds':1156671,'LocalThreadPoolLockWaitMicroseconds':2444304,'LocalThreadPoolJobs':911,'JoinBuildTableRowCount':1278923,'JoinProbeTableRowCount':10,'JoinResultRowCount':10,'SelectedParts':28,'SelectedPartsTotal':30,'SelectedRanges':28,'SelectedMarks':179,'SelectedMarksTotal':6181,'SelectedRows':1416195,'SelectedBytes':122869475,'RowsReadByMainReader':1416193,'FilteringMarksWithPrimaryKeyMicroseconds':9062,'WaitMarksLoadMicroseconds':947,'ContextLock':130,'RWLockAcquiredReadLocks':3,'PartsLockHoldMicroseconds':102,'PartsLockWaitMicroseconds':31,'RealTimeMicroseconds':11915757,'UserTimeMicroseconds':482750,'SystemTimeMicroseconds':107787,'SoftPageFaults':34176,'OSCPUWaitMicroseconds':213212,'OSCPUVirtualTimeMicroseconds':589957,'OSReadChars':81999081,'OSWriteChars':28224,'QueryProfilerRuns':11,'ThreadPoolReaderPageCacheMiss':772,'ThreadPoolReaderPageCacheMissBytes':81506800,'ThreadPoolReaderPageCacheMissElapsedMicroseconds':15788,'SynchronousReadWaitMicroseconds':3392394,'HashJoinPreallocatedElementsInHashTables':1162880,'InterfaceNativeSendBytes':175772,'ConcurrencyControlSlotsGranted':1,'ConcurrencyControlSlotsDelayed':31,'ConcurrencyControlSlotsAcquired':7,'ConcurrencyControlSlotsAcquiredNonCompeting':1,'ConcurrencyControlQueriesDelayed':1,'FilterTransformPassedRows':269924,'FilterTransformPassedBytes':156897282}
Settings: {'connect_timeout_with_failover_ms':'1000','load_balancing':'nearest_hostname','distributed_aggregation_memory_efficient':'1','do_not_merge_across_partitions_select_final':'1','os_thread_priority':'2','log_queries':'1','max_bytes_before_external_group_by':'65498251264','max_bytes_before_external_sort':'65498251264','max_memory_usage':'117896852275','prefer_localhost_replica':'0','parallel_view_processing':'1','async_insert_busy_timeout_max_ms':'200','parallel_replicas_for_cluster_engines':'0'}
used_aggregate_functions: []
used_aggregate_function_combinators: []
used_database_engines: []
used_data_type_families: []
used_dictionaries: ['public.quote_tokens_dict']
used_formats: []
used_functions: ['less','divide','toYYYYMM','equals','if','toDateTime64','intExp10','greaterOrEquals','minus','and','multiply','notIn','greater']
used_storages: []
used_table_functions: []
used_row_policies: []
used_privileges: ['SELECT(decimals, network, address) ON public.quote_tokens_dict','SELECT(decimals, network, address) ON public.tokens','SELECT(network, exchange, market_id, base_token_contract_address, quote_token_contract_address, user_address, base_amount, quote_amount, price, direction, timestamp, block, trade_fee, protocol_fee, tx_signature, instruction, created_at) ON public.trades']
missing_privileges: []
transaction_id: (0,0,'00000000-0000-0000-0000-000000000000')
query_cache_usage: None
asynchronous_read_counters: {}
2 rows in set. Elapsed: 1.012 sec. Processed 25.77 million rows, 51.67 GB (25.47 million rows/s., 51.07 GB/s.)
Peak memory usage: 475.03 MiB.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment