samUntitled Query
    Updated 2022-12-14
    WITH
    kamino_tx AS (
    SELECT
    tx_id
    FROM
    solana.core.fact_events
    WHERE block_timestamp >= '2022-01-01'
    AND program_id = 'HubbLeXBb7qyLHt3x7gvYaRrxQmmgExb7fCJgDqFuB6T'
    AND block_timestamp >= DATE_TRUNC(week, DATEADD(day, -1, IFF( SUBSTR( CONCAT({{year}}, {{week}}), 5) = 1, TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), DATEADD( week, SUBSTR( CONCAT({{year}}, {{week}}), 5) - 2, DATEADD( day, 1, LAST_DAY(TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), week) ) ))))
    AND block_timestamp < DATEADD(day, 1, LAST_DAY(IFF( SUBSTR( CONCAT({{year}}, {{week}}), 5) = 1, TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), DATEADD( week, SUBSTR( CONCAT({{year}}, {{week}}), 5) - 2, DATEADD( day, 1, LAST_DAY(TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), week) ) )), week))
    ),
    interactions AS (
    SELECT
    signers[0] AS wallet,
    COUNT(DISTINCT tx_id) AS num_tx
    FROM
    solana.core.fact_transactions
    WHERE block_timestamp >= '2022-01-01'
    AND succeeded = 'True'
    AND block_timestamp >= DATE_TRUNC(week, DATEADD(day, -1, IFF( SUBSTR( CONCAT({{year}}, {{week}}), 5) = 1, TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), DATEADD( week, SUBSTR( CONCAT({{year}}, {{week}}), 5) - 2, DATEADD( day, 1, LAST_DAY(TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), week) ) ))))
    AND block_timestamp < DATEADD(day, 1, LAST_DAY(IFF( SUBSTR( CONCAT({{year}}, {{week}}), 5) = 1, TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), DATEADD( week, SUBSTR( CONCAT({{year}}, {{week}}), 5) - 2, DATEADD( day, 1, LAST_DAY(TO_DATE( SUBSTR( CONCAT({{year}}, {{week}}), 1, 4), 'YYYY'), week) ) )), week))
    AND tx_id IN (
    SELECT
    tx_id
    FROM
    kamino_tx
    )
    GROUP BY
    wallet
    ORDER BY
    num_tx DESC
    ),
    outliers AS (
    SELECT
    wallet
    FROM
    Run a query to Download Data