WITH tier_data AS ( SELECT date_trunc('month', block_timestamp) AS date, origin_from_address, SUM(raw_amount/pow(10,6)) AS trading_volume, CASE WHEN SUM(raw_amount/pow(10,6)) < 100000 THEN 'Free' WHEN SUM(raw_amount/pow(10,6)) >= 100000 AND SUM(raw_amount/pow(10,6)) < 1000000 THEN 'I' WHEN SUM(raw_amount/pow(10,6)) >= 1000000 AND SUM(raw_amount/pow(10,6)) < 5000000 THEN 'II' WHEN SUM(raw_amount/pow(10,6)) >= 5000000 AND SUM(raw_amount/pow(10,6)) < 10000000 THEN 'III' WHEN SUM(raw_amount/pow(10,6)) >= 10000000 AND SUM(raw_amount/pow(10,6)) < 50000000 THEN 'IV' WHEN SUM(raw_amount/pow(10,6)) >= 50000000 AND SUM(raw_amount/pow(10,6)) < 200000000 THEN 'V' ELSE 'VIP' END AS tier, CASE WHEN SUM(raw_amount/pow(10,6)) < 100000 THEN NULL WHEN SUM(raw_amount/pow(10,6)) >= 100000 AND SUM(raw_amount/pow(10,6)) < 1000000 THEN 0.00035 WHEN SUM(raw_amount/pow(10,6)) >= 1000000 AND SUM(raw_amount/pow(10,6)) < 5000000 THEN 0.000275 WHEN SUM(raw_amount/pow(10,6)) >= 5000000 AND SUM(raw_amount/pow(10,6)) < 10000000 THEN 0.000225 WHEN SUM(raw_amount/pow(10,6)) >= 10000000 AND SUM(raw_amount/pow(10,6)) < 50000000 THEN 0.000175 WHEN SUM(raw_amount/pow(10,6)) >= 50000000 AND SUM(raw_amount/pow(10,6)) < 200000000 THEN 0.000125 ELSE 0.0001 END AS avg_fee FROM ethereum.core.fact_token_transfers WHERE to_address IN ( '0x0fd829c3365a225fb9226e75c97c3a114bd3199e', '0x92d6c1e31e14520e676a687f0a93788b716beff5', '0x7e9b1672616ff6d6629ef2879419aae79a9018d2', '0xd54f502e184b6b739d7d27a6410a67dc462d69c8', '0xa8b39829ce2246f89b31c013b8cde15506fb9a76', '0x09403fd14510f8196f7879ef514827cd76960b5d', '0x1e0447b19bb6ecfdae1e4ae1694b0c3659614e4e' ) AND block_timestamp BETWEEN DATE '2022-08-01' AND CURRENT_DATE AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' GROUP BY date, origin_from_address HAVING trading_volume > 100000 ) SELECT date, origin_from_address, trading_volume, tier, avg_fee, trading_volume * avg_fee AS fees_collected FROM tier_data ORDER BY date, origin_from_address;

dYdX Free Trading Campaing Analysis