Ekubo Leaderboard points multipliers

query.sql

WITH pair_swap_counts_by_day
         AS (SELECT s.pool_key_hash,
                    date_bin(INTERVAL '1 day', b.time, '2000-01-01') AS day,
                    COUNT(DISTINCT transaction_hash)                 AS swap_count
             FROM swaps AS s
                      JOIN event_keys AS ek ON s.event_id = ek.id
                      JOIN blocks AS b ON ek.block_number = b.number
             WHERE s.event_id < ${maxEventIdExclusive}
             GROUP BY s.pool_key_hash, day),

     -- this boost allows users to earn more points by depositing liquidity in pools that are heavily utilized
     pair_points_boost AS (SELECT pool_key_hash,
                                  (20::NUMERIC / (1 + EXP(-0.0001 * SUM(swap_count)))) - 10 AS multiplier
                           FROM pair_swap_counts_by_day
                           GROUP BY pool_key_hash),

     token_symbols AS (SELECT *
                       FROM (VALUES (0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7::NUMERIC,
                                     'ETH'),
                                    (0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8,
                                     'USDC'),
                                    (0x0124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49, 'LORDS'),
                                    (0x01fa2fb85f624600112040e1f3a848f53a37ed5a7385810063d5fe6887280333,
                                     'R'),
                                    (0x070a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac,
                                     'LUSD'),
                                    (0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3, 'DAI'),
                                    (0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2,
                                     'wstETH'),
                                    (0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8,
                                     'USDT'),
                                    (0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac,
                                     'WBTC'),
                                    (0x0319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610,
                                     'RETH'),
                                    (0x049210ffc442172463f3177147c1aeaa36c51d152c1b0630f2364c300d4f48ee, 'UNI'),
                                    (0x06f15ec4b6ff0b7f7a216c4b2ccdefc96cbf114d6242292ca82971592f62273b,
                                     'xSPEPE'),
                                    (0xd3d60f69d76b6639fc51ae1447480ecdf0ba1ea3f6162e3230be0a40ef2a6b,
                                     'xGARY'),
                                    (0x26e0852e1de834db3858b644270c52c4e0cab5be1da710751711c11b74eefed,
                                     'PUNK'),
                                    (0x55c3868c743e92c470701799388cb3fb1d922dcec271150d10462c6948e3cf4, 'STRAK-meme'),
                                    (0x03a6ec0b0ea7a1903329d5dec4bb574ecb4d6fdc206664e1c61eeded8158ab40,
                                     'BLACK'),
                                    (0x0137dfca7d96cdd526d13a63176454f35c691f55837497448fad352643cfe4d4,
                                     'AKU'),
                                    (0x04ee423b1f89f1ac011d6f2f05c1eb35d61594b801122771cce7747718aa416f,
                                     'cro')) AS q (token, name))
SELECT t1.name                                                                                  AS token0_symbol,
       t2.name                                                                                  AS token1_symbol,
       ROUND((pool_keys.fee / (340282366920938463463374607431768211456) * 100) * 10000) / 10000 AS fee_percent,
       ROUND(((pow(1.000001, pool_keys.tick_spacing)) - 1) * 10000000) / 100000                 AS tick_spacing,
       ROUND(pair_points_boost.multiplier * 100) / 100                                          AS fees_multiplier
FROM pair_points_boost
         JOIN pool_keys ON pool_key_hash = pool_keys.key_hash
         JOIN token_symbols t1 ON token0 = t1.token
         JOIN token_symbols t2 ON token1 = t2.token
ORDER BY pair_points_boost.multiplier DESC

result.csv