users.sql

with
    combined as (
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."AddLiquidity"
        union
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."RemoveLiquidity"
        union
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."OpenLong"
        union
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."CloseLong"
        union
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."OpenShort"
        union
        select
            "from" as addr,
            "blockTimestamp",
            pool
        from
            indexer_blue."CloseShort"
    ),
    last_month as (
        SELECT
            EXTRACT(
                EPOCH
                FROM
                    NOW ()
            ) - 86400 AS lm
    ),
    last_month_addresses as (
        select distinct
            addr
        from
            combined
        where
            "blockTimestamp" < (
                select
                    lm
                from
                    last_month
            )
    ),
    this_month_addresses as (
        select distinct
            addr
        from
            combined
        where
            "blockTimestamp" >= (
                select
                    lm
                from
                    last_month
            )
    )
select
    count(distinct current.addr)
from
    this_month_addresses current
    LEFT JOIN last_month_addresses ON current.addr = last_month_addresses.addr;