# Clickhouse notes/questions

## Schema
- chain_id: LowCardinality(String);
- from: String;
- to: String;
- timestamp: Date;
- block_number: Uint256;
- tx_index: Uint32;

ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (chain_id, timestamp, block_number, tx_index)
SETTINGS index_granularity=1024

Projection 1: (from,timestamp,block_number,tx_index)
Projection 2: (to,timestamp,block_number,tx_index)

# Query 1
Txs, ordered by timestamp DESC, single chain or cross chain

SELECT * FROM transactions WHERE "chain_id"='...' ORDER BY timestamp DESC, block_number DESC, tx_index DESC LIMIT 10;
SELECT * FROM transactions ORDER BY timestamp DESC, block_number DESC, tx_index DESC LIMIT 10;
SELECT COUNT(*) FROM transactions WHERE ...; -- Same filters as above

# Query 2
Txs, ordered by timestamp DESC, single chain or cross chain, for a given FROM address

SELECT * FROM transactions WHERE "chain_id"="..." AND "from"='...' ORDER BY timestamp DESC LIMIT 10;
SELECT * FROM transactions WHERE "from"='...' ORDER BY timestamp DESC LIMIT 10;
SELECT COUNT(*) FROM transactions WHERE ...; -- Same filters as above

# Query 3
Txs, ordered by timestamp DESC, single chain or cross chain, for a given TO address

SELECT * FROM transactions WHERE "chain_id"="..." AND "to"='...' ORDER BY timestamp DESC LIMIT 10;
SELECT * FROM transactions WHERE to"='...' ORDER BY timestamp DESC LIMIT 10;
SELECT COUNT(*) FROM transactions WHERE ...; -- Same filters as above

# Query 4
Txs, ordered by timestamp DESC, single chain or cross chain, for a given FROM or TO address

SELECT * FROM transactions WHERE "chain_id"="..." AND ("from"='...' OR "to"='...') ORDER BY timestamp DESC LIMIT 10;
SELECT * FROM transactions WHERE "from"='...' OR "to"='...' ORDER BY timestamp DESC LIMIT 10;
SELECT COUNT(*) FROM transactions WHERE ...; -- Same filters as above

# Query 5

SELECT * FROM transactions WHERE "from"='...' OR "to"='...' ORDER BY timestamp DESC LIMIT 10;

# Query 6
Daily count for latest N days.
Note: Would this be a good use case for an AggregatingMergeTree materialized view?

SELECT toYYYYMMDD(timestamp) AS date, count()
FROM transactions
WHERE timestamp >= '...' AND timestamp < '...'
GROUP BY toYYYYMMDD(timestamp)
ORDER by toYYYYMMDD(timestamp) desc limit 30;

SELECT toYYYYMMDD(timestamp) AS date, count()
FROM transactions
WHERE timestamp >= '...' AND timestamp < '...' AND chain_id = '...'
GROUP BY toYYYYMMDD(timestamp)
ORDER by toYYYYMMDD(timestamp) desc limit 30;

# Query 7
Daily count by address for latest N days
Note: Would this be a good use case for an AggregatingMergeTree materialized view? Issues with high cardinality of addresses (around 4M total now)