hera_najamDAU_MAU_for_BSC
    Updated 2023-04-13
    SELECT a.day, a.dau, a.month_, a.mau, (a.dau/a.mau) as "DAU/MAU", a.address, b.address_name, b.label_type from
    (SELECT aa.day, aa.dau , CONCAT(year(bb.month), '-', month(bb.month)) as month_, bb.mau, aa.address from
    (select DATE_TRUNC('day', Block_Timestamp::date) as day, count(distinct FROM_ADDRESS) as dau, TO_ADDRESS as address
    from bsc.core.fact_transactions where STATUS='SUCCESS' and day = CURRENT_DATE-1 group by day,address) aa
    JOIN
    (select DATE_TRUNC('month', Block_Timestamp::date) as month, count(distinct FROM_ADDRESS) as mau, TO_ADDRESS as address
    from bsc.core.fact_transactions where STATUS='SUCCESS' and month BETWEEN '2022-09-01' AND '2022-09-30' group by month,address) bb
    on aa.address = bb.address) a
    JOIN
    (SELECT address, address_name, label_type FROM bsc.core.dim_labels WHERE label_type in ('defi', 'dex', 'dapp')) b
    on a.address = b.address where address_name != '' ORDER BY a.mau DESC;
    Run a query to Download Data