hera_najamDAU_MAU_for_BSC
Updated 2023-04-13Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
›
⌄
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